SQL 2008 : High Availability Whitepaper

Just out, a whitepaper on achieving High Availability in SQL 2008

http://msdn.microsoft.com/en-us/library/ee523927.aspx


Looking at the feature table, i'm glad I'm running Enterprise edition!

TSQL : Comma Separated List of Columns

Like the title says, how to generate a comma separated list of columns for a given table...

DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName  VARCHAR(100)
DECLARE @CommaSeparatedColumnList VARCHAR(MAX)

SET @SchemaName = 'myschema'
SET @TableName  = 'mytable'
SET @CommaSeparatedColumnList = ''

SELECT @CommaSeparatedColumnList = COALESCE(@CommaSeparatedColumnList + '[' + COLUMN_NAME + '],','')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName
AND TABLE_NAME = @TableName          
ORDER BY ORDINAL_POSITION
SET @CommaSeparatedColumnList = LEFT(@CommaSeparatedColumnList,LEN(@CommaSeparatedColumnList)-1)

SELECT @CommaSeparatedColumnList

2 ways to audit all logins

1) Via Server Properties -


2) via a TSQL script -

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3
GO

MCITP : Database Administrator 2008

Following passing my MCTS on Monday , I passed MCITP Database Administrator 2008 this morning.

Contemplating the development track (70-433 & 70-451) next month to bring me up to date.

r

Bookmark : Management

A colleague found this today. An excellent post nicely summing up managing technical teams...

Opinion : The unspoken truth about managing geeks

MCTS : Database Administration 2008

Just a quick note to say I passed 70-432 : Microsoft SQL Server 2008, Implementation and Maintenance yesterday.

I used the MCTS Self-Paced Training Kit (Exam 70-432) if you want to do the same.

r

Stored Procedures : Execute as owner

Using EXECUTE AS OWNER in a stored procedure definition allows you to raise permissions for the execution of the procedure.

This enables a application login with low privileges to perform owner (dbo) privileged functionality using just the execute permissions on the sproc.

CREATE PROCEDURE dbo.EmptyMyTable

WITH EXECUTE AS OWNER

AS

BEGIN

TRUNCATE dbo.TableA

END

Clay Lenhart : SQL Server Security with EXECUTE AS OWNER

SSIS : Using Stored Procedures with an OLEDB Connection in SSIS

Using Stored Procedures with an OLEDB Connection in SSIS

Large recordsets cause visual studio to freeze for long periods of time, giving the impression it has hung.
(This happens repeatedly when navigating data connections).

The Solution is to pass metadata up front to SSIS, hence avoiding LONG delays whilst SSIS validates your recordset.

There is a widely publicised work-around of using 'SET FMTONLY ON' to pass metadata only (passing 'SET FMTONLY ON; EXEC dbo.proc1') but I had limited success with this.


Basically, declare an empty record set of the correct type at the start of the procedure >

CREATE PROCEDURE dbo.usp_SSIS_Data_Fetch (@eventid BIGINT, @rows BIGINT) AS

BEGIN
SET NOCOUNT ON



-- Dummy Records to pass datatypes back to SSIS for validation without returning entire recordset.

-- (note, this expression always evaluates to false so is never used except to declare a result set of

-- the correct data types at the start of the procedure.



IF 1 = 0

BEGIN

SELECT
CAST(NULL AS BIGINT) AS [ID]
,CAST(NULL AS VARCHAR(255)) AS [TrackingUrl]
,CAST(NULL AS VARCHAR(255)) AS [SearchTerm]
END

-- Genuine Result set

SELECT ID, TrackingUrl, SearchTerm FROM dbo.TrackingTable

END
GO

In addition to this you have a ValidateExternalMetadata property on an OLEDB Source inside a data flow. You can set this to false if you are confident the output of your Stored Procedure is not going to change!


Links :
http://munishbansal.wordpress.com/2009/02/18/set-fmtonly-on-useful-in-tsql-ssis-packages-for-using-temp-tables/
http://sqlserverpedia.com/blog/sql-server-bloggers/ssis-stored-procedure-metadata/

Multiple CTEs in one statement

Putting here as this had me stumped...
How to use CTEs (Common Table Expressions) together -

WITH
  cte1 as (SELECT * FROM schema.table1),
  cte2 as (SELECT * FROM schema.table2)

SELECT * FROM cte1 UNION SELECT * FROM cte2

SQL Server blocked access to procedure 'sys.sp_OACreate'

Executed as user: Domain\SQLServiceAgent. SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.

The message tells us exactly what to do, use sp_configure -

sp_configure 'show advanced options', 1
GO 
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1
GO 
RECONFIGURE;
GO 
sp_configure 'show advanced options', 1
GO 
RECONFIGURE;

Career / Quote

A brilliant post from Brent Ozar, and an excellent quote...

" Being a good manager boils down to one simple thing: motivating people you dislike to do things they dislike. "

http://www.brentozar.com/archive/2009/09/managing-people-sucks-but-you-should-try-it-anyway/

SQL 2008 : Using MultiServer Queries

In SQL 2008 you can run a query against multiple servers at the same time by opening a query window against a server group rather than an individual server.

To demonstrate, a query that fetches data about a server -

SELECT
  TOP 1 BACKUP_START_DATE AS LAST_BACKUP
, @@VERSION AS SQL_VERSION
, CREATE_DATE AS LAST_STARTUP
, DATEDIFF(D,CREATE_DATE,GETDATE()) AS DaysUptime
FROM MSDB.DBO.BACKUPSET (NOLOCK)
CROSS JOIN SYS.DATABASES (NOLOCK)
WHERE SYS.DATABASES.NAME = 'TEMPDB'
ORDER BY BACKUP_SET_ID DESC

By executing against the group we get it executed against each server, hence a row for each >

News / Humour / Irony !

A colleague sent me this. A pigeon in South Africa is 'faster than broadband'

http://news.bbc.co.uk/1/hi/world/africa/8248056.stm

SQL 2008 : SET ROWCOUNT Deprecated

Spotted this in Books Online today...

'Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax. '

So any nibble delete or update functions using SET ROWCOUNT are going to have to be looked at.

Think thats plenty enough warning though....

Server 'dev-02' is not configured for RPC.

Msg 7411, Level 16, State 1, Procedure usp_procname, Line 23
Server 'dev-02' is not configured for RPC.

These linked server options allow you to execute a stored procedure against a remote data source.

exec sp_serveroption @server='dev-02', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='dev-02', @optname='rpc out', @optvalue='true'

Immense do-it-yourself storage solution

Was sent this by a colleague. clever stuff...

http://blog.backblaze.com/2009/09/01/petabytes-on-a-budget-how-to-build-cheap-cloud-storage/

TSQL : Who is connected and how !

Adapted from a newsgroup posting.
Gives authentication and Windows login information too -

select s.session_id,
s.host_name,
s.program_name,
s.client_interface_name,
s.login_name,
s.nt_domain,
s.nt_user_name,
c.auth_scheme,
c.client_net_address,
c.local_net_address,
--c.connection_id,
--c.parent_connection_id,
--c.most_recent_sql_handle,
(select text from master.sys.dm_exec_sql_text(c.most_recent_sql_handle )) as sqlscript,
(select db_name(dbid) from master.sys.dm_exec_sql_text(c.most_recent_sql_handle )) as databasename,
(select object_id(objectid) from master.sys.dm_exec_sql_text(c.most_recent_sql_handle )) as objectname
from sys.dm_exec_sessions s inner join sys.dm_exec_connections c
on c.session_id=s.session_id
--where login_name='XXXXX'

Subnet Calculator tool

A useful time saver...

http://www.t1shopper.com/tools/calculate/ip-subnet/

SQL 2008 : Uncompressed Objects Procedure

SQL Server Central have published my latest procedure as 'script of the day'.

The procedure is based arond SQL 2008 compression functionality and provides >

1. Lists of tables & indexes without compression
2. Lists of tables & indexes not using the desired compression (e.g. ROW when you've specified a compression type of PAGE)
3. TSQL commands to compress the database objects.

SSC : SQL 2008 : Uncompressed Objects Procedure


March 2011 Update : Putting the function here too now as SSC exclusivity period over...

CREATE PROCEDURE dbo.UncompressedObjects (@database VARCHAR(50) = '' ,@emailrecipients VARCHAR(1000) = '' ,@emailprofile VARCHAR(50) = '' ,@compressiontype VARCHAR(4) = 'PAGE')
AS
BEGIN

/*
Procedure : dbo.UncompressedObjects
Version   : 1.0 (August 2009)
Author    : Richard Doering
Web       : http://sqlsolace.blogspot.com
*/

SET NOCOUNT ON

-- Check supplied parameters
IF @database = '' 
 BEGIN 
  PRINT 'Database not specified'
  RETURN 
 END
IF @database NOT IN (SELECT name FROM sys.databases) 
 BEGIN 
  PRINT 'Database ' + @database + ' not found on server ' + @@SERVERNAME
  RETURN 
 END
IF @emailrecipients = '' AND @emailprofile <> '' 
 BEGIN 
  PRINT 'Email profile given but recipients not specified'
  RETURN 
 END
IF @emailrecipients <> '' AND @emailprofile = '' 
 BEGIN 
  PRINT 'Email recipients given but profile not specified'
  RETURN 
 END
SET @compressiontype = UPPER(LTRIM(RTRIM(@compressiontype)))
IF @compressiontype NOT IN ('PAGE', 'ROW')
 BEGIN 
  PRINT 'CompressionType must be PAGE or ROW'
  RETURN 
 END
 
-- Declare variables
DECLARE @indexreport VARCHAR(MAX)
DECLARE @missingindexcompressiontsql VARCHAR(MAX)
DECLARE @missingindextablelist VARCHAR(MAX)
DECLARE @missingindexindexlist VARCHAR(MAX)
DECLARE @missingcompressiontablecount INT
DECLARE @missingcompressionindexcount INT
DECLARE @changeindexcompressiontsql VARCHAR(MAX)
DECLARE @changeindextablelist VARCHAR(MAX)
DECLARE @changeindexindexlist VARCHAR(MAX)
DECLARE @changecompressiontablecount INT
DECLARE @changecompressionindexcount INT
DECLARE @CurrentRow INT
DECLARE @TotalRows INT
DECLARE @Objecttype VARCHAR(10)
DECLARE @objectname VARCHAR(100)
DECLARE @command VARCHAR(1000)
DECLARE @emailsubject VARCHAR(100)
DECLARE @dynamicsql VARCHAR(MAX)               

-- Create temporary tables.
-- These are used because they're scope is greater than a tablevariable i.e. we can pull results back from dynamic sql.
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '##MissingCompression%')
   DROP TABLE ##MissingCompression
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '##ChangeCompression%')
   DROP TABLE ##ChangeCompression      
CREATE TABLE ##MissingCompression
     (uniquerowid INT IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL,
                   objecttype VARCHAR(10),
                   objectname VARCHAR(100),
                   command VARCHAR(500));
CREATE TABLE ##ChangeCompression
     (uniquerowid INT IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL,
                   objecttype VARCHAR(10),
                   objectname VARCHAR(100),
                   command VARCHAR(500));
                   
-- Work out what indexes are missing compression and build the commands for them
SET @dynamicsql =
'WITH missingcompression
     AS (SELECT ''Table''  AS objecttype,
                s.name + ''.'' + o.name AS objectname,
                ''ALTER TABLE ['' + s.name + ''].['' + o.name + ''] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ' + @compressiontype + ');'' AS command
         FROM  ' + @database + '.sys.objects o
                INNER JOIN  ' + @database + '.sys.partitions p
                  ON p.object_id = o.object_id
                INNER JOIN  ' + @database + '.sys.schemas s
                  ON s.schema_id = o.schema_id
         WHERE  TYPE = ''u''
                AND data_compression = 0
                AND Schema_name(o.schema_id) <> ''SYS''
         UNION
         SELECT ''Index'' AS objecttype,
                i.name AS objectname,
                ''ALTER INDEX ['' + i.name + ''] ON ['' + s.name + ''].['' + o.name + ''] REBUILD WITH ( DATA_COMPRESSION = ' + @compressiontype + ');'' AS command
         FROM   ' + @database + '.sys.dm_db_partition_stats ps
                INNER JOIN ' + @database + '.sys.indexes i
                  ON ps.[object_id] = i.[object_id]
                     AND ps.index_id = i.index_id
                     AND i.type_desc <> ''HEAP''
                INNER JOIN ' + @database + '.sys.objects o
                  ON o.[object_id] = ps.[object_id]
                INNER JOIN ' + @database + '.sys.schemas s
                  ON o.[schema_id] = s.[schema_id]
                     AND s.name <> ''SYS''
                INNER JOIN ' + @database + '.sys.partitions p
                  ON p.[object_id] = o.[object_id]
                     AND data_compression = 0)
                     
-- populate temporary table ''##MissingCompression''
INSERT INTO ##MissingCompression (objecttype, objectname, command)
SELECT objecttype, objectname, command FROM missingcompression ORDER BY objectname ASC, command DESC '
exec (@dynamicsql)

SET @dynamicsql =
'WITH changecompression
     AS (SELECT ''Table''  AS objecttype,
                s.name + ''.'' + o.name AS objectname,
                ''ALTER TABLE ['' + s.name + ''].['' + o.name + ''] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ' + @compressiontype + ');'' AS command
         FROM  ' + @database + '.sys.objects o
                INNER JOIN  ' + @database + '.sys.partitions p
                  ON p.object_id = o.object_id
                INNER JOIN  ' + @database + '.sys.schemas s
                  ON s.schema_id = o.schema_id
         WHERE  TYPE = ''u''
                AND data_compression <> 0
                AND data_compression_desc <> ''' + @compressiontype + ''' 
                AND Schema_name(o.schema_id) <> ''SYS''
         UNION
         SELECT ''Index'' AS objecttype,
                i.name AS objectname,
                ''ALTER INDEX ['' + i.name + ''] ON ['' + s.name + ''].['' + o.name + ''] REBUILD WITH ( DATA_COMPRESSION = ' + @compressiontype + ');'' AS command
         FROM   ' + @database + '.sys.dm_db_partition_stats ps
                INNER JOIN ' + @database + '.sys.indexes i
                  ON ps.[object_id] = i.[object_id]
                     AND ps.index_id = i.index_id
                     AND i.type_desc <> ''HEAP''
                INNER JOIN ' + @database + '.sys.objects o
                  ON o.[object_id] = ps.[object_id]
                INNER JOIN ' + @database + '.sys.schemas s
                  ON o.[schema_id] = s.[schema_id]
                     AND s.name <> ''SYS''
                INNER JOIN ' + @database + '.sys.partitions p
                  ON p.[object_id] = o.[object_id]
                     AND data_compression <> 0
                     AND data_compression_desc <> ''' + @compressiontype + ''' )
                     
-- populate temporary table ''##ChangeCompression''
INSERT INTO ##ChangeCompression (objecttype, objectname, command)
SELECT objecttype, objectname, command FROM changecompression ORDER BY objectname ASC, command DESC '
exec (@dynamicsql)

-- We now have populated our temporary tables (##MissingCompression & ##ChangeCompression)

-- First, loop objects with no compression.
-- For each object >
--  1) increment the counter, 
--  2) add the object name to the list for display 
--  3) generate the tsql for compression commands

  -- set initial variables
  SET @missingindexcompressiontsql = ''
  SET @missingindextablelist = ''
  SET @missingindexindexlist = ''
  SET @missingcompressiontablecount = 0
  SET @missingcompressionindexcount = 0
  SELECT @TotalRows = Count(* ) FROM ##MissingCompression
  SELECT @CurrentRow = 1

  WHILE @CurrentRow <= @TotalRows
    BEGIN
   SELECT @Objecttype = objecttype,
      @objectname = objectname,
      @command = command
   FROM   ##MissingCompression
   WHERE  uniquerowid = @CurrentRow
      
   SET @missingindexcompressiontsql = @missingindexcompressiontsql + @command + Char(10) + Char(10) 
     
   IF @Objecttype = 'table'
     BEGIN
    SET @missingindextablelist = @missingindextablelist + @objectname + Char(10)     
    SET @missingcompressiontablecount = @missingcompressiontablecount + 1
     END
      
   IF @Objecttype = 'index'
     BEGIN
    SET @missingindexindexlist = @missingindexindexlist + @objectname + Char(10)
    SET @missingcompressionindexcount = @missingcompressionindexcount + 1
     END
      
   SELECT @CurrentRow = @CurrentRow + 1
    END
  
  
-- Now deal with Objects that need to change compression type
-- For each object >
--  1) increment the counter, 
--  2) add the object name to the list for display 
--  3) generate the tsql for compression commands

    -- set initial variables
  SET @changeindexcompressiontsql = ''
  SET @changeindextablelist = ''
  SET @changeindexindexlist = ''
  SET @indexreport = ''
  SET @changecompressiontablecount = 0
  SET @changecompressionindexcount = 0
  SELECT @TotalRows = Count(* ) FROM ##ChangeCompression
  SELECT @CurrentRow = 1

  WHILE @CurrentRow <= @TotalRows
    BEGIN
   SELECT @Objecttype = objecttype,
      @objectname = objectname,
      @command = command
   FROM   ##ChangeCompression
   WHERE  uniquerowid = @CurrentRow
      
   SET @changeindexcompressiontsql = @changeindexcompressiontsql + @command + Char(10) + Char(10)
     
   IF @Objecttype = 'table'
     BEGIN
    SET @changeindextablelist = @changeindextablelist + @objectname + Char(10)     
    SET @changecompressiontablecount = @changecompressiontablecount + 1
     END
      
   IF @Objecttype = 'index'
     BEGIN
    SET @changeindexindexlist = @changeindexindexlist + @objectname + Char(10)
    SET @changecompressionindexcount = @changecompressionindexcount + 1
     END
      
   SELECT @CurrentRow = @CurrentRow + 1
    END

   -- Build the text output for the report  >
   -- First for objects missing compression >
  IF (@missingcompressionindexcount + @missingcompressiontablecount) > 0
    BEGIN
   IF (@missingcompressiontablecount) > 0
     BEGIN
    SET @indexreport = @indexreport + 'Tables not currently utilising ' + @compressiontype + ' compression >' + Char(10) +  '--------------------------------------------' + Char(10) + @missingindextablelist + Char(13) + Char(13)
     END      
   IF (@missingcompressionindexcount) > 0
     BEGIN
    SET @indexreport = @indexreport + 'Indexes not currently utilising ' + @compressiontype + ' compression >' + Char(10) +  '---------------------------------------------' + Char(10) + @missingindexindexlist + Char(13) + Char(13)
     END
    END
 
  -- Now for objects using the incorrect compression type >
  IF (@changecompressionindexcount + @changecompressiontablecount) > 0
    BEGIN
   IF (@changecompressiontablecount) > 0
     BEGIN
    SET @indexreport = @indexreport + 'Tables with incorrect compression type >' + Char(10) + '--------------------------------------------' + Char(13) + Char(10) + @changeindextablelist + Char(13) + Char(10)
     END      
   IF (@changecompressionindexcount) > 0
     BEGIN
    SET @indexreport = @indexreport + 'Indexes with incorrect compression type >' + Char(10) + '---------------------------------------------' + Char(13) + Char(10) + @changeindexindexlist + Char(13) + Char(10)
     END
    END
  IF (@missingcompressionindexcount + @missingcompressiontablecount) > 0
   BEGIN
    SET @indexreport = @indexreport + char(10) + '/* TSQL to implement ' + @compressiontype + ' compression */' + Char(10) + '-----------------------------------' + Char(10) + 'USE [' + @database + ']' + Char(10) + 'GO' + Char(10) + @missingindexcompressiontsql + Char(13) + Char(10)
   END
 IF (@changecompressionindexcount + @changecompressiontablecount) > 0
   BEGIN
    SET @indexreport = @indexreport + char(10) + '/* TSQL to change to ' + @compressiontype + ' compression type */' + Char(10)  + '-------------------------------------' + Char(10) + 'USE [' + @database + ']' + Char(10) + 'GO' + Char(10) + @changeindexcompressiontsql + Char(13) + Char(10)
   END 
-- Tidy up. Remove the temporary tables.
DROP TABLE ##MissingCompression
DROP TABLE ##ChangeCompression

-- Display report and email results if there are any required actions >
IF ( (@changecompressionindexcount + @changecompressiontablecount + @missingcompressionindexcount + @missingcompressiontablecount) > 0)
 BEGIN
  -- Compression changes recommended, display them
  PRINT @indexreport
  -- If email paramters supplied, email the results too.
  IF @emailrecipients <> '' AND @emailprofile <> '' 
   BEGIN
    SET @emailsubject =  @@SERVERNAME + ' : Uncompressed object report : ' + @database + ' (' + @compressiontype + ' compression)'
    -- send email
    EXEC msdb.dbo.sp_send_dbmail
     @recipients = @emailrecipients,
     @subject = @emailsubject,
     @body = @indexreport, 
     @profile_name = @emailprofile
   END  
  END
 ELSE
  BEGIN
   PRINT 'No database objects to compress'
  END
END

GO

CREATE PROCEDURE dbo.UncompressedServerObjects AS
BEGIN 
SET NOCOUNT ON

DECLARE  @CurrentRow INT
DECLARE  @TotalRows INT
DECLARE  @DatabaseName NVARCHAR(255)                   
DECLARE  @Databases  TABLE(
   UNIQUEROWID  INT IDENTITY ( 1,1 ) PRIMARY KEY NOT NULL,
   DATABASENAME NVARCHAR(255)
   )

SELECT @CurrentRow = 1
               
INSERT INTO @Databases (DATABASENAME)
 SELECT NAME
 FROM SYS.DATABASES
 WHERE DATABASE_ID > 4
               
SELECT @TotalRows = COUNT(*) FROM @Databases
 
WHILE @CurrentRow <= @TotalRows  
 BEGIN    
  SELECT @DatabaseName = DATABASENAME      
  FROM @Databases     
  WHERE UNIQUEROWID = @CurrentRow         

  EXEC dbo.UncompressedObjects
    @database = @DatabaseName 
  , @compressiontype = 'PAGE'
  , @emailrecipients = 'emailaddress@domain.com'
  , @emailprofile = 'Profile Name' 

  SELECT @CurrentRow = @CurrentRow + 1  
 END 
END
GO