The lessons covered for this review is
- Configure Analysis Services
- Configure and Deploy Reporting Services
- Configure and Deploy Sharepoint Integration
- Configure Integration Services security
- Managing Full-Text Indexing
- Configure Filestream and Filetable
Configure Analysis Services
You have 2 options when configuring analysis services: multidimensional or tabular mode and data mining mode. Tabular mode is the new mode that supports tabular modeling features and can process data in a much faster way.
Analysis services can be installed from the command line by using the /features=AS option. The /ASSERVERMODE can be set to MULTIDIMENSIONAL, TABULAR and POWERPIVOT.
Analysis services uses a managed service account when installed by default. If your service needs to connect to network resources in the security context of the current logged in user account, you should create a specific domain account for use exclusively of analysis service. Then to grant access to this account, do it in the Analysis Server host.
The best practice is to run Analysis services by using an account with least privileges. Avoid using the default Localservice and NetworkService accounts in production environments because the Analysis service connection strings and passwords can be decrypted and is accessible to the Analysis services logon account.
Configure and Deploy Reporting Services
There are 2 options when configuring a Reporting Services from the installation disk as shown in the figure. You can only choose the Install and configure option if you have installed the web server role prior to this installation and you are installing the database engine on the same server. The second option install only will not configure the reporting services during this time but you can do it at a later time. You can configure from Start>Microsoft SQL Server 2012>Configuration Tools>Reporting Services Configuration.
From the Reporting Services Configuration Manager tool, you can configure the service accounts, the web service URL and properties, report manager URL, email settings, backup and restore and scale out deployment. In my case, I have the following setup in my Reporting Services.
There is one more thing you need to do with your Reporting Services Administrator site after all the configuration is done. Usually you will encounter errors in connecting your URL for the first time. If this happens, as many in the SQL DBA community say it does happen but it’s not really a bug, make sure you add your Reporting Services Administrator site to your trusted connections or you can open your browser with administrator account and you should be good to go.
Configure and Deploy Sharepoint Integration
If your organization has a Sharepoint farm, you can deploy Analysis services and Reporting services as shared services. This will enable you to use features such as the new PowerPivot for Microsoft Sharepoint and Power View, a Reporting services interactive report designer.
The following products must be installed and properly configure prior to deploying Reporting services, Power View and PowerPivot for Sharepoint 2010.
- Sharepoint Server 2010 Enterprise Edition
- SQL Server 2012 Database Engine
- SQL Server 2012 Reporting Services and Reporting Services Addin
- SQL Server 2012 PowerPivot for Sharepoint
The host computer must be joined to the domain and you will need to create domain user account for the following services
- Sharepoint Web Services and Administrative Services
- Reporting Services
- Analysis Services
- Microsoft Excel Services
- Secure Store Services
- PowerPivot System Services
You will also need to perform the following to integrate Sharepoint 2012 with SQL Server 2012:
- Install a Sharepoint Server 2010 SP1 Enterprise Edition farm. You can do the configuration of the farm at a later time by not running the Sharepoint 2010 Product Configuration Wizard. This will make your SQL Server 2012 database engine the default database for the farm.
- Install the SQL Server 2012 Database Engine and PowerPivot for Sharepoint as shown below.
- Accept the default instance ID of PowerPivot and complete the installation of SQL Server 2012.
- Use the PowerPivot Configuration tool from the Configurations tool folder to create a farm, a default web application and root site collection.
- Verify the farm configuration is successful by navigating to Central Administration
- Run SQL Server 2012 setup to install and configure Reporting Services and Reporting Services Addin
- Sharepoint site Administrators can extend Sharepoint document libraries to use Business Intelligence content types.
- Sharepoint site Administrators create data connections files to launch Power View by creating a BI semantic model connection (.bism) or a Reporting services shared data (.rsds) source as a data source for Power View.
If you want to test the configuration of Sharepoint Server or want to play around with it, you can download a trial version for 180 days here.
Prior to proceeding to install Reporting Services Add-in for Sharepoint features, you must have the Sharepoint Server 2010 already installed and configured in your system.
Configure SQL Server Integration Services Security
For more information on this part of review topic, please visit my previous article on Integration Services Security. This topic is also applicable to SQL Server 2012.
Managing Full-Text Indexing
You can create full-text index on table via the Full-text Creation Wizard or by running the CREATE FULLTEXT INDEX statement.
A sample of using a CREATE FULLTEXT INDEX statement is as below. This will create full-text index on table Production.ProductReview table under the existing unique key PK_ProductReview_ProductReviewID.
CREATE FULLTEXT CATALOG Production_Catalog;
CREATE FULLTEXT INDEX ON Production.ProductReview
ReviewerName, EmailAddress, Comments
KEY INDEX PK_ProductReview_ProductReviewID ON production_catalog;
To use the Fulltext creation Wizard, right click on the table where you want to create the full-text index and select Full text index>Define Full-text Index. Proceed to follow the instructions on the wizard and you are good to go.
To test your newly created Fulltext index and to make sure that query optimizer engine is using it is to check the query plan as shown below.
Some points to keep in mind on Full-text index;
- A full-text index can contain up to 1024 columns
- Only one full-text index can be created per table so be very careful with your table design
- In SQL Server 2012, the full-text index is part of the SQL Server process rather than a separate service.
FILESTREAM allows SQL Server based applications to store unstructured data such as images and documents on the host server computer file system.
It is best to use FILESTREAM if you have objects that you need to store that is more than 1MB. The traditional varbinary(max) limit of 2GB does not apply to BLOBs. Also it is important to use FILESTREAM when your application demands fast read access.
To enable FILESTREAM, open SQL Server Configuration Manager and click on your SQL Server service properties, go to FILESTREAM tab and check as shown below. Then restart your SQL Server service.
From there, when you go to your database and check your database properties then go to Filegroups, the part where in you can add filegroups for your filestream should already be enabled. See below screenshot from my installation on my system.
And when you also visit the Options menu on the right, you should be able to see your filestream file system directory in the FILESTREAM section. SQL Server will use this root directory to store filestream metadata and filestream transaction logs. See my installation screenshot below.
Your next step should be to add a filestream in the FILES side menu, initially you can make this your default filestream, then go to FILEGROUPS, add a filegroup to handle that default filestream you just created, the file type must be FILESTREAM Data, then you should be good to go. Don’t forget to add proper permissions to this root directory for your filestream.
To test filestream data, you can create a test table similar below. It is mandatory to use ID with UNIQUEIDENTIFIER datatype as PK for the table.
CREATE TABLE FilestreamTestTable
( [ID] uniqueidentifier ROWGUIDCOL not null PRIMARY KEY,
[FSDATA] varbinary(max) FILESTREAM null
Then you have no more worries on the 2G limit on varbinary(max) because by using filestream you can now store hundreds of gigabyte in filesize 🙂
To configure filetables, go back to your SQL Server 2012 SSMS, right click on your database, expand Tables, you should be able to see folder for FILETABLES. Right click on this and go to New File Table menu and click. This will open a new query editor window with a script template on how you can go about creating file groups. File groups are file directories in your server file system where you can allow users and applications to create and store their files. See below.
SQL Server is sitting on top of this file table directory and monitors new files dropped to this folder and when query this from your SSMS, it’s there! No fuss!
Ain’t it the coolest? 🙂