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.

Setting up SQL Server backups with Tivoli TDP agent

INSTALLATION

For SQL Server 2000 => TDPSQL 5.2.1 agent
For SQL Server 2005 => TDPSQL 5.2.1 + patch 3
Patch 3 is required to avoid ACO5423E error

For 2000 file, you will need to extract it manually onto c:\TEMP, then find the appropriate SETUP.exe, run it and follow the instructions. For 2005 file, it will do that automatically.

PDF file in the same directory contains all further details about the installation and configuration.

CONFIGURATION

Ask Tivoli administrator to register TDP for SQL as a client node within the TSM server.
Set up the option and configuration file

TESTING
tdpsqlc query TSM
If this command fails, then connection to the Tivoli Manager is not set up properly.
tdpsqlc query TDP
This command just checks whether your configuration file is set properly.
tdpsqlc query SQL *
The agent will try to connect to the databases.

RUNNING THE BACKUP
TDPSQLC Backup *|dbname[,dbname,...] [Full/Difffull/Log]
/DIFFESTimate=10 -- to estimate the size of changed blocks to 10 percent
/BUFFers=128 -- this will put the size of buffer to 128Kb. The buffer is used to transfer the data between TDP and TSM

We have been running a differential backup on 50G database. It takes two minutes to finish it as most of our data are static. The full backup takes 1 hour.

EXCLUDING THE DATABASE FROM THE BACKUP

To exclude the database from the Tivoli backup, for example you can add this command to your option file:
EXCLUDE.BACKUP "\...\DBEMPL\...\*"

If you want to exclude just for certain backups, create a separate option file, for example opt_small_databases_bakup.opt
and then run your backup command:
"C:\Program Files\Tivoli\TSM\TDPSql\tdpsqlc" backup * full /TSMOPTFile="C:\Program Files\Tivoli\TSM\TDPSql\dsm_small_databases_bakup.opt"

This will backup up all database except for DBEMPL.

SCHEDULING THE BACKUP
It can be done either via TSM or as a SQL Server job

RESTORE
TDPSQLC Restore *|dbname[,dbname,...] [Full/Difffull]|[FIle/Log=*|logicalname[,logicalname,...]] [parameters]
The relevant parameters are
For the restore:
/DBOonly - prevents general users from accessing a restored database.
/STOPAT - restores a SQL database to a specified point in time.

DOCUMENTATION
http://publib.boulder.ibm.com/infocenter/tivihelp/v1r1/index.jsp?topic=/com.ibm.itsmfd.doc/ab5m000171.htm

Various useful small SQLs

Find the edition

select serverproperty('Edition')

Who are you connected as?

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

Show current time

select getdate()

Show current server

select @@servername

Show current version

select @@version

or even better:

xp_msver

Google the results to see what numbers mean

Find out current service pack

SELECT SERVERPROPERTY('ProductLevel')

Sizing

EXEC sp_msforeachdb @command1="use [?] exec sp_spaceused"

EXEC sp_spaceused

EXEC sp_spaceused table_name

List Backups in the last 30 days

SELECT s.database_name, S.backup_start_date, S.type,
M.physical_device_name
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE DATEDIFF(DAY,S.backup_start_date,GETDATE()) <>

Auto close and auto shrink

SET NOCOUNT ON

SELECT [name] AS DatabaseName^?
, CONVERT(varchar(100),DATABASEPROPERTYEX([Name] , 'Recovery')) AS RecoveryType^?
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoClose')) AS AutoClose^?
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoShrink')) AS AutoShrink^?
FROM master.dbo.sysdatabases Order By DatabaseName^?

To create the shink log commands for ALL databases in FULL recovery mode:

SELECT ' use '+name +
char(10) + char(13)+ 'go ' + char(10) + char(13)+
'dbcc shrinkfile ('+name+',0,truncateonly)'+
char(10) + char(13)+ 'go ' + char(10) + char(13)+
FROM master.dbo.sysdatabases
where CONVERT(varchar(100),DATABASEPROPERTYEX([Name],'Recovery'))='FULL'

Check the last statistics update

SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(o.id,i.id)
FROM sysobjects AS o WITH (NOLOCK)
JOIN sysindexes AS i WITH (NOLOCK)
ON object_id(o.name) = i.id
ORDER BY STATS_DATE(o.id, i.id) desc

Check the instance parameters

select * from sys.configurations order by name

List database objects

SELECT * FROM sysobjects WHERE TYPE = 'P'

SELECT name, type FROM sysobjects WHERE id={number}

List all Tables

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

FROM INFORMATION_SCHEMA.TABLES

ORDERBY TABLE_SCHEMA, TABLE_NAME

Execute SQL command for each table

EXEC sp_MSForEachTable 'ALTER TABLE ? ETC ETC'

To release the deallocated space

DBCC UPDATEUSAGE ('dbname')

Fix orphaned users

exec sp_change_users_login 'Report'

CREATE LOGIN [abclogin] WITH PASSWORD=N'passw0rd', DEFAULT_DATABASE=[DBname], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE DBName

GO

sp_change_users_login 'update_one', 'youruser', 'abclogin

Log Shipping

select * from log_shipping_monitor_primary

select * from log_shipping_monitor_secondary

select * from log_shipping_monitor_history_detail