SQL Server Start Up Switches

Some useful SQL Server start up switches:

Switch	Action
-d	Default startup option – specifies the fully qualified path for the master database file. If this is not provided, the current registry value will be used
-e	Default startup option – specifies the fully qualified path for the master database error log file. If this is not provided, the current registry value will be used
-l	Default startup option – specifies the fully qualified path for the master database log file If this is not provided, the current registry value will be used
-m	Start SQL Server in single user mode. Allows any member of the Local Administrator group to connect as sysadmin onto the SQL instance. CHECKPOINT process is not started
-m”client app name”	Specify which application can take the single user connection (e.g. –m”SQL Server Management Studio – Query”). Use when an unknown application keeps grabbing the single user connection when SQL Server starts
-f	Starts SQL Server in minimal configuration mode (this is also single user mode as well). Useful if a configuration setting is stopping SQL Server from starting
-n	Doesn’t use Windows Application Log to record events. Use –e in conjunction with this to ensure events are logged to SQL error log
-s	Start a named instance. Without this, a default instance is started
-Ttrace#	Start SQL with a specified trace flag (note the uppercase T)
-x	Disables several monitoring features
-c	Shortens the time taken to start SQL Server from command line

The following T-SQL code will tell you which start up switches have currently been applied to SQL Server:

SELECT

    DSR.registry_key,

    DSR.value_name,

    DSR.value_data

FROM sys.dm_server_registry AS DSR

WHERE

    DSR.registry_key LIKE N'%MSSQLServer\Parameters';

...and querying the sys.dm_server_registry dynamic view in it's entirety will give you details about the registry entries for SQL Server.

Resources:
Info on trace flags: http://www.sqlservercentral.com/articles/trace+flags/70131/

What's new in SQL Server 2014

Some information on the new features / enhancements  introduced in SQL Server 2014:

In memory OLTP engine:
Allows tables and stored procedures to move to memory to drastically reduce IO and improve performance many times over. Microsoft provide the Analysis, Migrate and Report (AMR) tool that identifies objects that would benefit from being moved into memory.

AlwaysOn Availability Groups:
SQL Server now supports up to 8 secondary replicas (as opposed to 4 in SQL 2012), and secondary replicas are available for read only workloads even when the primary replica is unavailable. Now also supports using Azure for asynchronous replicas for high availability / disaster recovery scenarios.

http://sqlmag.com/sql-server-2014/sql-server-2014-important-new-features

Permissions - Server & Database Roles

When assigning permissions, it is important to always grant the least privileged role - minimum permission to perform the function that is required.

Server Roles:
More info from MSDN

Fixed server-level role	Description
sysadmin	Members of this role can perform any action on the SQL Server instance. Initial membership of this role happens at installation
serveradmin	Members can perform instance wide administration tasks, including shutting down the instance
securityadmin	Members can manage instance level permissions. Permissions for this role are equivalent to sysadmin - as members of this role can elevate their own permissions to sysadmin
processadmin	Members can view and terminate processes on the database engine
setupadmin	Members can add / remove linked servers to the database instance, and execute some system stored procedures
bulkadmin	Members can run the BULK INSERT statement
diskadmin	Members can administer database file configuration
dbcreator	Ability to create databases (database will be owned by the creator)
public	Every login is automatically a member of the public role; you cannot change membership for public. You can change the permissions for public however. If a user has no permissions granted, they will revert to the permissions assigned to public

Database Roles:

More info from MSDN

Database-level role name	Description
db_owner	Has permissions to do anything within the database
db_securityadmin	Members can administer security of roles (note: can elevate their own permissions to db_owner)
db_accessadmin	Can administer who has access to database for Windows Logins, Windows Groups and SQL Server Logins
db_backupoperator	Members have ability to backup the database. Cannot restore the database - this is reserved for dbcreator / sysadmin fixed server roles
db_ddladmin	Can perform any Data Definition Language (DDL) operations (those that create / modify structure of database), e.g. CREATE, ALTER, DROP, TRUNCATE
db_datawriter	Allows users to add / delete / update data on all user tables
db_datareader	Allows users to read data from all user tables
db_denydatawriter	Members cannot add / delete / update any data in any user tables
db_denydatareader	Members cannot read data from any user tables

:

High Availability - AlwaysOn Availability Groups

Overview:
AlwaysOn Availability Groups can be thought of as a hybrid of the best of clustering, and the best of mirroring. It must be configured on a Windows Server Failover Cluster, but rather than installing SQL Server as a clustered installation with shared storage for the databases, a separate installation of SQL Server is installed on each node of the Windows Cluster, using the local disks of the cluster node (i.e. the OS is clustered, SQL Server isn't). Like clustering, the Availability Groups have a shared IP / name that everyone can point to. Unlike clustering, databases are mirrored across all servers in the cluster. There can be one primary, and up to four replicas. Updates can be asynchronous or synchronous. Requires Full recovery mode for databases.

Secondaries / Read Only Replicas:
Secondaries can be used as read only databases for backups / reporting / DBCC checks etc, although these functions lend themselves more to secondary replicas in an asynchronous configuration, as synchronous secondaries are more useful for failover.

Connecting to Secondaries:
You can connect directly to a secondary replica, or indirectly by using the "ApplicationIntent" (either ReadWrite or ReadOnly) within the connection string for SQL Server connections, with Read Only routing configured on the listener service for the main shared instance name.

Failover Types:
From MSDN:

• Synchronous-commit replicas support two settings—automatic or manual. The "automatic" setting supports both automatic failover and manual failover. To prevent data loss, automatic failover and planned failover require that the failover target be a synchronous-commit secondary replica with a healthy synchronization state (this indicates that every secondary database on the failover target is synchronized with its corresponding primary database). Whenever a secondary replica does not meet both of these conditions, it supports only forced failover. Note that forced failover is also supported a replicas whose role is in the RESOLVING state.
• Asynchronous-commit replicas support only the manual failover mode. Moreover, because they are never synchronized, they support only forced failover.

Pros:
Good for high availability, and reporting via usable secondary copies of the data.

Cons:
You cannot add system databases to an Availability Group. To that end, logins, SQL Jobs etc are not available on secondary replicas automatically - these must be manually set up / kept in sync.
Expensive, as you must license each node individually.
Doubles your data (or more) - as you have multiple copies of the databases many times.
Distributed transactions are not guaranteed in the event of failover.
All servers in the availability group must be in the same domain

Resources:
Great post here around HA options in SQL Server: http://dba.stackexchange.com/questions/53815/clustering-vs-transactional-replication-vs-availability-groups

Moving a SQL Server to a Different Domain

Things to consider:

http://dba.stackexchange.com/questions/37583/moving-sql-server-to-different-domain

SP_WHO2 into a table to query

The following SQL will return the results of a SP_WHO2 query into a temporarty #SP_WHO2 table to allow querying of the table:

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),

      Login  VARCHAR(255),HostName  VARCHAR(255),

      BlkBy  VARCHAR(255),DBName  VARCHAR(255),

      Command VARCHAR(255),CPUTime INT,

      DiskIO INT,LastBatch VARCHAR(255),

      ProgramName VARCHAR(255),SPID2 INT,

      REQUESTID INT)

INSERT INTO #sp_who2 EXEC sp_who2

SELECT      *

FROM        #sp_who2

-- Add any filtering of the results here :

WHERE       DBName <> 'master'

-- Add any sorting of the results here :

ORDER BY    DBName ASC

DROP TABLE #sp_who2

Estimate time remaining for backup / restores

Use the following SQL to estimate time remaining for active backup / restore jobs within SQL Server:

select    session_id,
    start_time,
    status,
    command,
    percent_complete,
    estimated_completion_time,
    estimated_completion_time / 60 / 1000 as estimate_completion_minutes,    dateadd(n,(estimated_completion_time / 60 / 1000),getdate()) as estimated_completion_timefrom    sys.dm_exec_requests
where    command = 'BACKUP DATABASE'
    or command = 'RESTORE DATABASE'