Recovering access

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 BUILTIN\Administrators was by default a member of sysadmin.   In 2008/2008R2 that login was not added by default, but NT AUTHORITY\SYSTEM still is.

psexec -s -i cmd

sqlcmd

create login [login] from windows
go

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

Powershell ISE

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 and Update Statistics

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.

How to get out of SINGLE_USER mode

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

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
****************************************/

ODBC: Another 32/64bit MS undocumented "feature"

There are two different ODBC Administrator runtimes on a 64-bit machine. 

The first ODBC Manager is used to manage 64-bit data sources, while the second is used to manage 32-bit data sources. 

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 ODBC Manager will be for 64-bit data sources. 

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

“ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application” 

Use this ODBC Manager to Review 64-Bit Data Source Names 
c:\windows\system32\odbcad32.exe 

Use this ODBC Manager to Review 32-Bit Data Source Names c:\windows\sysWOW64\odbcad32.exe

Login History Server Trigger

use master
go

IF NOT EXISTS ( SELECT TOP 1 1 FROM master.sys.sysobjects WHERE type='U' AND name='ServerLoginHistory' )
 BEGIN
  CREATE TABLE [dbo].[ServerLoginHistory]
  (
   [SystemUser] [varchar](512) NULL,
   [HostName] [varchar](512) NULL,
   [DBUser] [varchar](512) NULL,
   [SPID] [int] NULL,
   [LoginTime] [datetime] NULL,
   [AppName] [varchar](512) NULL,
   [DatabaseName] [varchar](512) NULL
  ) 
 END
GO


IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'tr_ServerLoginHistory')
 BEGIN
   DROP TRIGGER [tr_ServerLoginHistory] ON ALL SERVER
 END
GO



CREATE TRIGGER [tr_ServerLoginHistory]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON 
AS
BEGIN
   IF ORIGINAL_LOGIN() NOT IN ( 'NT AUTHORITY\SYSTEM' )
    BEGIN
     INSERT INTO [ServerLoginHistory]
          SELECT ORIGINAL_LOGIN(), HOST_NAME(),USER, @@SPID, GETDATE(), APP_NAME(), DB_NAME()
    END
END --tr_ServerLoginHistory
GO


ENABLE TRIGGER [tr_ServerLoginHistory] ON ALL SERVER
GO
        
        
--DISABLE TRIGGER [tr_ServerLoginHistory] ON ALL SERVER
--GO

how to tell collation

SELECT 
   DATABASEPROPERTYEX('[DBNAME]', 'Collation') DBCollation
 , SERVERPROPERTY ('Collation') "ServerCollation";

apache2

Notes for managing vhosts on apache2 (Apache 2.2.16 Ubuntu)



Add new vhosts:
1. create folder for site in /var/www/vhosts/
2. create vhost file in /etc/apache2/sites-available
3. create ln to sites-available file from sites-enabled
4. Restart Apache
/etc/init.d/apache2 restart

To check version:

apache2ctl -v or apache2 -v 

Steps to clean install new Mule ESB server version

1. unregister old version in MMC
2. mule stop
3. mule remove
4. archive old version folder
5. remove old version folder
6. unzip new version of mule
7. Change %MULE_HOME% environmental variable to point to new version
8. start new command shell
9. check echo %MULE_HOME% to confirm value set to new version
10. mule -installLicense licensefile.lic
11. mule install
12. mule start
13. register new version in MMC
14. deploy mule apps

Find OS User for command shell spawn from SQL Server context

If the policy on the server allows execution of xp_cmdshell then can find out the windows user that owns the spawned shell to handle OS commands executed from within sql server context

   EXEC xp_cmdshell 'whoami'

to get the value into a variable can do following

   DECLARE @whoisit nvarchar(4000)
   CREATE TABLE #test ( output nvarchar(4000) null )

   INSERT INTO #test (output)
   EXEC xp_cmdshell 'whoami'

   SELECT top 1 @whoisit = output 
     FROM #test 
    WHERE output IS NOT NULL

   SELECT @whoisit "whoisit"

   DROP TABLE #test

Permission paths for login

You can look for the permission paths used by a login as follows:

EXEC xp_logininfo 'DOMAIN\USER_A', 'all'

SQL Server 2012: Kill all processes for a specific database

          SELECT 'KILL ' + cast(A.session_id as varchar)
            FROM sys.dm_exec_sessions A WITH (NOLOCK)
      INNER JOIN sys.databases B WITH (NOLOCK)
              ON A.database_id = B.database_id
           WHERE B.name = 'your.dbname.here'

Why don’t software development methodologies work?

My long standing thesis that you cannot apply assembly line theory to software development, it is the people that make it succeed or fail not the process.

People all too often look to a tool or a process to fix their problem, but what typically happens is that all these things serve to do is divert attention away from the problem and distract people with process and tools.

Kind of like buying gym memberships or exercise equipment expecting them to make you healthy.  The thing is, if someone isn't already exercising on their own (jogging, doing push-ups and sit-ups), then no amount of equipment or tools will solve their problem.  Instead, if a person is already in the right mindset and demonstrating the right behavior, then tools can help leverage performance to the next level -- basically do the right thing, better.

My experiences and conclusions mirror those of the author of the article linked to below:

Why don’t software development methodologies work?

[Excerpt] It’s common now for me to get involved in a project that seems to have no adult supervision. Surprisingly, left to themselves programmers don’t revert to cowboy coding—they adopt or create methodologies stricter and more filled with ritual than anything I experienced in 1980. In fact programmers today can be much more rigid and religious about their methodologies than they imagine a 1970s-era COBOL shop was. I now routinely get involved with projects developed by one or two people burdened with so much process and “best practices” that almost nothing of real value is produced.