SQL Server Updatable Subscriptions: What is the best replication type for this?

Replication is used for copying data and database objects from one location to another on a recurring basis. The topology of replication is vary, it can be a simple implementation with two servers or a complex implementation with many local and remote servers and mobile devices. Servers involved are designated with server roles such as Publisher, Distributor and Subscriber and their responsibilities are publishing, distributing, and finally subscribing published data. In some scenarios, subscribers use the subscription only for reading data. In other scenarios subscribers make modification to subscription (change data) and send back to publisher. Such subscriptions are called as Updatable Subscription. SQL Server provides many ways to implement a replication with updatable subscription. This post discusses some considerations and guide lines for selecting the best replication type with updatable subscription suit for your requirement.

The general overview of Replication can be illustrated as below.

• Publisher: Publishes data with publication.
• Distributor: Distributes data. This server holds metadata, historical data and replicated data for some replication types.
• Subscriber: Receives data from publications. They can make changes to subscribed data too.

Updatable Subscription
SQL Server provides three different types of Replications. They are:

• Snapshot replication
• Transactional replication
• Merge replication

Changing data at subscriber and sending changes back to publisher facility is not available with all types. Transactional replication and Merge provides facilities for it.

Transactional Replication is mainly used in Server-to-Server scenario. Subscribers are initiated with a snapshot of publication and then transactions occurred are sent to them in incremental manner. This replication type allows subscribers to update data and send back to publisher. If it is required, Transactional Replication with updatable subscription option is available at creation of publication. This works in two ways:

• Immediate Updating: Uses MSDTC for sending data from subscribers to publisher and applying. It is done through triggers added in published tables in subscription.
• Queued Updating: Changes are sent to a queue. The Queue reader agent pull changes and apply to publisher. It is done with triggers too.

Read more on these two at: http://msdn.microsoft.com/en-us/library/ms151794.aspx

Now, where we can use Transactional replication with updatable subscription? Here are some guidelines for selecting this as your replication type:

• Scenario is Server-to-Server.
• Number of subscribers are less than 10.
• Subscribers update data infrequently.
• Subscribers, distributor and publisher are connected most of the time (For Immediate updating, regular connection is required as update happens synchronously).
• Subscribers need all changes, not the last change (Eg. if there are 100 transactions for a record, all are required by the subscriber, not the final state of the record)
• Expect less update conflicts with queued updating. This type does not have rich facilities for handling conflicts.

**Note that MSDN says that this feature will be removed in a future version of SQL Server, so it is not advisable to use this if you plan to upgrade your system with future versions.

Merge replication is mainly for Server-to-Client environment. This allows subscribers to make modification on replicated data and send them back to publisher. Use following guidelines when selecting Merge as your replication type for updatable subscription:

• Scenario is Server-to-Client.
• Many number of updating subscribers (Merge is designed for this).
• Expect conflicts (Good support on conflict detection and resolving)
• Subscribers need the final state of the change, not all transactions.
• Subscribers, distributors and publisher are not always connected.
• Subscribers need a portion of publication, not the entire one (This is done with dynamic filters in publication).

Okay, you can consider given guidelines and select the best type for your requirement. How about Peer-to-Peer replication? Can we use it too for handling this?

Peer-to-peer replication is scale out and high availability solution. It maintains copies of data across multiple servers, referred as nodes. Its foundation is Transactional replication and works in similar manner. The main difference is, nodes are not designated as publisher or subscribers, all are publishers and subscribers. Here are some guidelines for selecting it:

• Requires high availability (with other types, if publisher goes down, replication topology is down too).
• Less number of nodes (would be better to have 10 or less).
• Filtering of publication is not required.
• Do not expect much conflicts.

Self-Service Business Intelligence with SQL Server 2008 R2 – Speaking engagement in SQL Server Sri Lanka User Group

The February meeting of SQL Server Sri Lanka User Group meeting was held on last Wednesday and sessions were done by Dinesh Asanka and me. My session was on Self-Service Business Intelligence with SQL Server 2008 R2, focusing on PowerPivot. The agenda for the sessions was:

• What’s new in SQL Server 2008 R2?
• What is Business Intelligence?
• Introduction to New Paradigm
• Introduction SQL Server PowerPivot
• Future Releases
• Q & A

The presentation is available for downloading: http://www.sqlserveruniverse.com/v2/SSSLUG/Downloads.aspx

Guest user account

The Guest user is an account which is automatically created when a database is created. This account is used when a login connects to a database without having a user account created in it. By default, guest is disabled, to enable and disable, use the codes below;

-- to enable

GRANT CONNECT TO GUEST

-- to disable

REVOKE CONNECT FROM GUEST

The recommended way of managing guest account is, have it in disabled status. If you have enabled it for some reason, permission can be granted either using graphical interface or through T-SQL.

-- Give data read permission to guest on Customer table

GRANT SELECT ON Sales.Customer TO GUEST

If guest is enabled in AdventureWorks2008 database and above permission is granted, when a login (eg. Jane) connects to AdventureWorks2008, she will be able to read data from Sales.Customer though she has no user account created in AdventureWorks2008.

Template Explorer in SQL Server

Although this is not much popular among DBEs, it is one of the facilities should be used. The Template Explorer gives us various type of templates for constructing objects. For an example, if you need to create a database using T-SQL, the template of the CREATE DATABASE can be taken from the Template Explorer and altered as you need. This definitely speeds up coding though it discourages remembering syntax.

The Template Explorer can be opened using View –> Template Explorer (or Ctrl+Alt+T) menu. Templates are grouped into three: SQL Server, Analysis Server, SQL Server Compact. Templates in a group are categorized too. For an example, if you want to create a database with multiple file groups, CREATE DATABASE ON MULTIPLE FILEGROUPS in DATABASE  category under SQL SERVER group can be used.

Template can be brought into query window by dragging and dropping or double-clicking. Once placed, need to replace parameter values with your values. Although you can manually do it, easiest way is, using Specify Values for Template Parameters window. It can be accessed using Query –> Specify Values for Template Parameters menu.

You can create your own templates and have them in Template Explorer. Start it with creating a folder or add new ones in given folders (categories). This facility is open through right-clicking on the folder in Template Explorer and selecting New. One thing you need to remember is the way of adding parameters. Parameters should be enclosed with “<” and “>” and should contain three parts: Parameter name, type, value. You can have the same for both Parameter name and value, allowing user to replace the “value” with their value. For an example, parameter for database name would be: <Database_Name, sysname, Database_Name>.

sys.database_files: size column and why 128?

It came up while my class was being conducted. We were discussing of finding the size of files attached to a database and available free space of files. As usual I used my sample codes to show few things, this is one of them:

SELECT name, size/128 size FROM sys.database_files

Now, why 128? It did not come into my mind immediately . Result of being a designer.

Size column shows current size of the file in 8KB pages. It is the number of pages that have been allocated. So, why 128? It is for converting to MB. There are 128 8KB pages in 1MB. Here is my altered code;

SELECT name, size/128 SizeInMB

    , FILEPROPERTY(name, 'SpaceUsed')/128 SpaceUsedInMB 

FROM sys.database_files

-- or

SELECT name, size*8 SizeInKB

    , FILEPROPERTY(name, 'SpaceUsed')*8 SpaceUsedInKB 

FROM sys.database_files

Installing and Configuring PowerPivot for SharePoint 2010

Microsoft PowerPivot is slowly becoming an essential technology for Business Users addressing Self-Service Business Intelligence. It is innovated by Microsoft SQL Server team as a part of Microsoft SQL Server 2008 R2 release and comes in two flavors; PowerPivot for Excel and PowerPivot for SharePoint. I have already written a post on Excel add-in (Creating Self-Service Business Intelligence dashboard with PowerPivot – Part I) which discusses dashboard creation with Excel and PowerPivot. This post discusses about Installing PowerPivot for SharePoint 2010 with following sections;

• Excel and SharePoint
• Installing PowerPivot for SharePoint 2010
• Error: Could not load assembly
  ‘Microsoft.AnalysisServices.SharePoint.Integration.dll’
• SQL Server Analysis Services Installation – VertiPaq
• Deploying PowerPivot solution packages to Web Application
• Other Services required
• Creating PowerPivot Service Application
• PowerPivot Feature Integration for Site Collections
• Configuring PowerPivotUnAttendedAccount for Data Refresh

Excel and SharePoint
Why two technologies? We can consider that Excel is for authoring PowerPivot applications. It allows to create an application with one or many data sources and then facilitates data analysis in a multi-dimensional structure. It does not need a constant connection to used data sources, everything will be stored inside Excel Workbook and if want, data can be refreshed. SharePoint can be considered as a hosting environment for PowerPivot applications. We usually go for SharePoint if collaboration, sharing and reporting are required.

Installing PowerPivot for SharePoint 2010
There are two components need to be installed. PowerPivot System Service and Analysis Services in VertiPaq mode which is a local, in-process Analysis Services engine. Let’s start with Analysis Services.

Installing Analysis Services for PowerPivot is straight forward. I have already set up my SharePoint 2010 farm. It uses SQL Server 2008 R2 that exist in same box. Note that my installation contains full SQL Server product suite, including SQL Server Analysis Services, hence new instance of Analysis Services for PowerPivot will be a named instance. There is a known issue you might face if SQL Server installation is already exist. It is;

Could not load assembly ‘Microsoft.AnalysisServices.SharePoint.Integration.dll’
You get this error if you have a prior installation of SQL Server 2008 R2. Unfortunately it comes at the completion of the installation.

Solution for this is simple. Create a text file as below and save it as Setup100.exe.config.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <runtime>

    <disableCachingBindingFailures enabled="1" />

  </runtime>

</configuration>

Then move the file into %ProgramFIles%\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64. Once the file is placed, you need to start the installation again. For more information, see this: http://support.microsoft.com/kb/2261507 .

SQL Server Analysis Services Installation – VertiPaq
Before starting the installation, make sure that a domain account is created for new Analysis Services instance for running the service. In my case, newly created account for this is PowerPivotAnalysisServices.

Now run the SQL Server 2008 R2 setup. It would be better to run the setup as Administrator (Right click on Setup.exe and select “Run as Administrator”). If you have configured more than one server for the farm as Application Servers, the installation should be done for all servers (Generally we set up two servers one for WFE and another for applications. This installation should be done only on Application Servers).

Once the installation is started, select New installation or add shared features as per the screen below.

Continue with screens until you get a screen which allows you to select SQL Server PowerPivot for SharePoint. Select Existing Farm for Add PowerPivot for SharePoint to (Note that assumption here is Farm is already configured).

Next screen is Feature Selection. It is a read-only screen, you will not be able to change anything on it. Continue with the wizard until you get Instance Configuration screen. You will notice that the instance has already been named ad POWERPIVOT. Do not change it, continue the wizard.

At the Server Configuration screen, set newly created domain account and its password for SQL Server Analysis Services. When you have Analysis Services Configuration screen, make sure that accounts that need to be administrators for Analysis Services are added.

Everything is set now, continue the wizard and complete the installation. Once it is done, go to SharePoint Central Administration –> System Settings –> Manage Servers in this Farm. It shows all the servers in the farm and services running. If you see SQL Server Analysis Services and SQL Server PowerPivot System Service, your installation is successful.

Deploying PowerPivot solution packages to Web Application
Next part is deploying PowerPivot solution packages to Web Applications (to WFE Servers). There are two solution packages. Installation we performed earlier deploys solution packages for Central Administration but not for our web application. Go to SharePoint Central Administration –> System Settings –> Farm Management –> Manage Farm Solutions. You should see two solutions;

• powerpivotfarm.wsp is deployed globally, no need to deploy again.
• powerpivotwebapp.wsp, you can see that it has been deployed to my default web application (http://w2008r2-sp:10000). This is the solution we need to deploy manually to Web Application which suppose to hold PowerPivot data.

Let’s deploy powerpivotwebapp.wsp to a web application. Click on powerpivotwebapp.wsp. It opens Solution Properties. Click on Deploy Solution. Select the web application from the drop-down you need to use under Deploy to? and click OK. Once the Solution Properties page is open, make sure that added web application is listed as below.

Other Services required
Next step is starting all other necessary services required by PowerPivot. Make sure following services are running;

• Excel Calculation Service
• Secure Store Service
• Claims to Windows token Service
• SQL Server Analysis Services
• SQL Server PowerPivot System Service

These services are listed under SharePoint Central Administration –> System Settings –> Manage Services on Servers.

Creating PowerPivot Service Application
This helps to establish the communication between WFE and PowerPivot service via PowerPivot Service Application Proxy. Let’s create it. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Click on New and select SQL Server PowerPivot Service Application.

With Create New PowerPivot Service Application window, set the name as PowerPivotServiceApplication. Do not use any existing pool, create a new one and assign a domain account for it. Set the database server and database name then. Finally select the checkbox Add the proxy for this PowerPivot service application to the default proxy group. Click OK to create it.

PowerPivot Feature Integration for Site Collections
This is appeared under Site Collection Features and must be activated for each of the site collections. Go to your site collection and click on Site Actions –> Site Settings –> Site Collection Administration –> Site Collection Features. When the page open, find PowerPivot Services feature integration for Site Collections and activate it.

Configuring PowerPivotUnAttendedAccount for Data Refresh
PowerPivot unattended account is used for refreshing data in the workbook if user credentials are not exist with the workbook. This account is stored in SharePoint Secure Store Service. In order to continue, you need to make sure that Secure Store Service is up and running, and master key is created. I have already configured Secure Store Service for PerformancePoint. If you have not, see this post for configuring it: http://dinesql.blogspot.com/2010/07/configuring-performancepoint-2010.html.

Let’s open the Secure Store Service Application. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Click on Secure Store Service (or select it and click on Manage) link;

Secure Store Service uses Target Applications for holding accounts. In my case, I have an application set up for PerformancePoint and that is what has been listed. If you have note, nothing will be listed. Once a Target Application is created for PowerPivot, it has to be associated with PowerPivot Service Application. Let’s start creating the Target Application. Click on New for opening Create New Secure Store Target Application. Set ID and name for the application. Appropriate ID and name would be PowerPivotDataRefresh and Power Pivot Data Refresh. Enter a valid email for Contact Email and select Individual for Target Application Type.

Next screen for specifying fields (columns) for the application. Accept the default as we need only user name and password and continue.

Next screen for setting admins for the application. Make sure that you have added the account which is used for PowerPivot Service Application pool. In addition to that, any account need to be an admin can be added. Continue the wizard and complete. Finally you are back on Secure Store Service. You should see the newly created application listed now.

Next is setting credentials for PowerPivotDataRefresh application (or PowerPivot unattended account). Select the check box next to newly created application and click on Set Credentials button. It opens the Set Credentials for Secure Target Application (Individual). Settings for this screen will be;

• Credential Owner: This is the owner of the credential. It will be used by PowerPivot unattended account but this is not the account used as the unattended account.
• Windows user name and password: This is the actual account used as the unattended account.

Click OK. Now we need to associate the application with PowerPivot Service Application. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Find PowerPivot Service Application and click the link (or select it and click on Manage button). This opens PowerPivot Management Dashboard. Click on Configure Service Application Settings in Actions frame. It opens PowerPivot Settings. Find PowerPivot Unattended Data Refresh Account input box under Data Refresh. Type the ID of the application created in Secure Store Service, in my case, it is PowerPivotDataRefresh.

Done. To complete the process, few setting have to be done in Excel Services.

Settings in Excel Services
Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Find Excel Services Application and open it. It opens Manage Excel Services Application. Click on Trusted File Locations.

You will see that SharePoint has been added as a Trusted Location (Address is http://). In order to access a workbook by both Excel and PowerPivot services, this is required.

Get the menu of http:// and select Edit. Change following items in Edit Trusted File Location.

• Workbook Properties: Maximum Workbook Size. Set as you want. Note that maximum file size accepted by SharePoint for both Excel and PowerPivot is 2GB.
• External Data: Select Trusted data connection libraries and embedded under Allow External Data.

Click OK to save settings. Now, You should be able to upload an Excel file that contains PowerPivot data into the web application and see.

Will discuss more on this in another post.

PPS Reports: Integrating Reporting Services Reports with PerformancePoint 2010 Dashboards

The success of Business Intelligence solution can be measured with Visualization of data, hence Visualization in proper manner is important. It is handled through many ways, providing easier access for information to business users. One of the key components in Microsoft Business Intelligence suite is PerformancePoint 2010 which supports visualizing data in number of ways. PPS allows business user to interact with data, organize as he wants and change the content as he wants with its reporting features. Some of reporting facilities available with PPS are Reporting Services reports, Analytic charts, grids, and Decomposition trees. This post focuses on Reporting Services reports, basically, how a Reporting Services report can be added to PPS dashboard.

Microsoft Report Viewer 2008
In order to use Reporting Services report with PPS Dashboard Designer, you need to make sure that Microsoft Report Viewer is installed in your machine. If not, you may face errors like “Unable to connect with Report Server” or “Could not load file or assembly ‘Microsoft.ReportViewer.WinForms’…..”. It is available for 2005, 2008 and 2010 now. It looks like PPS Dashboard Designer works only with 2008 (I have 2008 SP1) but you can try 2010 too. Here are links for downloading it:
Microsoft Report Viewer Distributable 2008
Microsoft Report Viewer Distributable 2008 SP1
Microsoft Report Viewer Distributable 2010

Getting Started
As usual, you have to start with PPS Dashboard Designer. If you have configured Business Intelligence Web Application and site properly with SharePoint 2010, there will a link available for downloading this ClickOnce application (see Configuring PerformancePoint 2010 for more details). Click on Run Dashboard Designer for downloading the designer.

Once open, Save it with a meaningful name. Then select PerformancePoint Content folder in left-pane and go to Create tab in the ribbon. Click on Reporting Services.

Report Setting
You need to set few settings in order to have a Reporting Services report in your dashboard.

Server Mode: This allows you to set the mode of Reporting Server you are going to connect with. If it is hosted in SharePoint, select SharePoint Integrated, else select Report Centre. In my case, it is SharePoint Integrated mode.

Report Server URL: This is the URL of Report Server Web Service which you set of Reporting Services Configuration Manager.

Report URL: This is the exact URL of the report. If it is hosted in SharePoint, make sure that you have given the proper URL (including the extension). If you select Report Centre, you will be able to browse the report and set.

Show toolbar, Show Parameters, Show DocMap: You can set the visibility of these items. Note that Document map will not be available for all reports. It has to be created with either Report Builder or BIDS.

Zoom: Allows you to set the viewing size.

Format: Default is HTML 4.0. You can select other types such as Excel or PDF. Based on the type you select, report will be either shown in the web page or will be downloading to client machine.

Section: This allows you to set the page to be displayed. If you set 3 for this, 3rd page will be open as the default page.

DocMap ID: It is supposed to show the element set with this as the default page. I could not figure it out the way of setting it, for me, it did not work.

Report Parameters: This allows to set parameters default values.

Once settings are done, add a dashboard and place the report, and publish to SharePoint site. SharePoint site can be set with File Button –> Designer Options –> Server –> SharePoint URL. Make sure it is set before publishing.

Here is the published dashboard.

PerformancePoint 2010: Decomposition Tree is not available

Are you unable to find the Decomposition Tree in the menu? Is it not visible?

Reason is simple, make sure SilverLight 3 (or 4) is installed. Once it is installed, it will be available for you. Thanks for Dinusha for reminding me on this.

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part III

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated mode is straight forward and simple. If you have read my post Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part I and Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part , then you know the way of configuring it. You will not face much issues after configuring if it is done with Standalone SharePoint 2010 architecture. If it is a SharePoint farm which components are distributed, few things should be considered. Here are some of points you need to consider when Reporting Services is configured in SharePoint 2010 Farm. Note that this was tested with a Web Application which Authentication is set as Classic and NTLM is set for Authentication Provider.

Domain accounts created
Reporting Services Account: Domain account, no admin privileges. Usually name it as a Domain\ReportingServices. Set this with Reporting Service Configuration Manager.

Application Pool Account: Domain account, no admin privileges. Usually name as Domain\SP_SSRSApp. Use this when the Web Application is created with SharePoint.

Site Collection (or Site) account: Domain account, need local admin rights. Usually name as Domain\SP_Admin.

** All accounts are maintained as Managed Accounts in SharePoint.

Reporting Services Windows Service Account
This should be a domain account. Once it is set with Reporting Services Configuration Manager, you need add the same account to SharePoint too. First, have it as a Managed Account (Central Administration –> Security –> General Security –> Configure Managed Accounts). Then add the account to the Site where you have configured Reporting Services Libraries. You can add this account to Home Members group. Errors you may get if it is not added are “The permissions granted to user ‘Domain\User’ (what ever account you used for logged in) are insufficient for performing this operation.” and “rsAccessDenied”. Note that error has no link with the service account but it is related to it. You may get this error with Report Builder or BIDS when deploying reports, or viewing reports.

In addition to that, you need to make sure that the account is added to Web Application content database which is created for Web Application. If not, you may get and error “Report Server has encountered a SharePoint error”.

Other than that, this account is in ReportServer database too. It is automatically added, but make sure that it is there.

Allow Anonymous Setting
Make sure that Allow Anonymous is set as false when the Web Application is created. I faced an issue related deployment from BIDS if this is not set to false (read my post on it). Once the application is created, double-check with IIS (Site –> Authentication) and see whether it is disabled. As per the Authentication and Authentication Provider we set, Only ASP.NET Authentication and Windows Authentication should be enabled.

SharePoint Integrated Reporting Services: BIDS pops up Login Dialog box indefinitely

Annoying…. This is all about SharePoint integrated Reporting Services deployment via BIDS. Web application for Reporting Services has been created with SharePoint 2010 and all necessary libraries are added (if you need to see the way of configuring SSRS 2008 R2 in SharePoint 2010, please see my posts Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part I and Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part II).

Now the problem is, popping up login dialog box indefinitely in BIDS when try to deploy reports, regardless of what account is supplied. As usual, went through the internet, found many solution but none of them worked for me. One of picked solutions by me is, disabling anonymous user. But it did not work too.

Solution
After all unsuccessful attempts, I created a new web application and set the Anonymous Access to No when creating the Web Application. Then I pointed the project’s target URL to newly created site, Wow, no login dialog box. I tried the same with old site too (by disabling Anonymous Authentication in IIS) but did not work. So, not sure about this solution but it worked with the new site. If you face the same, try a new site and see.

PerformancePoint 2010 Error: The URL is not available, does not reference a SharePoint site, or you do not have permission to connect.

If you face for this error when connecting to SharePoint site through PerformancePoint designer;

… then check the domain account which has been used for PerformancePoint service application has been added to site content database as an owner. Once added, you are not going to see this error .

PerformancePoint 2010 Error: The URL must be in one of the following zones: Local, Intranet, or Trusted

If you face for this error when connecting to SharePoint server from Dashboard Designer;

… simply add the site for Trusted Sites in your Internet Explorer.

MCTS: SQL Server Database Administrator – Maintaining a MS SQL Server Database 2008 – 6231A Course

Course 6231A: Maintaining a SQL Server 2008 Database – This is my first class for 2011. Class will be started on Sunday, 23rd January 2011. If you are a DBA or interest on SQL Server Administration, join with us. Some topics that are covered with this course are Installing and Configuring, Security, Back up strategies, Disaster Recovery, High Availability, Replication and data transferring (see more on http://www.microsoft.com/learning/en/us/course.aspx?id=6231A).

Good news! Microsoft gives 40% discount for students for Microsoft exams. All you have to do is, go to an authorized Centre and prove that you are a student of any university (both private and government), then you get 40% discount for any exam. Good Luck!

Cannot login to SharePoint 2010 site when the Host Header is set

Everybody prefers a good URL for sites rather than server name with ports. Same for SharePoint Web Applications (SharePoint sites). When a Web Application is created, it allows us to set the URL for the site as we want through Host Header as well as the port. For example, when a Web Application is created as http://SPServerName:3000 , URL can be set as http://MyTestWeb.com with Host Header input box. Once created you need to make sure that a DNS record is created and pointed to SharePoint server.

My environment is set up with two Windows 2008 R2 Enterprise Editions. Once server is configured as the Domain Controller and other has SharePoint 2010 and SQL Server 2008 R2.

Here is the way I set;

Add URL I need for the Web Application:

Set DNS record in DNS Server, in my case it is DC:

Done. I should be able to navigate my site (after creating a Site Collection for the Web Application) using the URL set with Host Header (in my case, it is http://bi.andromeda.com) without using http://W2008R2-SP:30000 . But I am unable….

My Problem
When I open the browser for http://bi.andromeda.com , browser prompts me the authentication dialog box and asks for an user id and a password. It prompts three times even with correct user id and password and then opens a blank page. Could not figure out the reason for a while, finally Prabath sent me an URL that contains a solution for this. The solution is given by Tech Battle Field and post is http://spiderwool.blogspot.com/2010/09/access-denied-host-header-sharepoint.html.

In summary, solution is adding a new Multi-String registry key named BackConnectionHostNames in SharePoint server at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0 and set the host names with values (Read the post for more details). This is what I did:

Finally it worked. Thanks for Tech Battle Field for sharing this and thanks for Prabath for guiding me onto it.

Awarded Microsoft MVP again for 6th time

MVP again!

Microsoft has awarded the title again for the 6th time, thanks for Microsoft, Microsoft Sri Lanka, Wellignton and Lilian for recommending me.

Two new MVPs are introduced; Prabath Fonseka (SharePoint) and Preethiviraj (SQL), Congratulation guys!

Analysis Services Security: Dimension Data – Select all members or Deselect all members?

While configuring Analysis Services security on Dimension Data, I did not pay much attention on two options available with the screen; Select all members and Deselect all members. However the importance of them was raised up when my colleague Asanka Padmakumara showed a point on them. Which one should we select? See the screen below;

I have selected the first option which is Select all members and granted permission on Accessories and Bikes. I can do the same, select the other option too. Let’s move into Advanced section and see how it has been set;

Note that other two categories (Clothing and Components) have been added under Denied member set. What we have set? Yes, we granted permission on Accessories and Bikes but we have done indirectly is, denying permission on Clothing and Components. Main thing we need to consider is, permission on future categories. Since the items we did not select have gone to Denied member list, all future items will be available for this role. If you need to let this role to access future categories without explicitly setting them, use Select all members for setting permission.

Now let’s use the other option; Deselect all members;

I have given same permission set but the option selected is Deselect all members. Have a look on Advanced section now;

Now you can see the items we selected have been added to Allowed member set. This means all other items we did not select are denied to this role. What will happen to future categories? This is the difference. Since we have explicitly added Accessories and Bikes to Allowed member set, future items will NOT be available under Allowed member set. If you need to disallow this role to access future categories without explicitly setting them, use Deselect all members for setting permission.

Analysis Services Security: DENY does not override GRANT

Is the famous rule of thumb broken? I am sure that you all are aware the rule and you know that DENY always wins over GRANT. If you want to see an exception (or a situation) read one of my previous posts “Column GRANT overrides DENY TABLE, Avoiding with Common Criteria Compliance”.

This post discusses another situation, it is not with SQL Server Relational Engine, it is will SQL Server Analysis Services.

Analysis Services behaves bit differently. As you know, if an object of SQL Server database is denied explicitly, it is always denied even with an explicit grant. For example, if you are in a Role that grants SELECT on Table1 and in another role that denies SELECT on Table1, you will not be able to execute SELECT on Table1. Analysis Services authorizes on items differently. It unions all grants and authorizes. If you are in a Role that grants on Item1 and Item2 (Note that in Analysis Services, once items are granted, all other items are denied, no need to explicitly deny them as SQL Server database engine objects) and in another Role that grants Item2 and Item3, you are granted for Item1, Item2, and Item3.

Let me show you a sample on this. The below image shows a Role in Analysis Services Adventure Works DW 2008R2 database. This is how it is created;

• Name: Business Users
• Membership: Dinesh
• Cubes: Adventure Works – Read
• Dimension Data:
  • Product Dimension, Category – granted for Accessories and Bikes
  • Other Dimensions – granted for all

Second image shows another role in same database. Its properties are as follows;

• Name: Coordinators
• Membership: Dinesh
• Cubes: Adventure Works – Read
• Dimension Data:
• Product Dimension, Category – granted for Bikes and Clothing
• Other Dimensions – granted for all

Business Users role denies for Clothing and Components. Coordinators role denies for Accessories and Components. When Dinesh sees data, he sees union of both; Accessories, Clothing, and Bikes. The reason for this is, Analysis Services uses all GRANTs from all Roles for connected user and then authorizes. That is why he sees Accessories and Clothing even though they are denied from each role. This is how he sees when Categories are loaded with Excel.

Note: This type of security cab be tested with Management Studio too. Start Cube Browser and click on Change User icon. The opened window allows you to set the context with Current User, Other User, or Roles.

Way to Visualize: Project Crescent; What is it?

The Era that Business User is used to carry an armload of tools for analyzing and visualizing data, has come to an end!!!!! Yes, Microsoft never stops making business user’s life easier. Microsoft never stop supporting business user to manage and visualize data in a rich way. Microsoft has come out with an exciting facility; The Project Crescent.

What is Project Crescent?

Project Crescent is the recent Microsoft Business Intelligence team innovation that helps to manage and visualize large amount of data quickly in very rich format. It has been built entirely on Silverlight and will be available as a BI client. It allows to show the insights of the business with reports, with highly interactive visualization and animations.

The releasing date of this is unknown. Most probably it will be released with Denali, at the end of 2011 or beginning of 2012. Long time to wait…..

Here is a sneak peak of project Crescent, in Amir Netz’ demo at PASS Summit 2010:

Here is a post from SQL Server Reporting Services team blog: http://www.silverlightshow.net/news/Project-Crescent-built-entirely-on-Silverlight.aspx

Reporting Services: Difference between RDL and RDLC files

Microsoft Reporting Services is an enterprise-capable reporting solution that can be used to create all types of reports such as Production, Analytical, and Dashboard. Building reports is done through Business Intelligence Development Studio which is an extension of Visual Studio. Reports are created as RDL (Report Definition Language) files. Once the reports are created with BIDS, they (RDLs) are hosted in Reporting Services and users can be accessed them in many ways.

What are RDLC files?
RDLC: Report Definition Language, Client Side files are files that are generated with either ReportViewer Control that comes with Visual Studio 2008 Windows or ASP.NET project templates, or Reporing –> Report Application project template. It is a XML file, just like the RDL. It uses the same XML schema that is used by RDL files but RDLC does not require to have values for all elements such as query text. If RDLC file is needed to be hosted in Reporting Services, it needs to be renamed as RDL and all empty elements must be filled manually.

Reporting with ReportViewer Control
The ReportViewer Control allows us to embed a report to ASP.NET web page or Windows form. It supports two modes: Remote Processing mode and Local Processing mode. The Remote Processing mode allows to connect with reports that have been already deployed to SSRS instance. In this case, all the processing take place at the SSRS server. The Local Processing mode allows to create reports within Visual Studio itself. The extension of them are RDLC. This does not require an instance of Reporting Services, hence processing is handled by the client application.

Here are some of important points to remember about these two modes:

• Local Processing mode does not require an instance of Reporting Services, hence a license for Reporting Services is not required.
• Local Processing mode is not recommended for large reports. It is used for small, OLTP types of reports. They may run infrequently.
• Remote Processing can be scaled out but Local Processing.
• Local Processing mode supports following extensions only:
• Visual Studio 2008: PDF and Excel
• Visual Studio 2010: PDF, Excel and Word
• Local Processing mode with Visual Studio 2008 does not support Tablix data region. Visual Studio 2010 supports.
• Mapping parameters with query parameters has to be done manually with Local Processing mode.