Setting up an instance of SQL Server 2008 R2 64bit Standard, got this error when trying to connect from off the server.

Still working to determine the cause, in the meantime was able to temporarily remedy by disabling names pipes and setting service account to “LocalSystem”.

.instance
(local)instance
127.0.0.1instance – loopback
servernameinstance
ipinstance
ip,port
tcp:servernameinstance – tcp/ip
lpc:servernameinstance – shared memory
np:servernameinstance – named pipes

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')