Ravi S.Maniam's Blog

Kilimanjaro is now SQL Server 2008 R2. This is the next version of SQL Server 2008 that will give your organization a power steering to drive the Business Intelligence efforts much faster & focused in your organization. Why I say that it is the next version of SQL Server 2008 instead of “SQL Server” is because Kilimanjaro has extended the existing capabilities of SQL Server 2008.

Thank you very much for visiting this article. In case if you are not on the MSDN blogs then I would request you to please visit my blog at http://blogs.msdn.com/manisblog because at times I improve the existing articles after reading emails from people who enthusiastically provide their feedback. These improvements might not be reflected on the other blog sites who have indexed this blog post.

The Kilimanjaro comes with some of the most powerful features and most desired features for Business Intelligence that will allow the organizations to broaden the reach of BI to every section of the business helping business to take faster, better and more relevant decisions. SQL Server 2008 R2 will help you to unlock your data & produce meaningful information in no time which in turn will empower the end users to take reliable & relevant decisions.

SQL Server 2008 R2 is presently in the CTP (Community Technology Preview) stage it can be downloaded from the Microsoft Website : http://technet.microsoft.com/en-us/evalcenter/ee315247.aspx . I started using the SQL Server 2008 R2 since last a few days and among the features I used, I find them all very compelling and convincing.

Some of the features / project that SQL Server 2008 R2 comes with are worth mentioning like the “Project Madison” which is a highly scalable and high performance data warehouse appliance that will deliver amazing performance at low cost through a massively parallel processing (MPP). Moreover it allows you to scale your data warehouse from Terabyte to Petabyte range on an Industry Standard Hardware. The best part is that “Project Madison” will complement your existing SQL BI solution & ongoing BI efforts because the appliance model of “Project Madison” simplifies deployment and maintenance, it integrates with existing SQL Server 2008 data warehouses via hub-and-spoke architecture and the fruits are greater ROI from BI investments through integration with SQL Server 2008.

A sample of scale out data warehouse with “Madison”.

Picture Source : Click Here

Do you love MS-Excel for analyzing your business results ? The project “Gemini” in an innovative solution that allows you to analyze your data on your most loved tool MS-Excel. This Excel add-in enables the Excel power users to easily create powerful BI solutions by streamlining the integration of data from multiple sources enabling interactive modeling and analysis of massive amount of data. “Gemini” also supports the effortless sharing of data models and reports through Microsoft Office SharePoint 2010.

A quick look at the “Gemini” dashboard

Picture Source: Click Here

People are using SQL Server Reporting Services for sometime now, they have developed their critical and important reporting solutions with SQL Server Reporting Services. So now the SQL Server 2008 R2 comes with an enhanced SQL Server Reporting Services which also has a new and enhanced Report Builder component the “Report Builder 3.0”. The Report Builder 3.0 accelerates report creation, collaboration and consistency. The Reporting Services now allows rich visualization of geospatial data which includes mapping, routing, and custom shapes that can help the end users to create customized reports that leverage existing content objects, such as queries, data regions, and charts and graphs. The users can also enhance location-based data reports with Bing Maps in Report Builder 3.0.

There are many more exiting features that I am exploring now like, the UCP (Utility Control Point) that will help the DBAs with the manageability or the MDS (Master Data Services) that helps to improve the quality of the data and many more….. If you have not explored the features of SQL Server 2008 R2 then you may do it now… Download the latest SQL Server 2008 R2 August CTP from : http://technet.microsoft.com/en-us/evalcenter/ee315247.aspx

I will get back to you soon with my articles on the SQL Server 2008 R2 features till then keep visiting my blogs and keep writing to me !

SQL Server 2008 introduces an enhancement to the way the indexes are created which helps to get better performance from the same queries. This new little guy is known as ‘Filtered Index’. This is actually an Optimized Non-Clustered index which is well suited to cover the subset of data with in a table.

Every organization has a specific data usage pattern and if we have an idea of the data usage pattern then we can create the filtered indexes to cover the subsets of data that can actually yield faster results from our same query / stored proc.

Let’s say the sample database “AdventureWorks” is used by a company that sells spare parts and they have different departments that sell specific products and lets say the racing bikes being the hottest in the market, it drives more sales for the racing bike spare parts. This means that more transactions are being done related to a specific product(s) so most of the times the data is filtered, is based on a specific Product Id. Moreover if there are millions of Rows in the table, it becomes really necessary to choose the indexes correctly and carefully because of the storage space involved to store them.

For example the queries (like the following one) are executed based on a selection criteria very frequently then the filtered indexes will help you a lot.

Let see generally how indexes speed up the queries. During the SELECT, UPDATE, DELETE operations the SQL optimizer will look for indexes and determine the most efficient method to complete an operation. In this process SQL Optimizer determines to use an index or to go for a table scan. The SQL Optimizer does this operation to reduce the query execution time and improve the performance.

Index seeks are preferred for the selective queries like the one above and the index seeks happen only when the optimizer finds the index on the table useful, otherwise it will scan all the records. Generally, searching an index to spot a matching record is faster than a table scan because it is ordered and less number of columns means small set of data which in turn means less hops to find the match. Unique indexes are faster and table partitions speeds it up more. Well ….discussing the anatomy of indexes and strategy for indexing the tables is a huge subject and there are books available for that so …let me get back to the topic where I left it.

So why a filtered index ? A filtered index can be used as an index for a subset of data rather than the entire set of data. The filtered index takes less space to store the index because it stores only index information related to a subset of data unlike an usual non-clustered index. In no way this is a substitute for  a regular Clustered and Non-Clustered indexes. This is an additional enhancement that can help you in tuning specific highly selective queries.

What are the advantages of Filtered Indexes :

Improved query performance and plan quality

A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table non-clustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

Reduced index maintenance costs

An index is maintained only when data manipulation language (DML) statements affect the data in the index. A filtered index reduces index maintenance costs compared with a full-table non-clustered index because it is smaller and is only maintained when the data in the index is affected. It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.

Reduced index storage costs

Creating a filtered index can reduce disk storage for non-clustered indexes when a full-table index is not necessary. You can replace a full-table non-clustered index with multiple filtered indexes without significantly increasing the storage requirements.

You can also determine the space required for an index. Please visit : http://msdn.microsoft.com/en-us/library/ms191163.aspx 

How to create a Filtered Index ?

Let’s see an example :

So simple .. just add a WHERE clause to the index. You need to choose the WHERE clause carefully.

When to Use Filtered Indexes

Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. Examples are:

• Sparse columns that contain only a few non-NULL values.
• Heterogeneous columns that contain categories of data.
• Columns that contain ranges of values such as dollar amounts, time, and dates.
• Table partitions that are defined by simple comparison logic for column values.

Reduced maintenance costs for filtered indexes are most noticeable when the number of rows in the index is small compared with a full-table index. If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. In this case, you should use a full-table index instead of a filtered index.

Filtered indexes are defined on one table and only support simple comparison operators. If you need a filter expression that references multiple tables or has complex logic, you should create a view.

Please visit books online to know more about filtered indexes and keep reading my blogs.

Bye Now !! :-)

We create ASP.Net applications which is hosted at a Central Location say Seattle (USA) but the customers using the application are located all around the world and when they enter the transaction they want their local time to be considered but when we call the function GetDate() the SQL Server will take the server time into consideration. This is a classic problem & people have come up with various ways to tackle this problem but with SQL Server 2008 and DateTimeOffset feature it has become a bit easier.

So how to do it…. When the user registers herself to the ASP.NET application for the first time you can ask the Country and State of residence of this user which is stored in the database. Now, when the transaction is posted you can do the following to get the user’s local time of the transaction.

Thank you very much for visiting this article. In case if you are not on the MSDN blogs then I would request you to please visit my blog at http://blogs.msdn.com/manisblog because at times I improve the existing articles after reading emails from people who enthusiastically provide their feedback. These improvements might not be reflected on the other blog sites who have indexed this article.

Step 1:

I created a table called TimeOffsets with two Columns, Location and TimeOffset with a Primary Key on ‘Location’.

Step 2:

I entered some values into this table. It is a little time consuming but it is a one time job. For your help I have attached an Excel Document along with this blog post so you can import the values into the SQL Server Table.

NOTE: The Time Zone Offset that I am providing below is not a complete list and it is not daylight saving aware. You have to make the changes accordingly. This is just a sample.

Step 3:

Create a SQL Function that will return the value of type DateTimeOffset.

Step 4:

Now say suppose your Local TimeZone is “Delhi” (India) i.e. your server is located in Delhi and the local time zone is set for the server and you want to find out what is the current time in Los Angeles (USA) then you will call this function as follows:

This will yield the current time at Los Angeles.

NOTE: I am attaching the SQL Script to create the Table and the Function as well as the Excel file containing the Time Zone Offsets along with this post. You can download it from here.

Thank you for reading this post … and keep a watch on my blog for newer posts.

Recently, I attended the Tech-Ed 2009 at Hyderabad (India). In the Application Lifecycle Management track the Visual Studio Team Test team showcased the new features of Visual Studio 2010 Team Test features. Usually, I write my blogs with screenshots and samples but this time I could not resist myself from telling my first hand experience with the cool features of VSTS 2010.

Mr. Amit Chatterjee delivered the first session on the VSTT 2010 features in the Tech-Ed on 13-May-2009. The theme was ‘Visual Studio 2010 has a new BUG’. Yes, the Visual Studio Team test has a new “BUG” i.e. the BUG Workitem. You will say ‘what is so new in that ?’  it is already there in the Visual Studio 2008 but this is really new & exciting …

… now in the Bug WorkItem you can automatically record the test and attach the screenshots & the video to the Bug workitem and it happens automatically once you turn on a couple of switches. Now Visual Studio will have the Lab management capabilities so if you want to test your application on a given platform you can do that. Rather than giving each tester additional set of PCs to test the application, you can put all these PCs in a Pool. These PCs will host the Hyper-V VMs specific to your test environment. The tester can test your applications thoroughly in this environment, preserve the environment, if something goes wrong - they can always step back to the previous setting and finally when everything done – they can tear down this VM to make additional room for other VMs. The coolness in this feature is … the Developers & Testers need not to be an IT-Pro or an Administrator to stage all this environment, the VSTS provides the connectivity to System Center Virtual Machine Manager and from within the VSTS you can initiate this environment and manage it. This will help the customers spend less from their IT budget and enhance the productivity of the testers. If you remember, I said “ENVIRONMENT” i.e. you can even test your distributed applications. These features were demonstrated by Shay Mandel.

One of the other cool feature that I loved the most is the Coded UI test. I work with a lot of ISVs and it has been a constant demand that they need to have some tools to test the UI. Now the VSTS 2010 comes with an ‘INTELLIGENT’ test recorder. ‘I want to emphasize the word INTELLIGENT’ because unlike the usual test tool that records the (X,Y) coordinates and breaks when the screen resolution changes, the VSTS 2010 records the details of the controls used on the web pages and creates the C# or VB.Net code in the background so now you can either play it back and write a few lines of code to test this application’s UI against the million possible values. You can even fetch the data from your database and play it on the UI to check if any of these values break the UI, just by writing simple code.

Check out for more in the near future…

I attended the Microsoft Tech-Ed event @ Hyderabad (India) on 13,14,15th of May 2009 i.e. the last week. There were a lot of exiting sessions for Developers, Architects and IT Pros. The podium was setup in a large hall with all vibrant colors.

The Rock Band – Agnee http://www.agneelive.com started the session with Rock Music – Jee Le Baby !! This song was specially composed for Microsoft Tech-Ed. It is a very cool song. Everyone in the audience was very restless to see Steve Ballmer with a lot of expectations and questions, glittering their eyes.

It started with a Key Note by our CEO - Steve Ballmer. Everyone cheered loud when Steve entered the hall. Steve started with the economic slow down and he shared his vision of optimism towards the future. People had a lot of questions for him starting with Windows 7 to economic slow down. Steve said that very soon Windows 7 will be released and there is a lot of buzz in the industry. People who are using the pre-release version of Windows 7 are already liking it very much. Some SQL Server MVPs had the question on SQL Server’s future release and Steve said “Keep a watch !!”. Steve is very optimistic about the release of the latest Microsoft software in the near future like Windows 7, VSTS 2010 etc. There was a question around the Microsoft’s future plans in India. Steve said that, he sees a great talent pool in India. Steve sees the current “Economic Reset” as a half glass full, he energized everyone with positive energy. The key note ended with loud cheers from the audience.

Overall the arrangements were done very well. From the registration desk, upkeep of the hotel, certification kiosks to the food, everything was well managed. The event organizers wearing the green T-shirts were always ready to help and guide. There were some certification kiosks setup where people played games and won the vouchers. The XBox counter was the one I liked the most. There were many more counters from Microsoft learning, Citrix, Intel, Kingfisher Airlines etc. Overall it was very informative. The security arrangements, quality of food and beverages are worth mentioning.

This event was co-sponsored by many other partners including the news paper, Economic Times http://economictimes.indiatimes.com/.

There were many tracks in the Tech-Ed like the Developer Track, IT-Pro track, Business Intelligence Track, Architecture Track, Application Lifecycle Management Track. I attended the Business Intelligence Track and the Application Lifecycle Management Track.

The session by Amit Chatterjee on Visual Studio Team Test is the one I liked the most. He talked about the new capabilities in Visual Studio 2010. I attended the detailed session on VSTS 2010 Lab Management and Visual Studio Team Test 2010 called as “Tech Tent” delivered by Shay Mandel, I was simply spell bound. The new VSTS 2010 carries so many cool features. The entire Visual Studio Team Test team, including Eyal Melamed, Neelesh Kamkolkar, Vishal Mehrotra, Vinod Malhotra and others were very helpful, addressed every question that came up during the session and took a note of every feedback.

I will write about the new features of VSTS 2010 and other sessions I attended very soon … so keep a watch on this blog. Cheers !!

The official Tech-Ed site is : http://www.microsoft.com/india/teched2009/

I am glad to see people realizing the value of SQL Server 2008 platform and they are happily migrating to the SQL Server 2008 platform.

Thank you very much for visiting this article. In case if you are not on the MSDN blogs then I would request you to please visit my blog at http://blogs.msdn.com/manisblog because at times I improve the existing articles after reading emails from people who enthusiastically provide their feedback. These improvements might not be reflected on the other blog sites who have indexed this article.

Hmm.. what am I going to cover today.... it is something that we all expect from a SQL Server database i.e. performance and I am going to write it in the same fashion that you like most i.e. simplify the contents, make it easy to understand and provide the steps with pictures.

Introduction

We all know we have to work with loads of data every day and over the time with the growth of the organization the data bank also grows tremendously. We see there is a performance degradation after few years or months. Is it the same SQL Server that was working efficiently in past is now getting slower, it is not the SQL Server binaries that have changed, it is still the same efficient SQL Server but the growth of data and the way it is structured plays a big role in the performance.

In my experience I have seen that an inefficient query, badly built index, poorly structured database can eat up the entire resources on the server. If the best practices are followed then you will get good performance from the SQL Server. For the best practices on Physical Design of SQL Server Database visit: http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx

Today I am going to discuss how easy it is for us to partition our tables for better performance with SQL Server 2008 (Katmai). I am not going to cover what is the criteria for partitioning your data because it is a long conversation topic. I am just going to highlight the SQL Server 2008 Management Studio's feature for table partitioning.

Partitioning Steps

You should partition your tables that has accumulated a lot of data over the time and you find that the performance is slowing down. It would not be a very good idea to partition a table it has  just a couple of hundred rows for example : Product Category table. You could partition your tables that contains a lot of transactions like Purchase Order tables, Sales Order tables, General Ledger Tables etc.

Step 1:

Open the SQL Server 2008 management studio and select the appropriate table that needs to be partitioned. Right click on the table and in the context menu select Storage >> Create Partition.

Step 2:

I am using the [Purchasing.WorkOrder] table in the 'AdventureWorks' sample database. When you select the above option a wizard dialog box appears, press the next button once.

You will see a dialog box with the columns available for partition with data type, length etc. Select the column on which you would like to create a partition and press next.

Step 3:

Provide the name of the partition function and press Next.

Step 4:

Provide the name of the partition scheme and press next.

Step 5:

You have to select the option Left Boundary or Right Boundary and then click the button that says Set Boundaries.

Left boundary
The Boundary column label in the grid will dynamically display <= Boundary when you select Left boundary.

Right boundary
Select to include range values up to the specified value in the Boundary column for each filegroup selected. The specified value will be the starting value for the range values of the filegroup on the next row. The Boundary column label in the grid will dynamically display < Boundary when you select Right boundary.

A small dialog box appears with the caption "Set Boundary Values". Select the starting and ending date and select the Date Range from Monthly, Yearly, Quarterly, Half-Yearly , Daily and press OK. I am using End Date field for the partition in this demo.

The above option will create the partition ranges for you. Select the appropriate File Group for each partition in the grid and then click on the 'Estimated Storage' button which will give you the details of the required space. Once you are happy with the results, please press 'Next'.

Step 6:

After pressing the 'Next' button you will get the option to create a script or to run it immediately or to schedule it for a later time. Depending on your requirements you can select the option and press the finish button.

See it's so easy to partition your tables and achieve better performance. Please keep a track of my blogs because I will soon come up with more blog posts, if you have any feedbacks or if you liked this article then please drop me an email. Thank you.

People often ask me how to deploy SQL Server 2008 Express with applications as a prerequisite with Visual Studio 2008. Most often this situation is encountered when people want to deploy applications using the ClickOnce or they want to build a MSI package with SQL Server 2008 using Visual Studio 2008 and include the SQL Server packages with the setup.

Now I am providing you with a set of steps to demonstrate deployment of SQL Server 2008 with your application as a prerequisite. You can try these steps on your test environment first. SQL Server 2008 Express needs Windows Installer 4.5 and .Net 3.5 SP1 Framework.

In case if you are not on the MSDN blogs then I would request you to please visit my blog at http://blogs.msdn.com/manisblog because at times I improve the existing articles after reading emails from people who enthusiastically provide their feedback. These improvements might not be reflected on the other blog sites who have indexed this article.

1. Download and install Visual Studio 2008 Express edition because the SQL Server 2008 bootstrapper will be found with the VS2008 Express. Now the steps that I am suggesting would be done using your usual VS 2008 SP 1 development environment like VS 2008 Professional, Team Suite etc.
   
   NOTE: If you are using any other language version other than English like German etc. then please download the appropriate language version of Visual Studio 2008 Express and SQL Server 2008 express.
   
   Visual Studio 2008 Express and SQL Server 2008 Express could be downloaded from : http://www.microsoft.com/express/download/ 
   
2. Using VS 2008 Development Environment, create a small .Net 3.5 Windows application that connects to SQL Server 2008 Express Edition.
3. Add a Setup Project to this Visual Studio 2008 Solution.
4. Add the project output of the Windows Application to the setup project.
5. In the setup project you would not be able to locate Windows Installer 4.5, .Net 3.5 SP1 and SQL Server 2008.
6. Now download all the files from http://www.microsoft.com/downloads/details.aspx?FamilyId=5A58B56F-60B6-4412-95B9-54D056D6F9F4&displaylang=en  to the Bootstrapper/packages folder which looks like C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages. This will provide you with the Windows Installer 4.5 Bootstrapper.
7. Download SQLEXPR_x64_ENU.exe , SQLEXPR_x86_ENU.exe, SQLEXPR32_x86_ENU.exe from http://www.microsoft.com/downloads/details.aspx?FamilyID=58ce885d-508b-45c8-9fd3-118edd8e6fff&DisplayLang=en and place them in the SQLExpress2008/en folder within the Bootstrapper/packages folder. This makes sure that the SQL Server 2008 Express is present.
8. Now to download the .Net 3.5 SP1 framework please navigate to http://download.microsoft.com/download/2/0/e/20e90413-712f-438c-988e-fdaa79a8ac3d/dotnetfx35.exe and download the 231 MB file.
9. At the command prompt navigate to the folder that contains the dotnetfx35.exe file and type : dotNetFx35.exe /x:. This will extract the Framework files to a folder named "WCU" in the current directory.
10. Copy the contents of the WCU\dotNetFramework folder and paste them in the %Program Files%\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\DotNetFx35SP1 folder (%ProgramFiles(x86)%\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\DotNetFx35SP1 on x64 operating systems). Note: Do not copy the WCU\dotNetFramework folder itself. There should be 5 folders under the WCU folder, and each of these should now appear in the DotNetFx35SP1 folder. The folder structure should resemble the following:

    DotNetFx35SP1 (folder)
    dotNetFX20 (folder
    dotNetFX30 (folder)
    dotNetFX35 (folder)
    dotNetMSP (folder)
    TOOLS folder)
    en (or some other localized folder)
    dotNetFx35setup.exe (file)

11. Now for the successful installation of the .Net 3.5 SP1 Framework on the client computer you need to take further following actions so that the framework installs successfully. After these steps you would be able to add .Net 3.5 SP1 Framework, Windows Installer 4.5 and SQL Server 2008 Express with your application setup.

Open the [Program Files]\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\DotNetFx35SP1 folder or %ProgramFiles(x86)%\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\DotNetFx35SP1 on x64 operating systems Edit the Product.xml file in Notepad. Paste the following into the <PackageFiles> element: <PackageFile Name="TOOLS\clwireg.exe" /> <PackageFile Name="TOOLS\clwireg_x64.exe" /> <PackageFile Name="TOOLS\clwireg_ia64.exe" /> Find the element for <PackageFile Name="dotNetFX30\XPSEPSC-x86-en-US.exe" and change the PublicKey value to: 3082010A0282010100A2DB0A8DCFC2C1499BCDAA3A34AD23596BDB6 CBE2122B794C8EAAEBFC6D526C232118BBCDA5D2CFB36561E152BAE 8F0DDD14A36E284C7F163F41AC8D40B146880DD98194AD9706D057447 65CEAF1FC0EE27F74A333CB74E5EFE361A17E03B745FFD53E12D5B0CA5 E0DD07BF2B7130DFC606A2885758CB7ADBC85E817B490BEF516B6625D ED11DF3AEE215B8BAF8073C345E3958977609BE7AD77C1378D33142F13 DB62C9AE1AA94F9867ADD420393071E08D6746E2C61CF40D5074412FE 805246A216B49B092C4B239C742A56D5C184AAB8FD78E833E780A47D8 A4B28423C3E2F27B66B14A74BD26414B9C6114604E30C882F3D00B707 CEE554D77D2085576810203010001 Find the element for <PackageFile Name="dotNetFX30\XPSEPSC-amd64-en-US.exe" and change the PublicKey value to the same as in step 4 above Save the product.xml file

NOTE : If the table above is not properly displayed then you can refer Section 2.3.1.1 in readme file. In the section 2.3.1.1 you would get the options to install additional language packs as well. You should copy the PUBLICKEY from the URL below. Please navigate to the following URL:

http://download.microsoft.com/download/A/2/8/A2807F78-C861-4B66-9B31-9205C3F22252/VS2008SP1Readme.htm.

I referred the following documents / blogs to put these steps together :

1. http://download.microsoft.com/download/A/2/8/A2807F78-C861-4B66-9B31-9205C3F22252/VS2008SP1Readme.htm#Product%20Issues
2. http://blogs.msdn.com/sqlexpress/archive/2008/09/12/faq-installing-the-sql-server-2008-express-clickonce-bootstrapper-for-visual-studio-2008-sp1.aspx 

Thank you very much for visiting my blog and this article. Please keep a watch on this blog for new posts.

Hi Friends, I was a bit caught up during these days so I could not update my blogs since a long time. Today again it is a weekend and I have some time so I am writing the article on Resource Governor Part II which is overdue since a long time.

In my first post you would have seen what is the advantage of using Resource Governor and how it works. In this post I will provide you with some sample code. The scenario is that you have one SQL Server 2008 installation and it is used by all the people in the organization. Sales people use it for querying historical sales data, senior executives use it for taking out reports and other useful data and at the same time other people use it for data entry, ad-hoc queries etc. Eventually the DBA finds that the server gets into run-away situation during peek hours. So now the DBA decides to allocate bandwidth each of these user groups.

Let's see a demo script how a DBA will do that.

NOTE : This script was written to execute well on my Laptop so please avoid copy + paste and execute on your production server. This script is provided just to help users understand the concept.

Sample Code

We will do some settings that needs to be done only once per installation. It is not same for all types of server so please refer to the Books Online before performing these settings.

We can find out the information on the Resource Governor by querying the DMVs (Dynamic Management View). These DMVs provide us with the information on the resource pools, workload groups and the classifier function.

Now we will create two Resource Pools first.

After creating the Resource Pools we will create the Workload Groups and these Workload Groups will be assigned to respective Resource Pools.

In the next step we will create SQL User Logins.

Now when the user logins are created our next task is to create a Classifier Function. Classifier function tells the Resource Governor on how to handle the incoming request. The classifier function has to be created in the Master database. After the creation of the classifier function we have to make the Resource Governor aware of this function.

Now we can set the priority of the Workload Group or the CPU usage by any Resource Pools etc. as demonstrated in the query below.

That is all in this post from my side. If you have any feedbacks then please feel free to share it with me over email.

Hi Friends, I am back with one more blog post on an interesting feature in SQL Server 2008. Today I was discussing about run away situations with my colleagues and I found that only few people read about this excellent feature available in SQL Server 2008 so just thought why not to blog about it.

If you are using SQL Server everyday then you would be aware of the situations when you get into run-away situation during the peak load on the server. For example: there is a heavy transaction volume on the server and someone from the remote end fired an ad-hoc query to churn several tables, running to a few million rows and everything comes to a screeching halt. You would have always wondered if there was a way to manage this to avoid run-away situation. Even if there was any solution that sort you would have wondered can it be done on the fly with a minimal impact.

What is run-away ?

To explain it in a very simplified manner, every server hardware has a finite number of CPUs and amount of RAM installed on it. When you try to fire a query that is resource intensive and the hardware resources are not available to serve the request ..you get into a run-away situation due to which the service requested is delayed and other requests being handled by the server in parallel are also delayed.

So what is the solution ?

In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory.

Resource Governor

Resource Governor is a new technology in SQL Server 2008 that enables you to manage SQL Server workload and resources by specifying limits on resource consumption by incoming requests. In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor.

It is feature that is designed to manage the resource limits in real time with minimal impact on workloads that are executing.

Issues addressed by Resource Governor

Resource Governor addresses 3 primary issues that are prevalent in the database environments.

1. Run-Away : A situation where one process eats up all the resources and another process starves.
2. Workload Priority : A situation where we need to prioritise one workload type over another viz. a current payment transaction over another query that is crunching millions of rows to take out some summarized information which is expected to run for a few minutes.
3. Unpredictable workload execution : A situation similar to this one where two data warehouse applications are a mix of OLTP and data warehouse applications. These applications are not isolated from each other and the resulting resource contention causes unpredictable workload execution.

All of the above scenarios require the ability to differentiate workloads in some way. Resource Governor provides:

• The ability to classify incoming connections and route their workloads to a specific group (Identifying a workload group based on an incoming connection).
• The ability to monitor resource usage for each workload in a group.
• The ability to pool resources and set pool-specific limits on CPU usage and memory allocation. This prevents or minimizes the probability of run-away queries.
• The ability to associate grouped workloads with a specific pool of resources.
• The ability to identify and set priorities for workloads. (Setting the priority or Low, Medium, High)

Scope of Resource Governor

There is a clear scope in which Resource Governor works and it is important to understand the scope of it.

• It is only manages the CPU Bandwidth and the RAM managed by the SQL Server 2008.
• Resource Governor itself will not optimise any slow running queries.
• No load balancing will be done between multiple SQL Server instances.
• It does not divide the server into isolated pieces.
• OLTP workloads. Resource Governor can manage OLTP workloads but these types of queries, which are typically very short in duration, are not always on the CPU long enough to apply bandwidth controls. This may skew in the statistics returned for CPU usage %.

How Resource Governor Works

The following three concepts are fundamental to understanding and using Resource Governor:

1. Resource pools. Two resource pools (internal and default) are created when SQL Server 2008 is installed. Resource Governor also supports user-defined resource pools.
2. Workload groups. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed. Resource Governor also supports user-defined workload groups.
3. Classification. There are internal rules that classify incoming requests and route them to a workload group. Resource Governor also supports a classifier user-defined function for implementing classification rules.

Explanation (Step by Step):

1. There is an incoming connection for a session (Session 1 of n).
2. The session is classified (Classification).
3. The session workload is routed to a workload group.(Workload Group 1)
4. The workload group uses the resource pool it is associated with, for example, Pool 1.
5. The resource pool provides and limits the resources required by the application, for example, Application 1

For more information on Resource Governor you may visit SQL Server 2008 Books Online.

I will write more about the Resource Governor very soon so keep a watch.

Hi Friends, I am back again with one more blog post on SQL Server 2008 Auditing.

As we all know that SQL Server 2008 is coming up with *Cool* enhancements that will help the End Users, DBAs, Developers in improving their productivity. Today I am going to blog about a feature which would be liked by people who maintain the compliance with the Security Standards.

Before the advent of SQL Server 2008, auditing was done using SQL Server Traces and Profiler. Now Auditing is an integral object in SQL Server 2008.

So what is Auditing in SQL Server..

Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. Based on the information accumulated we would be able to track the changes to the database, access to the database etc. An audit is the combination of several elements into a single package for a specific group of server actions or database actions. The components of SQL Server Audit combine to produce an output that is called an audit, just as a report definition combined with graphics and data elements produces a report. SQL Server Audit uses Extended Events to help create an audit.

While we are working with SQL Server 2008 auditing we need to keep four things in mind:

1. SQL Server Audit
2. Server Audit Specification (Events to capture on the Server Instance Level)
3. Database Audit Specification (Events to capture on a specific database)
4. Target (Where would be the events be logged)

Note: I will be using the definitions In Books Online to explain you these 4 objects because I think they are perfect and very easy to understand.

SQL Server Audit

The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance. When you define an audit, you specify the location for the output of the results. This is the audit destination. The audit is created in a disabled state, and does not automatically audit any actions. After the audit is enabled, the audit destination receives data from the audit.

Server Audit Specification

The Server Audit Specification object belongs to an audit. You can create one server audit specification per audit, because both are created at the SQL Server instance scope. The server audit specification collects many server-level action groups raised by the Extended Events feature. You can include audit action groups in a server audit specification. Audit action groups are predefined groups of actions, which are the atomic events exposed by the Database Engine. These actions are sent to the audit, which records them in the target.

Server-level audit action groups are described in the topic SQL Server Audit Action Groups and Actions.

Database Audit Specification

The Database Audit Specification object also belongs to a SQL Server Audit. You can create one database audit specification per SQL Server database per audit. The database audit specification collects database-level audit actions raised by the Extended Events feature. You can add either audit action groups or audit events to a database audit specification. Audit events are the atomic actions that can be audited by the SQL Server engine. Audit action groups are predefined groups of actions. Both are at the SQL Server database scope. These actions are sent to the audit, which records them in the target. Database-level audit action groups and audit actions are described in the topic SQL Server Audit Action Groups and Actions.

Target

The results of an audit are sent to a target, which can be a file, the Windows Security event log, or the Windows Application event log. (Writing to the Security log is not available on Windows XP.) Logs must be reviewed and archived periodically to make sure that the target has sufficient space to write additional records. Writing to the Windows Security log requires the SQL Server service account to be added to the Generate security audits policy. By default, the Local System, Local Service, and Network Service are part of this policy. This setting can be configured by using the security policy snap-in (secpol.msc). Additionally, the Audit object access security policy must be enabled for both Success and Failure. This setting can be configured by using the security policy snap-in (secpol.msc). In Windows Vista or Windows Server 2008, you can set the more granular application generated policy from the command line by using the audit policy program (AuditPol.exe). For more information about the steps to enable writing to the Windows Security log,see How to: Write Server Audit Events to the Security Log. For more information about the Auditpol.exe program, see Knowledge Base article 921469, How to use Group Policy to configure detailed security auditing. The Windows event logs are global to the Windows operating system. For more information about the Windows event logs, see Event Viewer Overview. If you need more precise permissions on the audit, use the binary file target. For more information about the audit records written to the target, see SQL Server Audit Records.

NOTE: Any authenticated user can read and write to the Windows Application event log. The Application event log requires lower permissions than the Windows Security event log and is less secure than the Windows Security event log.

Step By Step Walk Through

Now when you have understood Auditing Objects in SQL Server 2008, let me walk you through the process of creating an Audit in SQL Server 2008. I bet that it will be a good fun learning this feature in spite of the seriousness of the role of Auditing. In SQL Server 2008, the product team has made sure that the features are simple to use for the end users in spite of the underlying complexity. So let's start and explore.

Note: You can click on the images below to maximize.

Step 1:

Click open SQL Server 2008 Management Studio and log into it.

Step 2:

Explore the Security node in Object Explorer and select the Audit node.

Step 3:

Right click on the Audit node and select the option "New Audit" from the menu.

Step 4:

Now SQL Server 2008 will open up a dialog box "Create Audit" with a few fields. It is important to understand the significance of each of these fields. The details of these fields are provided below.

Audit name
The name of the audit. This is generated automatically when you create a new audit but is editable.

Queue delay (in milliseconds)
Specifies the amount of time in milliseconds that can elapse before audit actions are forced to be processed. A value of 0 indicates synchronous delivery. The default minimum value is 1000 (1 second). The maximum is 2,147,483,647 (2,147,483.647 seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds).

Shut down server on audit failure
Forces a server shut down when the server instance writing to the target cannot write data to the audit target. The login issuing this must have the SHUTDOWN permission. If the logon does not have this permission, this function will fail and an error message will be raised.

As a best practice, this should only be used in cases where an audit failure could compromise the security or integrity of the system.

Audit destination
Specifies the target for auditing data. The available options are a binary file, the Windows Application log, or the Windows Security log. SQL Server cannot write to the Windows Security log without configuring additional settings in Windows. For more information, see How to: Write Server Audit Events to the Security Log.

Note: Writing to the Security log is not available on Windows XP.

File path
Specifies the location of the folder where audit data is written when the Audit destination is a file. Clicking the button beside this field opens the Browse For Folder dialog to specify a file path or create a folder where the audit file is written.

Maximum rollover files
Specifies the maximum number of audit files to retain in the file system. When the setting of MAX_ROLLOVER_FILES=UNLIMITED, there is no limit imposed on the number of rollover files that will be created. The default value is UNLIMITED. The maximum number of files that can be specified is 2,147,483,647.

Maximum file size (MB)
Specifies the maximum size, in megabytes (MB), for an audit file. The minimum size that you can specify is 1024 KB and the maximum is 2,147,483,647 terabytes (TB). You can also specify UNLIMITED, which does not place a limit on the size of the file. Specifying a value lower than 1024 KB will raise the error MSG_MAXSIZE_TOO_SMALL. The default setting is UNLIMITED.

Reserve disk space
Specifies that space is pre-allocated on the disk equal to the specified maximum file size. This setting can only be used if MAXSIZE is not equal to UNLIMITED. The default setting is OFF.

Now after filling up appropriate values in the dialog box, press OK to create an Audit.

Step 5:

Now once the Audit is created, it could be found under the Security>>Audit node. Now to enable the Audit, just right click on the Server Audit that we have just created and from the menu select "Enable Audit".

You would see a dialog box with the success message, if the operation succeeds.

Step 6:

You can right click on the Audit just created and select the option "View Audit Logs". This opens up a dialog box that contains the audit logs.

You can also script the created policy. Right click on the Audit, select "Script Audit As" >> "Create To" >> (File/ Clipboard / Agent Job).

As we know that Policy Based Management is now an integral part of the SQL Server 2008 so if you would like to create a policy for this Audit then it is very easy to do that. You have to right click on the Audit just created, select the option "Facets" from the menu.

It brings up a dialog box that provides you with the status of the Audit. On the lower right corner of this dialog box you would find a button "Export Current State as Policy".

When you click on the button "Export Current State as Policy", it brings up another dialog box that shows the Policy Name and the condition name which is editable. You can select the appropriate option below these fields to apply this policy on the local server or to save it as a Policy file. If you select the "local server" option, you would see that a Policy and a Condition has been created with the name you have specified in the dialog box.

Step 7:

Now we will create the Server Audit Specification.

Before continuing further I would strongly recommend you to visit the TechNet Article : Server Audit Action Groups and Actions.

Once you have understood the Server Audit Action Groups, explore the node Security >> Audits >> Server Audit Specifications in the Object Explorer in the SQL Server 2008. Right click on the Server Audit Specifications and select "New Server Audit Specification".

When you select this option a dialog box appears in which you would specify Server Audit Specification Name and "Server Audit" that you have created in the Step 6. Thereafter you would specify the Audit Action Groups in the grid below. Once you have selected all the required Audit Action Groups, you would press OK and you would see an Audit Specification created for the server. In this Step I have selected the Audit Action Group "Backup Restore Group" and this event is raised whenever a backup or restore command is issued.

Once it is created, you would see that the Audit Specification is in the disabled state. You have to right click on the Audit Specification that you have just created and select "Enable Server Audit Specification" from the menu.

Just after that, you would see a dialog box saying, "The operation was successful" unless you have done something funny to make it fail. :-)

You may right click on the Server Audit Specification and select Facets from the menu. This brings up a dialog box with a button on the right bottom saying "Export Current State as Policy". Click on that button to create a policy as we have done above.

Step 8:

Now let's try backing up a database on the server. I have used AdventureWorksLT database for the demonstration. Then we will see the logs to assure that our Audit is working.

Explore the nodes in the Object Explorer, Databases >> AdventureWorksLT. Right click on the AdventureWorksLT database and select Tasks >> Backup.

This brings up the following dialog box to create a backup of the database. Please fill in appropriate values in the fields of this dialog box and press the button OK. For more information or help on taking backup please visit Books on Line.

You would see the backup successfully completes.

Now right click on the Server Audit we created in the Object Explorer and select the option View Audit Logs from the menu.

This will bring up the dialog box that will show the details of the backup event. In the picture below you would see the back up event that happened on the AdventureWorksLT database. You can scroll to the right to find the details in the dialog box.

Step 9:

Now we would create a Database Audit Specification. I would use the AdventureWorksLT database for the demonstration. You may visit the TechNet Article : Server Audit Action Groups and scroll down to "Database-Level Audit Action Groups" and "Database-Level Audit Actions" sections in this article to find more information.

The scenario is we want to Audit every select, insert, update, delete operation done on the SalesLT.Product table by anyone.

Explore the Database >> AdventureWorksLT >> Security >> Database Audit Specifications nodes in the Object Explorer. Now right click on the "Database Audit Specifications" node and select "New Database Audit Specification" from the menu.

This will open up a new dialog box with the Title - "Create Database Audit Specification".

Now we need to Give the Database Audit Specification a name and then select the Server Audit name from the drop down list.

Within the grid in the "Audit Action Type" choose "SELECT" from the drop down list, in the "Object Class" select "OBJECT". Click on the button beside Object Name text box, which opens up a dialog box. Select SalesLT.Product table which will bring "SalesLT" in "Object" and "Product" in "Object Name". Now click the button beside the text box under the section "Principal Name", select "public" by browsing the objects in the dialog box that appears once you click the button.

Repeat the operation for the Audit Action Type INSERT, UPDATE, DELETE. Then finally press OK.

This will create a Database Audit Specification for you which is not enabled. Right click on this Database Audit Specification and select "Enable Database Audit Specification" from the menu.

Once it the operation completes.. a dialog box appears confirming the success of the operation.

You can create a policy out of this Database Audit Specification by right clicking on the Database Audit Specification and selecting the option "Facets" and following the procedure in the similar way how we have done previously.

Step 10: (Last Step)

Now we would fire the query "Select * from SalesLT.Product" against the AdventureWorksLT database.

After that you may explore the Security Node in the Object Explorer. <SQL Instance Name> >> Security >> Audits and then right click on the server audit that is already created and select "View Audit Logs" from the menu.

Now the dialog box appears, it has the audit log that says that you have selected the SalesLT.Product table in the AdventureWorksLT Database. You may scroll to the right in the dialog box to find detailed information.

Wow! You have learned to work with this new new feature in just 10 steps.

Wasn't that very easy.

Thanks guys, to take a look at this post, I hope you enjoyed the article. Your comments and feedbacks are valuable so drop me an email. I would be happy to hear back from you, it gives me an opportunity to improve and gives me pleasure to bring new articles.

I will bring in more articles soon so keep a ...

  Bye now !

NOTE: Declarative Management Framework has been renamed to Policy-Based Management so I just changed the Title of the article, the contents are unchanged.

Hi, I am writing this post after somewhat long time … I hope you all are continuing with your learning on SQL Server 2008. Today I have some time to blog so I am writing something.

So after thinking hard what to write about I thought why not to write about the Declarative Management Framework. A wonderful solution for the DBAs who keep themselves busy patching and closing the mouse holes what other folks have created by violating some of the best practices, may it be naming convention of the Tables or they have created the Stored Proc. in the wrong schema. So if you are wondering what is this “Declarative Management Framework” then, please stop wondering and download the latest SQL Server 2008 November 2007 available, install it, read this article and start practicing. I promise you will like it.

What is Declarative Management Framework

Declarative Management Framework is a policy-based system for managing one or more instances of SQL Server 2008 which means that now rather than turning on/off the physical knobs to do the effective database administration you would have logical knobs to do it which could even be exported to a file for the future use and could be applied to a bunch of SQL Servers in a Group.

DBAs don't have to worry, it is not any other Framework like .Net Framework where you need to reach the Visual Studio and write custom code, test it, deploy and manage it. It is very simple and all the policies could be authored well from the SQL Server 2008 Management Studio.

Components of Declarative Management Framework

Declarative Management Framework has three components:

1. Policy management
2. Explicit administration
3. Automated administration

Policy Management

Policy administrators create policies.

Explicit Administration

Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly make the targets comply with a policy.

Automated Administration

Policy administrators can run policies on demand, or enable automated policy execution by using one of the following execution modes:

• Changes are attempted, prevent out-of-compliance. This uses DDL triggers to prevent policy violations.
• Changes are attempted, log out-of-compliance. This uses event notification to evaluate a policy when a relevant change occurs.
• On schedule, log out-of-compliance. This uses a SQL Server Agent job to periodically evaluate a policy.

When automated policies are not enabled, Declarative Management Framework will have no effect on system performance. If enabled the system will have only negligible effect on the performance depending on the number of policies applied on a particular object.

Declarative Management Framework - Key Terms and Concepts

Managed Target
Entities that are managed by Declarative Management Framework, such as an instance of the SQL Server Database Engine, a database, a table, or an index. All targets in a server instance form a target hierarchy. A target set is the set of targets that results from applying a set of target filters to the target hierarchy, for example, all the tables in the database owned by the HumanResources schema.

Facet
A set of logical properties that model the behavior or characteristics for certain types of managed targets. The number and characteristics of the properties are built into the facet and can be added or removed by only the maker of the facet. A target type can implement one or more management facets, and a management facet can be implemented by one or more target types.

Condition
A Boolean expression that specifies a set of allowed states of a Declarative Management Framework managed target with regard to a management facet.

Policy
A Declarative Management Framework condition and the expected behavior, for example, execution mode, target filters, and schedule. A policy can contain only one condition. Policies can be enabled or disabled.

Category
A user-defined category to help manage policies. Users can classify policies into different policy categories. A policy belongs to one and only one policy category. Database owners can subscribe a database to a set of policy categories. Only policies from its subscribed categories can govern a database. All databases implicitly subscribe to the default policy category.

Execution Mode
Specifies how a policy will be executed. On-demand execution modes are Check and Configure. Automated execution modes are:

1. Changes are attempted, prevent out-of-compliance
2. Changes are attempted, log out-of-compliance
3. On schedule, log out-of-compliance

Effective Policy
The effective policies of a target are those policies that govern this target. A policy is effective with regard to a target only if all the following conditions are satisfied:

• The policy is enabled.
• The target belongs to the target set of the policy.
• The target or one of the targets ancestors subscribes to the policy group that contains this policy.

Usage Scenarios : Examples

Declarative Management Framework would be helpful in resolving the issues presented in the following scenarios:

• A company policy prohibits enabling Database Mail or SQL Mail. A policy is created to check the server state of those two features. An administrator compares the server state to the policy. If the server state is out of compliance, the administrator chooses the Configure mode and the policy brings the server state into compliance.
• The AdventureWorks database has a naming convention that requires all stored procedures to start with the letters AW_. A policy is created to enforce this policy. An administrator tests this policy and receives a list of stored procedures that are out of compliance. If future stored procedures do not comply with this naming convention, the creation statements for the stored procedures fail.

A Quick Walk Through : Declarative Management Framework

We are going to create a quick policy that will make sure that the CLR integration is not enabled on this instance of SQL Server 2008. I am adding screen shots below, in case they are not clearly visible, click on them and they will be enlarged.

Step 1: We will reach the SQL Server 2008 Management Studio, log in and then expand the Management Node as demonstrated below.

Step 2: Now we will first create a condition so expand the node CONDITION and right click and in the menu select "New Condition". A new dialog box appears, in the "Name" field type in a name for this condition like "Condition_Disable_CLR" or any fancy name you like. In the Facet select "Server Configuration". Please see the screen shot below.

Step 3: In the Expression section reach the Field and select "@ClrIntegrationEnabled" from the Drop Down, Operator should be "=" and in the Value field select "False" from the Drop Down. Press OK and you have created a condition.

Step 4: Now it is the time to create a policy based on this condition. So right click on the "Policies" node in the Management Studio. Select "New Policy" from the menu. This brings up a new dialog box. In the "Name" field type in a name for the policy like "Policy_Disable_CLR". In the "Check Condition" select the condition that you have authored last in this case "Condition_Disable_CLR" which you would find under "Server Configurations". Select the "Execution Mode" as "On Demand" and Press OK.

Congratulations !!! you have created your first policy on the SQL Server 2008 Server.

Step 5: Now expand the "Policies" Node in the Management Studio and locate the Policy that you have created just now.

Step 6: Right click on the Policy and select "Test Policy" this will bring up the dialog box. If the CLR Integration is disabled on this Server instance then it will be notified else if the CLR was enabled anytime then it will be notified.

Policy Not Violated

Step 7: Now I will enable the CLR by executing the System Stored Procedure

sp_configure 'clr enabled', 1

Now we will execute "RECONFIGURE" to make the changes effective.

We will now retest the policy and see the result.

Policy Violated

So, I hope the DBAs will find it comfortable authoring policies for effective administration. If you like or dislike the article please post it .. it helps me to bring better articles every time. I will bring more articles on SQL Server 2008 Enhancements so keep a watch.

I use Windows Live Writer to write my blogs... what do you use ?

Hi ! It's been a long time since I last updated my blog. To continue with the Change Data Capture feature of SQL Server 2008, this is new post that helps you to know how you can write your own stored procedures / functions to get the required data in the fashion you want, using the CDC functions provided by Microsoft.

NOTE: If you haven't read the article "SQL Server 2008 - Change Data Capture - Part I" then I would recommend you to please visit the article first else you won't be able to connect well, what I am trying to express here.

The following function works in the similar fashion like cdc.fn_cdc_get_all_changes_InstanceHumanResourcesEmployee but this function will take the starting time and ending time i.e. the time range and the row filter option. The row filter option will either be 'all'  or 'all update old'.

cdc.fn_cdc_get_all_changes_InstanceHumanResourcesEmployee always matches the LSN number provided by you with the LSN present in the CDC table and if it is not found it will throw an error. The reason is it will check for the exact LSN Number so that duplicate records don't creep into the result set. The following function is more generic and will tell you about the changes that happened to a table within a time range. The logic is created in a fashion that if there are no records existing in the table matching the criteria specified by you then it will return an empty set.

This is just an example to demonstrate that how we could write our own SPs and Functions to get the data from the Change Tables.

I hope people are enjoying to work with SQL Server 2008, just in case if you have missed these following links, please visit them today.

1. SQL Server 2008 RC0 Download
2. SQL Server 2008 Feature Pack

Keep writing to me & keep a watch on my blog. I will bring new articles, on new features of SQL Server 2008 very soon. Till then ... Bye !

Hello everyone .. It's been a long time since I haven't blogged. So today being a weekend again I have some time for jotting down this blog. Writing gives me a great pleasure and since last few months I didn't get this pleasure to get in touch with you through my blogs.

Today I am planning to write on Change Data Capture feature of SQL Server 2008. I hope you would like this article as well like the other articles you've been liking so far.

Change Data Capture (referred as CDC going forward) is a cool feature inducted in SQL Server 2008. Have you ever wondered that somehow you could get the stream of data that has been changed since a point in time. People have been looking for this feature for quite some time now. Imagine that you have a Data Mart / Data Warehouse that holds the data that is the replica of the data in OLTP tables. The OLTP Database is changed by the numerous transactions that happen daily, finally you find that the data in Data Mart / Data Warehouse is not in synch with the OLTP Tables. In such scenario the CDC feature of the SQL Server 2008 provide a steady stream of the data that has changed. CDC is designed to capture the inserts / updates / deletes applied to SQL Tables.

How it works

The following diagram explains it all :

Within the OLTP Database you have the tables, whenever you have some Insertions / Deletions / Modifications to the Table, it is written to the Log simultaneously. The Change Data Capture Process picks up the changes to the source tables and stores in the change tables. When we want to load the Data Warehouse we will use the CDC functions and it will give the steady stream of changes happened to those tables and using the ETL (Extract Transform and Load) process we will get those changes in the Data Warehouse.

Preparing a database for using Change Data Capture

NOTE: CDC will be available in SQL Server 2008 Enterprise, Developer and Evaluation editions.

First of all we would check the CDC is enabled on the database. I would use the AdventureWorks Database for code samples.

If CDC is not enabled on the database then we will enable Change Data Capture on the database. When we enable the CDC on the Database the schema named 'cdc' is created and the following five system tables are also automatically created in the schema.

1. cdc.captured_columns
2. cdc.change_tables
3. cdc.ddl_history
4. cdc.index_columns
5. cdc.lsn_time_mapping

Now when the CDC is enabled on the Database we would set up Change Data Capture on the selected tables as per our requirement. I am using the 'HumanResources.Employee' Table in the AdventureWorks DB. The following query will set up the CDC on 'HumanResources.Employee' Table and create a database role by the name of 'cdcAdmin' as this role is not already present in the database. The owner of this role will be the schema 'cdc'. The capture instance name can be selected as per user convenience. I am using the name 'InstanceHumanResourcesEmployee'. When I fire the query, it will automatically create a new table in the 'cdc' schema with the name 'cdc.InstanceHumanResourcesEmployee_CT'. You will also find that a new table valued function with the following name pattern will be created cdc.fn_cdc_get_all_changes_<Capture Instance> . The part of the function name provided in red color depends on the capture instance name we specify. In our case the instance name is "InstanceHumanResourcesEmployee" so the new Table-valued function will be created as cdc.fn_cdc_get_all_changes_InstanceHumanResourcesEmployee

We will fire the following query to find out the details of the CDC just set on the Employee table.

If you would like to find out which tables are being monitored by CDC then run the following query. This will show the HumanResources.Employee table is being monitored by CDC.

The next step is to insert a new sample record in the HumanResources.Employee table and see if that is captured by the CDC.

Now we will use the following query to find out the changes.

Function sys.fn_cdc_map_time_to_lsn

In the above query the sys.fn_cdc_map_time_to_lsn is used. This function is used to get the LSN number that matches a timestamp. The function sys.fn_cdc_map_time_to_lsn takes two parameters.

Parameter 1: Relational Operator

Options for this parameter :

• 'largest less than'
• 'largest less than or equal'
• 'smallest greater than'
• 'smallest greater than or equals'

Parameter 2: Tracking Time

Any standard Date and Time could be specified.

Function cdc.fn_cdc_get_all_changes_<Capture Instance>

In the above query the second function used is cdc.fn_cdc_get_all_changes_InstanceHumanResourcesEmployee. I have already described above how this function is named and how it comes to existence. It takes three parameters.

Parameter 1: From LSN

This is the LSN Value from where we need start getting the changes i.e. low endpoint of the resultset.

Parameter 2: To LSN

This is the LSN Value where we need to stop getting the changes i.e. high endpoint of the resultset.

Parameter 3: Row Option Filter

Options for this parameter :

• 'all' : Returns all the changes within the specified LSN Range. For changes due to an update operation, this option only return the row containing the new values after the update is applied.
• 'all update old' :Returns all changes within the specified LSN Range. For changes due to an update operation, this option returns both row containing the column values before the update and the row containing the column values after update.

Download the SQL Script

Thank you for reading this blog. Friends that are many more options and functions available with Change Data Capture. I will write more about this in the Part II, very soon. So keep reading and keep posting your questions & comments. Bye !

Discover SQL Server 2008 through a variety of solution and technology-focused white papers.

General

• SQL Server 2008 Product Overview

  Microsoft SQL Server provides a policy-based system for managing one or more instances of SQL Server along with tools for performance monitoring, troubleshooting, and tuning that enable administrators to more efficiently manage their databases and SQL Server instances.
  Audience: High-level

• Running SQL Server 2008 on Windows Server 2008

  The combination of SQL Server 2008 and Windows Server 2008 offers enterprise IT administrators and professional developers a superlative platform for mission-critical applications together with enhanced end-to-end security, management, and development capabilities.
  Audience: High-level

Business Intelligence

• Introduction to New Data Warehouse Scalability Features in SQL Server 2008

  With the 2008 release, SQL Server makes a major advance in scalability for data warehousing. It meets the data warehouse needs of the largest enterprises more easily than ever. SQL Server 2008 provides a range of integrated products that enable you to build your data warehouse, and query and analyze its data. These include the SQL Server relational database system, Analysis Services, Integration Services, and Reporting Services. This paper introduces the new performance and manageability features for data warehousing across all these components. All these features contribute to improved scalability.
  Audience: High-level

• SQL Server 2008 Analysis Services Overview

  Microsoft SQL Server 2008 Analysis Services builds on the value delivered with the significant investments in Analysis Services 2005 around scalability, advanced analytics, and Microsoft Office interoperability. This paper covers a high-level look at the key Analysis Services features available in SQL Server 2008.
  Audience: High-level

• Predictive Analysis with SQL Server 2008

  This 10-page paper explores the complete and intuitive set of data mining tools offered in Microsoft SQL Server 2008, which features predictive analysis for all users, insight at every step of the data lifecycle, and data mining awareness in every application.
  Audience: High-level

• Business Intelligence in SQL Server 2008

  This 10-page paper covers the following topics: Unifying Data Storage and Access, Building and Managing Sophisticated BI Solutions, and Extending the Reach of Your BI Solution.
  Audience: High-level

• SQL Server 2008 Reporting Services

  This paper discusses how Microsoft SQL Server 2008 Reporting Services provides a complete server-based platform that is designed to support a wide variety of reporting needs including managed enterprise reporting, ad-hoc reporting, embedded reporting, and web based reporting to enable organizations to deliver relevant information where needed across the entire enterprise.
  Audience: High-level

Online Transaction Processing/Data Management

• Database Encryption in SQL Server 2008 Enterprise Edition

  With the introduction of transparent data encryption (TDE) in SQL Server 2008, users now have the choice between cell-level encryption as in SQL Server 2005, full database-level encryption by using TDE, or the file-level encryption options provided by Windows.
  Audience:  High-level

• SQL Server 2008 Security Overview for Database Administrators

  SQL Server 2008 is secure by design, default, and deployment. Microsoft is committed to communicating information about threats, countermeasures, and security enhancements as necessary to keep your data as secure as possible. This paper covers some of the most important security features in SQL Server 2008. It tells you how, as an administrator, you can install SQL Server securely and keep it that way even as applications and users make use of the data stored within.
  Audience: Mid-level

• Online Transaction Processing in SQL Server 2008

  This 10-page paper covers the following topics: Scale and Performance, High Availability, Security, and Manageability.
  Audience: High-level

• SQL Server 2008 Manageability

  This paper discusses how Microsoft SQL Server 2008 builds on the management capabilities of previous versions and provides a variety of tools and frameworks to help you manage your enterprise data environment.
  Audience: High-level

• SQL Server 2008 Performance and Scale

  This paper discusses how Microsoft SQL Server 2008 incorporates the tools and technologies that are necessary to implement relational databases, reporting systems, and data warehouses of enterprise scale, and provides optimal performance and responsiveness.
  Audience: Mid-level

• An Introduction to SQL Server 2008 Integration Services

  This paper discusses the challenges that face businesses that rely on data integration technologies to provide meaningful, reliable information to maintain a competitive advantage in today’s business world. It discusses how SQL Server 2008 Integration Services (SSIS) can help Information Technology departments meet data integration requirements in their companies. Real-world scenarios are included.
  Audience: High-level

Application Development/Data Programmability

• Microsoft SQL Server 2008 and Microsoft Data Platform Development

  This 10-page paper covers the following topics: Improved Productivity through ADO.NET Entity Framework, LINQ, and Visual Studio; Comprehensive Data Platform; and Scalable Data-Centric Solutions.
  Audience: Technical

• What's New for XML in SQL Server 2008

  This 16-page paper discusses how Microsoft SQL Server 2008 builds on the extensive support for XML by extending support for XML schema validation and XQuery, and by enhancing the xml data type.
  Audience: Technical

• Managing Unstructured Data with SQL Server 2008

  This 11-page paper discusses how Microsoft SQL Server 2008 provides a flexible solution for storing unstructured data and combining it with relational data to build comprehensive solutions that encompass the full range of data across an organization.
  Audience: Technical

• SQL Server 2008: Delivering Location Intelligence with Spatial Data

  This 10-page paper discusses how the new spatial support in SQL Server 2008 can help you make better decisions through visual analysis of location data that can be stored and manipulated in a SQL Server database.
  Audience: Mid-level

Online Transaction Processing/Data Management

• Database Encryption in SQL Server 2008 Enterprise Edition

  With the introduction of transparent data encryption (TDE) in SQL Server 2008, users now have the choice between cell-level encryption as in SQL Server 2005, full database-level encryption by using TDE, or the file-level encryption options provided by Windows.
  Audience:  High-level

• SQL Server 2008 Security Overview for Database Administrators

  SQL Server 2008 is secure by design, default, and deployment. Microsoft is committed to communicating information about threats, countermeasures, and security enhancements as necessary to keep your data as secure as possible. This paper covers some of the most important security features in SQL Server 2008. It tells you how, as an administrator, you can install SQL Server securely and keep it that way even as applications and users make use of the data stored within.
  Audience: Mid-level

• Online Transaction Processing in SQL Server 2008

  This 10-page paper covers the following topics: Scale and Performance, High Availability, Security, and Manageability.
  Audience: High-level

• SQL Server 2008 Manageability

  This paper discusses how Microsoft SQL Server 2008 builds on the management capabilities of previous versions and provides a variety of tools and frameworks to help you manage your enterprise data environment.
  Audience: High-level

• SQL Server 2008 Performance and Scale

  This paper discusses how Microsoft SQL Server 2008 incorporates the tools and technologies that are necessary to implement relational databases, reporting systems, and data warehouses of enterprise scale, and provides optimal performance and responsiveness.
  Audience: Mid-level

Server Consolidation

• Server Consolidation with SQL Server 2008

  This 10-page paper discusses SQL Server 2008 flexibility, manageability, and scalability and performance.
  Audience: High-level

I am loving the enthusiasm that people have for SQL Server 2008. Everyday I come across many questions on SQL Server 2008, this shows that people are curious and I am pretty sure that people are regularly reading and learning SQL Server 2008 to know more about it and how it can help to improve their present working environment. SQL Server 2008 CTP5 is awaited and it would be out for us very soon. There are lots of new features introduced in CTP5 and I think it will keep me busy writing and keep you busy reading. So lets get started and keep ourselves busy for something good. J

Till the date people have been creatively inventing their own mechanisms to store unstructured  data, we are not trying to put the end to the creativity but rather we are proving a new Robust data type that can help people in storing the unstructured data like bitmap images, text files, videos, audio files etc. in a single data type which is secure, more manageable and the among the best of features, the best is that it is very friendly to use. So the expected results are Happy DBAs, Happy Developers and Happy Users.

People have been inventing their own mechanisms to store the unstructured data, I will share some of the creative things I did some years back when I was a developer and I had the access only to old databases and legacy systems like NT 4.0. The application project needed to store such unstructured data securely like Price Quotations, Vendor Data etc. I had to store complete UNC Path in the database and store the file in that UNC Path. So the issues were UNC Path should be accessible to every user, so there was no security for these files and anyone could take a peek at any files stored at the UNC Path. So when I came across this issue I created multiple UNC Paths and only a single user will have the access to this path. This time a new problem jumped in front of me i.e. if the same file had to be shared to a group of 5 users then either I should create a new folder where these 5 users will have the access or or create 5 copies of the same file in separate folders so that the group of 5 people can take a look. The manageability of this system was not at all AGILE but rather FRAGILE when you get some 20-30 such cases per day.

FILESTREAM is going to solve all such problems. Applications can leverage the rich streaming APIs and performance of the FileSystem at the same maintain a transactional consistency between this unstructured data and structured data with security. Today much of the data is created in the unstructured way such as text documents, videos, images, spreadsheets etc. This unstructured data is often stored outside the database, separate from its structured data most of the time in the creative way like described in the above paragraph. This separation can cause data management complexities. Or, if the data is associated with structured storage, the file streaming capabilities and performance can be limited. FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data. FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

Inside FileStream

When to use

Most often the questions arise what should be stored in the FileStream, there are some of the scenarios that are described below. Before discussing the scenarios I would like to emphasize that SQL Server 2008 still supports varbinary(max), varchar(max), XML data types so we need to be judicious while using FileStream.  SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determines whether you should use database storage or file system storage. If the following conditions are true, you should consider using FILESTREAM:

• Objects that are being stored are, on average, larger than 1 MB
• Fast read access is important.
• You are developing applications that use a middle tier for application logic.
• For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

Storage

FILESTREAM storage is implemented as a varbinary(max) column in which the data is stored as BLOBs in the file system. The sizes of the BLOBs are limited only by the volume size of the file system. The standard varbinary(max) limitation of 2-GB file sizes does not apply to BLOBs that are stored in the file system. To specify that a column should store data on the file system, specify the FILESTREAM attribute on a varbinary(max) column. This causes the Database Engine to store all data for that column on the file system, but not in the database file. FILESTREAM data must be stored in FILESTREAM filegroups. A FILESTREAM filegroup is a special filegroup that contains file system directories instead of the files themselves. These file system directories are called data containers. Data containers are the interface between Database Engine storage and file system storage.

When you use FILESTREAM storage, consider the following:

• When a table contains a FILESTREAM column, each row must have a unique row ID.
• FILESTREAM data containers cannot be nested.
• When you are using failover clustering, the FILESTREAM filegroups must be on shared disk resources.
• FILESTREAM filegroups can be on compressed volumes.

Security

In SQL Server, FILESTREAM data is secured just like other data is secured: by granting permissions at the table or column levels. If a user has permission to the FILESTREAM column in a table, the user can open the associated files.Encryption is not supported on FILESTREAM data. Only the account under which the SQL Server service account runs is granted NTFS permissions to the FILESTREAM container. It is recommended that no other account be granted permissions on the data container. When a database is opened, SQL Server restricts access to the FILESTREAM data container(s), except when access is made by using the Transact-SQL transactions and OpenSqlFilestream APIs. However, when the database is closed, the physical data container is fully available and subject to Windows security check. We recommend that you secure directories that contain FILESTREAM data so that the files cannot be accidentally altered or deleted.

Management

FILESTREAM is implemented as a varbinary(max) column and integrated directly into the Database Engine, most SQL Server management tools and functions work without modification for FILESTREAM data. For example, you can use all backup and recovery models with FILESTREAM data, and the FILESTREAM data is backed up with the structured data in the database. If you do not want to back up FILESTREAM data with relational data, you can use a partial backup to exclude FILESTREAM filegroups.

Working with FILESTREAM

Enabling FileStream

Before accessing the FileStream and using it we need to Enable the FileStream. For Enabling the FileStream support sp_filestream_configure system stored procedure must be executed.

The syntax of the sp_filestream_configure Stored Procedure is as follows:

sp_filestream_configure [ [ @enable_level = ] level ]
    [ [ , @share_name = ] 'share_name' ] ;

The parameter @enable_level could be set anywhere between 0 and 3.

@share_name is the parameter that takes a string value which indicates by which name we would like to refer the FileStream for remote access.

It requires membership in sysadmin Fixed Server role and Windows Administrators on local computers.

Code Sample

After this code sample is executed we can switch to command window and see this share by typing NET SHARE on the command prompt.

Let's get some hands on ...

Creating Database

The first step in using FILESTREAM is to have a database that supports it. As the FILESTREAM uses a special type of filegroup, you must specify the CONTAINS FILEGROUP clause for least one filegroup.The following Transact-SQL code example creates a database named FileStreamDB. This database contains three filegroups: PRIMARY, RowGroup1, and FileStreamGroup1. PRIMARY and RowGroup1 are regular filegroups that cannot contain FILESTREAM data. FileStreamGroup1 is the FILESTREAM filegroup.For a FILESTREAM filegroup, FILENAME refers to a path. The path up to the last folder must exist, and the last folder must not exist. In this example, C:\FSDemo must exist, but the VendorCatalog sub-folder cannot exist when you execute the CREATE DATABASE statement.

After you run the sample code, you should see the C:\FSDemo\VendorCatalog folder on your drive. This folder contains a filestream.hdr file and an $FSLOG folder.

The filestream.hdr file is an important system file. Do not open or modify this file.

FileStream in Action

We will create a couple of tables for managing Vendor Details and the details of the Catalog sent by the Vendor. Thereafter we would insert records in the Table. In this example I am just casting a text and putting it to the FileStream. I will include some advanced example in a couple of days time in the same post.

I will update this article very soon with more information and some code samples so keep a watch......