70-457 Reviewer #05, Configuring SQL Server 2012 Components

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.

Analysis Services Configuration

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

Reporting Services Configuration

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.

Reporting Services Configuration Manager

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.

Setup Role

  • 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.

USE AdventureWorks2012;
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.

Fulltext Indexing Wizard

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.

Fulltext Test

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.

Filestream Configuration

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.

Database Properties Filestream Filegroups

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.

Database Properties Filesream Options

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.

FileTable Template

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? 🙂


2 responses to “70-457 Reviewer #05, Configuring SQL Server 2012 Components

  • twitter.com

    If you would like to improve your familiarity
    simply keep visiting this site and be updated with the most
    up-to-date news posted here.

  • Get free coupons

    Hi there I am so glad I found your weblog,
    I really found you by error, while I was browsing on Yahoo for something else, Anyhow
    I am here now and would just like to say thanks for a tremendous post and a all
    round exciting blog (I also love the theme/design),
    I don’t have time to look over it all at the minute
    but I have book-marked it and also added your RSS feeds,
    so when I have time I will be back to read a lot more, Please do keep up the awesome jo.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.


Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: