You can set up SQL Server aliases on a Windows Server or PC using the Network Client Utility even if they don’t have the SQL Server Client tools installed.

The trick is to keep in mind that there are two different SQL Server Network Client Utility runtimes on a 64-bit machine.

If you are running a 32-bit Operating System, you will have only 32 bit drivers installed.

If you are using a 64 bit machine, the default utility will be for 64-bit data sources.

If you have a 64bit OS and are trying to access an alias in your 32bit application and receive the error check to see if you have an alias configured for the architecture of your application ( 32-bit / 64-bit ).

Use this utility to Review 64-Bit aliases and protocols
c:\windows\system32\cliconfg.exe

Use this utility to Review 32-bit aliases and protocols
c:\windows\sysWOW64\cliconfg.exe

Additional info:
http://geekswithblogs.net/twickers/archive/2009/12/08/136830.aspx

http://blogs.technet.com/b/meamcs/archive/2013/01/22/creating-a-sql-server-alias-using-the-sql-server-client-network-utility.aspx

http://www.sharepointassist.com/2010/02/02/configure-a-sql-server-alias-for-sharepoint-sql-server-2008/

Local admins can get into instance when sysadmin access has been lost by starting the server in single user mode.   Here is how recover a sysadmin account without having to restart.  Prior to 2008 BUILTINAdministrators was by default a member of sysadmin.   In 2008/2008R2 that login was not added by default, but NT AUTHORITYSYSTEM still is.

psexec -s -i cmd

sqlcmd

create login [login] from windows
go

exec [sys].[sp_addsrvrolemember] 'login', 'sysadmin';
go

Powershell ISE is not installed by default on Windows Server 2008 R2

To install it, launch Powershell and enter the following commands:

Import-Module ServerManager
Add-WindowsFeature PowerShell-ISE

Rebuild Indexes, then update statistics.

An index rebuild also updates index statistics with 100% sample rate.

After an index rebuild, only statistics not updated by the index-rebuild need to be updated.

An index re-org/defrag does not update statistics

In SQL Server 2005+ sp_updatestats only updates the statistics that need to be updated.

USE [master]

GO

     SELECT ‘kill ‘ + cast(spid as varchar)

       FROM master.sys.sysprocesses

      WHERE dbid = DB_ID(‘dbname’)

GO
     SELECT ‘kill ‘ + cast(request_session_id as varchar)

       FROM   sys.dm_tran_locks

      WHERE resource_database_id = DB_ID(‘dbname’)

GO
     SELECT ‘kill ‘ + cast(session_id as varchar)

       FROM sys.dm_exec_requests

      WHERE database_id = DB_ID(‘dbname’)

GO
–KILL any processes connected to dbname
SET DEADLOCK_PRIORITY HIGH

GO

exec sp_dboption ‘[dbname] ‘, ‘single user’, ‘FALSE’;

GO

ALTER DATABASE [dbname] SET MULTI_USER WITH NO_WAIT

GO

ALTER DATABASE [dbname] SET MULTI_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE [dbname] SET READ_WRITE WITH NO_WAIT

GO

Why isn’t there a

SYSUTCDATETIMEOFFSET() 

function for

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

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

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.