Katmai

Katmai - SQL Server 2008 has been launched. I was playing around with the new features in Katmai and some of them really struck me as ground breaking changes compared to earlier versions. My speciality being the Database Engine, I have not been able to do justice to my poking around for other components like the BI part of it. Though whatever I have managed to poke around, I found the following enhancements and new features to be very impactful in the near future:

Compression - Database and Backup compression is now available in Katmai. This is a boon for DBAs who manage databases which are TBs in size. Now you have Row or Page level compression to choose from. Also, you have a SP which makes an estimate of how much benefit you will derive from enabling compression or will it benefit you at all.

Backup Compression is also another major boon as storing backups of VLDBs can be quite a task which have strict SLA requirements.

T-SQL Enhancements - I quite liked this one. Now I can use programming level constructs while coding in T-SQL. Eg. declare @var varchar(50) = 'Declare and Initialize!'

New Table Hints and Query Hints - A few other tools added to the repertoire of the DBA to make queries run faster in environments where the DBA has no control over the queries being sent to the the database engine via the application.

XEvents - This is an important new feature from a troubleshooting perspective.

Policy Based Management - This is another of those administration joys that a DBA will derive when using this new feature. With a few clicks, the DBA can administer multiple database environments to conform organizational policies and also have checks in place to report non-compliance.

Server Audit Tracing - This is a step towards helping in meeting Compliance standards like PCI etc. No longer will you need to run profiler traces which have a tendency to affect server performance if capturing a large number of events.

Resource Governor - Another new feature which provides more granularity in deciding on resource bifurcation for different applications and deciding on application priorities. This virtually allows the DBAs to decided on what kind of queries should get how much resources on the server w.r.t. CPU, Memory etc. However, one should understand the full impact and ramifications of enabling such resource throttling on the server before implementing it. Such an implementation of RG at a production level requires thorough testing as you might end up throttling an operation which you didn't intend to.

Read more about "SQL Server 2008" here.

How to connect to Embedded Edition from SSMS

From SQL Server Management Studio, you cannot connect to a SQL Server Embedded Edition instance by just providing the server name. You need to put the following in the server name input:
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

The files for an embedded edition instance should be at:

C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\

Using WMI to manage SQL Server 2000 services

There are quite a few blog posts out there to manage SQL Server 2005 services using the :\\.\root\Microsoft\SqlServer\ComputerManagement

However, WMI Admin Provider is not pre-installed for SQL Server 2000. It needs to be installed separately using the WMI Admin Provider Setup available along with the SQL Server 2000 Setup CD under x86\other\wmi folder.

Sample script to change SQL Server 2005 service startup account and password using WMI:
http://blogs.msdn.com/mwories/archive/2006/11/03/wmi_5F00_change_5F00_password.aspx

MSDN Documentation on Win32_Service class
http://msdn.microsoft.com/en-us/library/aa394418.aspx

Sample Script to change a SQL Server 2000 instance startup account using root\MicrosoftSQLServer namespace:

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\MicrosoftSQLServer")

' Obtain an instance of the the class
' using a key property value.

Set objShare = objWMIService.Get("Win32_Service.Name='MSSQL$SQL2000'")

' Obtain an InParameters object specific
' to the method.

Set objInParam = objShare.Methods_("Change"). _ inParameters.SpawnInstance_()

' Add the input parameters.
objInParam.Properties_.Item("StartName") = "LocalSystem"
objInParam.Properties_.Item("StartPassword") = ""

' Execute the method and obtain the return status.
' The OutParameters object in objOutParams
' is created by the provider.

Set objOutParams = objWMIService.ExecMethod("Win32_Service.Name='MSSQL$SQL2000'", "Change", objInParam)

' List OutParams
Wscript.Echo "Out Parameters: "Wscript.echo "ReturnValue: " & objOutParams.ReturnValue

RDTSC

I have seen a few cases where administrators have been concerned with CPU Drift and think that the SQL Server ERRORLOG reporting the following message is a serious cause for concern:

Error message 1
The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
Error message 2
CPU time stamp frequency has changed from 191469 to 1794177 ticks per millisecond. The new frequency will be used

The SQL Server ERRORLOG reports a variety of informational, error and warning messages and not all messages are problems. This message is just telling you that CPU frequency between one or more processors is not synchronized. And how does this affect you??

Quoting from one of the below articles:

"Generally the Microsoft SQL Server support team considers drift less than several seconds, noise."

If you are concerned that the drift values are actually affecting your test results, then it would be a good idea to have the Speed Step, Power Now etc. features turned off during your testing phase. This would require changes at the BIOS level. Also, it would be a good idea to have consulted your H/W manufacturer and find out if there are any updates that require to be installed. Once again, I reiterate unless the drift values are constantly reporting several seconds for prolonged periods, only then do we have a Beginning of a problem, otherwise these warnings are mostly noise.

Additionally, trace flag (–T8033) can be used to suppress the drift warnings. However, please do not enable this trace flag on an instance of SQL Server 2005 unless and until, you fully understand the ramifications of ignoring the drift warnings.

Related Links
SQL Server timing values may be incorrect when you use utilities or technologies that change CPU frequencies
http://support.microsoft.com/kb/931279/en-us
SQL Server 2005 SP2 will introduce new messages to the error log related to timing activities
http://blogs.msdn.com/psssql/archive/2006/11/27/sql-server-2005-sp2-will-introduce-new-messages-to-the-error-log-related-to-timing-activities.aspx
SQL Server 2005 - RDTSC Truths and Myths Discussed
http://blogs.msdn.com/psssql/archive/2007/08/19/sql-server-2005-rdtsc-truths-and-myths-discussed.aspx

Allow Updates Option

SQL Server 2005 doesn't have the allow updates option. So, if you execute:

sp_configure 'allow_updates',1

and then if you execute reconfigure, you would get the following error:

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

After this all changes to the sp_configure settings followed by a reconfigure would yield this error. To rectify this, you will have to change the allow_updates option back to 0 and run reconfigure. As per SQL Server 2005 Books Online:

This option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported.

Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts.

So, if in case you use allow_updates in any script in SQL Server 2005, please refrain from doing so. Updates to the system catalogs are not permitted in SQL Server 2005 and any attempt/changes made to the System Resource database would get you into an unsupported scenario.

WMI Tracing

A lot of companies find the need to monitor all DDL and DML activity on the server for Compliance reasons. Though SQL Profiler Traces provides this functionality by using Stored Procedures and Functions. These system stored procedures can be used from within your own applications to create traces manually, instead of using SQL Server Profiler. This allows you to write custom applications specific to the needs of your enterprise. However, you might choose not to use this approach due to the overhead of running profiler traces on the server. You can use server side profiler traces which tend to have a lesser performance impact on the server.

Another method is to use WMI Events to monitor SQL Server Events. Consider the following example, let's say you want to monitor the SQL Server events for all Update Statistics Events. Then you can use script below to create the WMI Alert and also the job to put the event details into a SQL Server database table.

<script>

USE TestDB
GO

-- Creating the table to store the DDL Events

CREATE TABLE [dbo].[UPD_STATS_Events](
[AlertTime] [datetime] NULL,
[SPID] [int] NULL,
[DBName] [nvarchar](100) NULL,
[TextData] [nvarchar](max) NULL
) ON [PRIMARY]

GO

-- Adding the job to run when the WMI Alert is raised

EXEC msdb.dbo.sp_add_job @job_name=N'Capture Update Statistics Events',
@enabled=1,
@description=N'Job for responding to DDL events' ;
GO

-- Adding the job step

EXEC msdb.dbo.sp_add_jobstep
@job_name = N'Capture Update Statistics Events',
@step_name=N'Insert data into Update Statistics Events table',
@step_id=1,
@on_success_action=1,
@on_fail_action=2,
@subsystem=N'TSQL',
@command= N'INSERT INTO UPD_STATS_Events
(AlertTime, Spid,DBName,TextData)
VALUES (getdate(), $(ESCAPE_NONE(WMI(SPID))),
''$(ESCAPE_SQUOTE(WMI(DatabaseName)))'',
''$(ESCAPE_SQUOTE(WMI(TSQLCommand)))'')',
@database_name=N'TestDB' ;
GO

-- Set the job server for the job to the current instance of SQL Server.
EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture DDL Events' ;
GO
-- Add an alert that responds to all DBCC events for
-- the default instance. To monitor deadlocks for a different instance,
-- Change MSSQLSERVER to the name of the instance.
-- For the named instance you need to use \\.\root\Microsoft\SqlServer\ServerEvents\<INSTANCE NAME>

EXEC msdb.dbo.sp_add_alert @name=N'Respond to DDL Events',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM UPDATE_STATISTICS',
@job_name='Capture DBCC Events' ;
GO

</script>

Whenever an update statistics task is fired against a database, the time when the command was executed, the SPID number, the database name and the T-SQL Command command associated with the event would be put into the table in the TestDB database.

Service Broker should be enabled for the MSDB and the database in which you are storing the event details.

Information about the namespaces can be found on MSDN. Also, you could use WMI Code Creator to browse through the namespace and the classes available and their properties.

Furthermore, you can also use the WMI Event Watcher Task or the WMI Data Reader Task of SQL Server 2005 SSIS to perform the same.

Connect: SQL Server

I have spoken to a lot of people who are not really aware of the right medium of submitting product feedback for SQL Server or reporting defects online. The CONNECT site for MS is the way to go here. For reporting SQL Server related issues, please use the link http://connect.microsoft.com/sqlserver

Product Wishes can also be filed here. If you do submit product defect that can be fixed then the Dev team will definitely fix it. Fixing a part of the code requires lot of strategic thinking as well as testing. One of the biggest reasons why some fixes are rejected is because of the fact this current change in the code would impact a lot of other parts of the product which is working seamlessly fine or has the possibility of impacting a fully functional error free component in an adverse manner.

So, in future if you do need to submit product feedback, please do so at Connect: SQL Server

We always welcome feedback regarding our Products and want our user community to tell us how to improve the same and what features they think they would be benefited with the most.

Federated Servers

To achieve the high levels of performance required by the largest Web sites, a multitier system typically balances the processing load for each tier across multiple servers. SQL Server 2005 shares the database processing load across a group of servers by horizontally partitioning the data in a SQL Server database. These servers are managed independently, but cooperate to process the database requests from the applications; such a cooperative group of servers is called a federation.

A federated database tier can achieve very high levels of performance only if the application sends each SQL statement to the member server that has most of the data required by the statement. This is referred to as collocating the SQL statement with the data required by the statement. Collocating SQL statements with the required data is not a requirement unique to federated servers. It is also required in clustered systems.

Federated Server Tier

There is one instance of SQL Server on each member server.

Each member server has a member database. The data is spread through the member databases.

The tables from the original database are horizontally partitioned into member tables. There is one member table per member database, and distributed partitioned views are used to make it appear as if there was a full copy of the original table on each member server.

The application layer must be able to collocate SQL statements on the member server that contains most of the data referenced by the statement.

Backing Up and Restoring Federated Database Servers

In a federated-database-server tier that is built by using distributed partitioned views, the member servers form one logical unit. Therefore, you must coordinate the recovery of the member databases to make sure that they remain synchronized correctly.

SQL Server 2005 does not require that you coordinate backups across member servers. Backups can be independently taken from each database, without regard for the state of the other member databases. Because the backups do not have to be synchronized, there is no processing overhead for synchronization and no blockage of running tasks.

The most important aspect of recovering a set of member databases is the same as recovering any other database: Plan and test the recovery procedures before you put the databases into production. You must set up processes to restore all the databases to the same logical point in time. SQL Server includes features to support the recovery of all member databases to the same point in time.

Pros

1. Federated servers if implemented correctly are a great way to load balance a database server environment and is very similar to a database farm implementation.

2. This would greatly allow you to distribute the load on your servers based on any of the following criteria:

a. Geographic location

b. Traffic in terms of users

c. Traffic in terms of transactions

d. Database table size

3. Also, federated servers give you the option on partitioning data across servers with the help of distributed partitioned views

4. This gives you the option of horizontally partitioning the data across various servers which ultimately leads to greater throughput

5. It lets you control the traffic coming in and also helps in maintaining the load thresholds across the entire setup

6. Furthermore, if we have a middle tier in the entire setup, then design changes in the federated server environment will not affect the client side applications in any manner as they would be connecting to the middle tier and the middle tier will connect with the database server

Cons

1. One of the major drawbacks is disaster recovery. If one of the member server fails, there needs to a failback plan in place which could cause minimum hindrance to the normal operations while implementation.

2. Also, if distributed partitioning is being implemented, then rules and constraints need to be strong enough to prevent any sort on inconsistencies from arising due to data modification

3. Also, if the performance of one of the member servers takes a hit, most of the distributed partitioned views would also take a hit in terms of operations done on them

4. The middle tier should be designed in such a robust manner that there is no ambiguity in resolving which server in the environment needs to targeted based on the query coming into the server

5. The backup/restore scenarios need to be designed and planned in such a way that all the member servers are all synchronized at all times

Useful Articles

The following whitepaper and TechNet articles should help a great deal here:

Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005Solution

http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/ImplementingScaleOut.doc

Scaling Out SQL Server with Data Dependent Routing

http://www.microsoft.com/technet/prodtechnol/sql/2005/scddrtng.mspx

Designing Data Tier Components and Passing Data Through Tiers http://msdn2.microsoft.com/en-us/library/ms978496.aspx