Restore Service Master Key

As a part of a DR test today, I recovered the encrypted database (log shipped) and restored the service master key from the production. When restoring the key, the following error showed up:

Msg 15320, Level 16, State 12, Line 1
An error occurred while decrypting master key 'DbName' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.

Fix
Use the FORCE option. Although the documentation says that you might lose some data, as long as you have the correct service master key from the production, that will not happen. Quite the contrary, the correct master key will decrypt the database.

Tags: SQL Server error 15320, service master key, restore

SQL Cluster: Resource dependencies

When installing a new clustered instance, you will probably want to assign multiple disk drives to a SQL instance. After you define all clustered resources and install instance, instance will only see the disk drive where you put your default data files. For any additional disk drives, you have to manually apply the solution below.

Solution:
In a SQL Cluster Administration, open the "SQL Server" resource, click on "Dependencies" and add the disk resource you want to be able to use.

Key words:
"SQL cluster" "Select the folder:" "can't see disk"

Linked server for 64-bit SQL 2005 SP3

I was supposed to set up the linked server from SQL Server to Oracle 10g database. Initially, I tried to use the Microsoft provider. After registering the 32-bit MDAC for Oracle and setting up the linked server, I got an error:

• C:\Program Files (x86)\Common Files\System\Ole DB>regsvr32 msdaora.dll
• EXEC sp_addlinkedserver 'CFS', 'Oracle', 'MSDAORA', 'oracle10g'
• EXEC sp_addlinkedsrvlogin 'CFS', 'FALSE',NULL, 'scott', 'tiger'
• SELECT * FROM CFS..SCOTT.SOME_VIEW
• Msg 7308, Level 16, State 1, Line 1
• OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

This was the 32-bit provider and apparently this is a known bug.

I switched to Oracle OLEDB Provider and it worked without issues:

• USE [master]
• GO
• EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
• GO
• EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'DynamicParameters', 1
• GO
• EXEC master.dbo.sp_addlinkedserver @server = N'CFS', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'CFS'
• EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CFS',@useself=N'False',@locallogin=NULL,@rmtuser=N'scott',@rmtpassword='tiger'

The other solution would be to install 64-bit MDAC on the Windows server. MDAC has been complied to both 32-bit and 64-bit files since the version 2.7, but it is limited to only few platforms and we couldn't find 64-bit MDAC for Windows 2003.

The only drawback with using the Oracle provider is that for any errors, we have to go to Oracle to get a support.

SQL Server Virtualization Summary

Support

• SQL Server is is supported on Microsoft Virtual Server, including the support for the clustered environment.
• With a Premier Support SQL Server is also supported for Novel virtual software.
• In 2007: VMWare is tested and supported by VMware, but not by Microsoft which is not a surprise considering it is in direct competition with their Virtual Server. If the customer has the premier support, Microsoft say it "will use commercially reasonable efforts to investigate potential issues".
• March 2009 update: Microsoft started supporting SQL on some of the VM, including VMware. This excludes quest clustering and unsupported SQL/VM's OS combinations.
  

Licensing

Licensing for the SQL Server on virtual environment is done depending on the edition:

• For Enterprise Edition, if all processors in a machine have been licensed, then the customer may run unlimited instances of SQL server 2005 on an unlimited number of virtual operating environments on that same machine. Otherwise, the same as below
• For the other edition, it goes per each virtual environment and, within it, for each processor that the virtual machine accesses.

VMWare

Vmware has interesting features like: automatic failover to another VMware-enabled server, movingSQL Server instances from one host to another LIVE (zero downtime), quick disaster recovery.

Virtual Server

VS 2005 EE is a free download since April 2006. It supports up to 3.6G of RAM per each environment.

Virtual Server provides simple two-node failover from one virtual machine to another for testing and development

SQL 2005 EE has an add-on that can run Virtual Hard Disk for up to 10G. VHD is a disk for each virtual machine and it is stored in a .vhd file.

SQL Server Auditing - Example

Set up objects

select * from master.dbo.ddl_all_databases

create table master.dbo.ddl_all_databases
( DateTime^? datetime,
LoginName^? char(20),
DBName char(20),
UserName^? char(20),
EventType^? char(200),
CommandText^? char(1000))

CREATE TRIGGER Trig_DBEvents ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS INSERT master.dbo.ddl_all_databases
(
DateTime^?,
LoginName^?,
DBName,
UserName^?,
EventType^?,
CommandText^?
)
SELECT GETDATE(),
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(50)'),
EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(50)'),
EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(50)'),
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)'),
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(1024)')
GO

Monitored events

This will monitor:

• DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_SYNONYM_EVENTS, DDL_SSB_EVENTS, DDL_DATABASE_SECURITY_EVENTS, DDL_EVENT_NOTIFICATION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_TYPE_EVENTS, DDL_XML_SCHEMA_COLLECTION_EVENTS, DDL_PARTITION_EVENTS, DDL_ASSEMBLY_EVENTS

Preferably you can start monitoring with only:

• DDL_USER_EVENTS,DDL_INDEX_EVENTS,DDL_TABLE_EVENTS

Trigger on the SERVER level can be set up and monitor DDL_SERVER_LEVEL_EVENTS instead (ALTER DB, adding, dropping roles,etc...)

Tuning SQL Statements

Top 10 worst SQL statements (measured by the time)

select top 10
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS sql_text,
total_worker_time/execution_count "CPU per exec",
total_elapsed_time/execution_count/1000000 "Time per exec",
total_physical_reads/execution_count "Ph.Reads per exec",
total_logical_reads/execution_count "Log.Reads per exec",
total_logical_writes/execution_count "Log.Writes per exec"
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where qs.total_elapsed_time/execution_count/1000000>10
order by "Time per exec" desc

Top 10 worst SQL statements with the explain plan

select top 10
qs.sql_handle,
total_worker_time/execution_count "CPU per exec",
total_elapsed_time/execution_count "Time per exec",
total_physical_reads/execution_count "Ph.Reads per exec",
total_logical_reads/execution_count "Log.Reads per exec",
total_logical_writes/execution_count "Log.Writes per exec",
qp.query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp

Indexes that are possibly not used

select * from sys.dm_db_index_usage_stats
where user_seeks=0 and user_scans=0 and user_lookups=0 and user_updates=0

Moving SQL Server database to another domain

Our SQL Server database was installed on a domain controller and later moved to a standard server in another domain. When trying to add SP2 to our SQL Server installation, I got errors like this:
Setup was unable add user DOMAIN\SVCUSER to local group DOMAIN\SQLServer2005SQLAgentUser$SVR$INSTANCE01
MSP Error: 29528
Transforming table Error

Fix:
Create local SQL Server groups for services and assign the users as you need them.
Remove "group" keys in the registry. You will know which ones as they all have word "group" in the name.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

Then install SP2.
Keys will get re-created.