Disk Partition Alignment Best Practices for SQL Server

Bytes per Cluster for Data/Log/TempDB disks: 65536    (64K)

Bytes per Cluster for OS, Application, Pagefile disks: 4096   (default 4K)

Check using:

fsutil fsinfo ntfsinfo d:

Default offset since Windows Server 2008 has been aligned at 1024k, may need to align to 1024k for disks formatted prior to Server 2008.

Check using:

wmic partition get BlockSize, StartingOffset, Name, Index

or

diskpart

select disk 1

list part

http://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

Clear Readonly Disk Attrib

Just got a spanky new VM for my squirrel server and the sysadmin took off for the holiday and left the drives offline.

Disk Management for whatever reason was no help, even running as Administrator.  The disks were flagged as readonly by policy and would not online or initialize via the GUI tool.

So break out the cmd window and get to DISKPARTying

DISKPART
select disk #
online disk
detail disk
attribute disk clear readonly
detail disk

Now can close and reopen Disk Management (diskmgmt.msc) and it can now initialize the disks :)



http://support.microsoft.com/kb/971436

SYSUTCDATETIMEOFFSET()

Why isn't there a

SYSUTCDATETIMEOFFSET() 

function for

TODATETIMEOFFSET(SYSUTCDATETIME(),'+00:00') 

Time zone conversion that is Daylight savings time aware

After a cursory search, could not find any built-in for SQL Server that do this or anything on the net and had to knock something out very quick. Would be very interested in a more efficient manner to convert to DateTimeOffset in a way that is daylight savings time aware.  This bit of code gives me 3 years to find a better solution.  Of course would be best to just have all the servers set to UTC the problem solved! :)

/*************************************************************************
 * NAME:
 * dbo.toUSTZ
 *
 * TYPE:
 * Function
 *
 * DESCRIPTION:
 * Converts a US Timezone to DateTimeOffset accounting for Daylight Savings Time
 * DST in US begins at 2:00 a.m. on the second Sunday of March and
 * ends at 2:00 a.m. on the first Sunday of November
 * REF: http://www.nist.gov/pml/div688/dst.cfm
 *
 * RETURNS:
 * datetimeoffset(0) (ie: '2014-10-31 18:25:51 +00:00')
 *
 * PARAMETERS:
 * @thedate datetime2 --the date to be converted
 * @timezone char(2)  --date's US time zone
 * Code ST  DST Time Zone
 * UT   +0  +0  Universal Time
 * AT   -4  -4  Atlantic
 * ET   -5  -4  Eastern
 * CT   -6  -5  Central
 * MT   -7  -6  Mountain
 * AZ   -7  -7  Arizona
 * PT   -8  -7  Pacific
 * AK   -9  -8  Alaska
 * HT   -10 -10 Hawaii
 * ST   -11 -11 Samoa
 * CH   +10 +10 Chamorro
 *
 * TODO: Add additional years and/or find more elegant way to do this...
 * REF: http://www.nist.gov/pml/div688/localtime.cfm#zones
 *
 * REVISION HISTORY:
 * 20141102 JMO Created
 *************************************************************************/
ALTER FUNCTION [dbo].[toUSTZ]
( @thedate datetime2
, @timezone char(2)
)
RETURNS datetimeoffset(0)
AS
BEGIN
   DECLARE @offsettime datetimeoffset(0)
         , @tzoffset varchar(10)

   --TODO: need function for second sunday in march and first sunday in november
   --      or make lookup table - but would it be more efficient?
   IF    @thedate BETWEEN '20100314 02:00:00' AND '20101107 01:59:59'
      OR @thedate BETWEEN '20110313 02:00:00' AND '20111106 01:59:59'
      OR @thedate BETWEEN '20120311 02:00:00' AND '20121104 01:59:59'
      OR @thedate BETWEEN '20130310 02:00:00' AND '20131103 01:59:59'
      OR @thedate BETWEEN '20140309 02:00:00' AND '20141102 01:59:59'
      OR @thedate BETWEEN '20150308 02:00:00' AND '20151101 01:59:59'
      OR @thedate BETWEEN '20160313 02:00:00' AND '20161106 01:59:59'
      OR @thedate BETWEEN '20170312 02:00:00' AND '20171105 01:59:59'
      OR @thedate BETWEEN '20180311 02:00:00' AND '20181104 01:59:59'
    BEGIN
      SELECT @tzoffset = CASE @timezone
                           WHEN 'UT' THEN '+00:00'
                           WHEN 'AT' THEN '-04:00'
                           WHEN 'ET' THEN '-04:00'
                           WHEN 'CT' THEN '-05:00'
                           WHEN 'MT' THEN '-06:00'
                           WHEN 'AZ' THEN '-07:00'
                           WHEN 'PT' THEN '-07:00'
                           WHEN 'AK' THEN '-08:00'
                           WHEN 'HT' THEN '-10:00'
                           WHEN 'ST' THEN '-11:00'
                           WHEN 'CH' THEN '+10:00'
                           ELSE NULL
                         END
    END
   ELSE
    BEGIN
      SELECT @tzoffset = CASE @timezone 
                           WHEN 'UT' THEN '+00:00'
                           WHEN 'AT' THEN '-04:00'
                           WHEN 'ET' THEN '-05:00'
                           WHEN 'CT' THEN '-06:00'
                           WHEN 'MT' THEN '-07:00'
                           WHEN 'AZ' THEN '-07:00'
                           WHEN 'PT' THEN '-08:00'
                           WHEN 'AK' THEN '-09:00'
                           WHEN 'HT' THEN '-10:00'
                           WHEN 'ST' THEN '-11:00'
                           WHEN 'CH' THEN '+10:00'
                           ELSE NULL
                         END
    END

   SET @offsettime = todatetimeoffset( @thedate, @tzoffset )
   RETURN(@offsettime)
END --f_toUSTZ
GO


/*************************************************************************
 * NAME:
 * dbo.toUTC
 *
 * TYPE:
 * Function
 *
 * DESCRIPTION:
 * Converts to UTC DateTimeOffset accounting for Daylight Savings Time
 *
 * RETURNS:
 * datetimeoffset(0) (ie: '2014-10-31 18:25:51 +00:00')
 *
 * PARAMETERS:
 * @thedate datetime2 --the date to be converted
 * @timezone char(2)  --the date's US time zone
 * Zone ST  DST
 * UT   +0  +0  Universal Time
 * AT   -4  -4  Atlantic
 * ET   -5  -4  Eastern
 * CT   -6  -5  Central
 * MT   -7  -6  Mountain
 * AZ   -7  -7  Arizona
 * PT   -8  -7  Pacific
 * AK   -9  -8  Alaska
 * HT   -10 -10 Hawaii
 * ST   -11 -11 Samoa
 * CH   +10 +10 Chamorro
 * REF: http://www.nist.gov/pml/div688/localtime.cfm#zones
 *
 * REFERENCES: 
 * dbo.toUSTZ
 *
 * REVISION HISTORY:
 * 20141102 JMO Created
 *************************************************************************/
CREATE FUNCTION [dbo].[toUTC]
( @thedate datetime2
, @timezone char(2)
)
RETURNS datetimeoffset(0)
AS
BEGIN
   DECLARE @offsettime datetimeoffset(0)
         , @utctime datetimeoffset(0)

   SET @offsettime = dbo.toUSTZ( @thedate, @timezone )
   SET @utctime = switchoffset( @offsettime, '+00:00' )
   RETURN(@utctime)
END --toUTC

GO

Trace Flag 2861

Some monitoring tools turn this on.

http://logicalread.solarwinds.com/why-dpa-uses-sql-server-trace-flag-2861-and-zero-cost-plans-tl01/#.VF5CMvnF9Ps


From Microsoft Technet:

SQL 8 - Cache query plans for queries that have a cost of zero or near to zero. When turned on, fn_get_sql function can return the SQL text for activities that have zero cost plans kb 325607 

Tip: Avoid Using Trace Flag 2861 to Cache Zero-Cost Query Plans

http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx

http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_042705.mspx


Basically if you have a very very busy server, you should turn this off.  Otherwise, if it not making an appreciable difference, it can benefit some monitoring tools.

SQL Server Security best practices

• Minimize the number of SQL Server logins.


• Use Windows group logins to simplify ongoing management where possible.


• Disable logins rather than dropping them if there is any chance that they will be needed again.


• Ensure that expiry dates are applied to logins that are created for temporary purposes.


• Use fixed server-level roles to delegate server-level management responsibility, and only create user-defined server-level roles if your specific administrative delegation solution requires them.


• Disable the guest user in user databases unless you specifically require guest access.


• Aim to grant the minimum number of explicit permissions possible to meet the security requirements, and use membership of roles and inheritance to ensure the correct effective permissions.


• Ensure every user has only the permission they actually require.

Using SQL Server Restore to see Backup Media Set Info

restore headeronly
from disk = 'V:\backups\mydb.bak'
go

restore filelistonlyfrom disk = 'V:\backups\mydb.bak'
go

restore verifyonly
from disk = 'V:\backups\mydb.bak'
go

Bulk-Logged Recovery

These operations are minimally logged:

• SELECT INTO.
• Bulk load operations (bcp and BULK INSERT).
• CREATE INDEX (including indexed views).
• text and image operations (WRITETEXT and UPDATETEXT).

SQL Server DDL Change Control

-- =============================================
-- Author:      sqlforge.blogspot.com
-- Create date: 20141008
-- Description: audit ddl changes
--              compatible SQL Server 2005+
-- =============================================

USE [master]
GO


IF EXISTS (SELECT TOP 1 1 FROM [master].[sys].[server_triggers] WITH (NOLOCK) WHERE parent_class_desc = 'SERVER' AND name = N'tr_ObjectChangeAudit') -- sql server 2005+
   DISABLE TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO


IF EXISTS (SELECT TOP 1 1 FROM sysobjects WITH (NOLOCK) WHERE TYPE = 'U' AND name = 'changelog')
 BEGIN
    PRINT 'master.dbo.changelog exists, check to be sure no differences in table from older version'
 END
ELSE
 BEGIN
CREATE TABLE [dbo].[changelog](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [databasename] [varchar](256) NOT NULL,
 [eventtype] [varchar](50) NOT NULL,
 [objectname] [varchar](256) NOT NULL,
 [objecttype] [varchar](25) NOT NULL,
 [sqlcommand] [varchar](max) NOT NULL,
 [eventdate] [datetime] NOT NULL,
 [loginname] [varchar](256) NOT NULL
)

ALTER TABLE [dbo].[changelog] ADD  CONSTRAINT [df_changelog_eventdate]  DEFAULT (getdate()) FOR [eventdate]

 END
GO


IF  EXISTS (SELECT * FROM sys.objects WITH (NOLOCK) WHERE type = 'P' AND name = 'sp_ObjectChangeAudit' )
DROP PROCEDURE [dbo].[sp_ObjectChangeAudit]
GO

-- =============================================
-- Author:      sqlforge.blogspot.com
-- Create date: 20141008
-- Description: audit ddl changes
-- =============================================
CREATE PROCEDURE [dbo].[sp_ObjectChangeAudit]
( @databasename varchar(256)
, @eventtype varchar(50)
, @objectname varchar(256)
, @objecttype varchar(25)
, @sqlcommand varchar(max)
, @loginname varchar(256)
)
AS
BEGIN
   SET NOCOUNT ON;

     INSERT INTO [master].[dbo].[changelog]
               ( databasename
               , eventtype
               , objectname
               , objecttype
               , sqlcommand
               , loginname 
               )
          VALUES 
               ( @databasename
               , @eventtype
               , @objectname
               , @objecttype
               , @sqlcommand
               , @loginname 
               )
END --sp_ObjectChangeAudit
GO


IF EXISTS (SELECT TOP 1 1 FROM [master].[sys].[server_triggers] WITH (NOLOCK) WHERE parent_class_desc = 'SERVER' AND name = N'tr_ObjectChangeAudit') -- sql server 2005+
   DISABLE TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO

IF EXISTS (SELECT TOP 1 1 FROM [master].[sys].[server_triggers] WITH (NOLOCK) WHERE parent_class_desc = 'SERVER' AND name = N'tr_ObjectChangeAudit') -- sql server 2005+
   DROP TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO


-- =============================================
-- Author:      sqlforge.blogspot.com
-- Create date: 20141008
-- Description: audit ddl changes
-- =============================================
CREATE TRIGGER [tr_ObjectChangeAudit]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR create_procedure, alter_procedure, drop_procedure, create_table, alter_table, drop_table, create_function, alter_function, drop_function 
AS 
   DECLARE @databasename varchar(256)
         , @eventtype varchar(50)
         , @objectname varchar(256)
         , @objecttype varchar(25)
         , @sqlcommand varchar(max)
         , @loginname varchar(256)
BEGIN
   SET NOCOUNT ON; 
   SET ANSI_PADDING ON;

   DECLARE @data xml
   SET @data = eventdata() 

       SELECT @databasename = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
            , @eventtype = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')
            , @objectname = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
            , @objecttype = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)')
            , @sqlcommand = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
            , @loginname = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)') 

BEGIN TRY
   EXEC [master].[dbo].[sp_ObjectChangeAudit]
        @databasename 
      , @eventtype 
      , @objectname 
      , @objecttype 
      , @sqlcommand
      , @loginname
END TRY
BEGIN CATCH
   PRINT 'SERVER TRIGGER tr_ObjectChangeAudit ' + ERROR_MESSAGE();
END CATCH
      
END --tr_ObjectChangeAudit


GO

DISABLE TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO

ENABLE TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO


/*****************************

IF EXISTS ( SELECT TOP 1 1 FROM sysobjects WHERE type = 'P' AND name = 'TESTME_01234' )
  DROP PROCEDURE TESTME_01234
GO

CREATE PROCEDURE TESTME_01234
AS
BEGIN
   SELECT 1;
END
GO

  DROP PROCEDURE TESTME_01234
GO


SELECT * FROM master.dbo.changelog ORDER BY eventdate DESC
GO
****************************************/