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

SQLIO & SQLIOSIM

SQLIO is a lightweight command line application from Microsoft, to test your disk I/O and get some performance benchmarking figures.

Resources:
Brent Ozar blog post about SQLIO: http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/
Download SQLIO from Microsoft: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=20163

SQLIOSIM is not a performance benchmarking tool, but more of a stress testing tool, due to it's use of random patterns. Unlike SQLIO, SQLIOSIM has a graphical user interface. SQLIOSIM will test what kind of I/O you'll get from a database based on where you'll put your data and log files, auto grow configuration, whether or not you're using sparse files etc

Resources:
Download SQLIOSIM from Microsoft: https://support.microsoft.com/en-us/kb/231619

Trace Flags

Trace flags are used within SQL Server to provide information / logging about specific system events, or to change specific configuration of the SQL Server instance. 

Trace flags can be set on / off for the current connection only, using the following T-SQL code:

-- Turn on trace flag 1234

DBCC TRACEON (1234);

GO

-- Turn off trace flag 1234

DBCC TRACEOFF (1234);

GO

Traces can be set on / off globally (i.e. for ALL connections to the SQL Engine) with the addition of the -1 argument at the end:

 -- Turn on trace flag 1234, globally

DBCC TRACEON (1234, -1);

GO

Multiple traces can be switched on by listing them within the parenthesis:

-- Turn on trace flags 1234, 2345 and 3456

DBCC TRACEON (1234, 2345, 3456);

GO

You can see which trace flags are currently active as follows (if none are active, a message of "DBCC execution completed. If DBCC printed error messages, contact your system administrator" will appear): 

-- Check status of current trace flags

DBCC TRACESTATUS

Only members of the sysadmin fixed group can switch traces on / off.

Useful Trace Flags:
1222: Examine deadlocks
1204: Write deadlock information to SQL Server log

Trace flag resources:
MSDN: https://msdn.microsoft.com/en-us/library/ms188396.aspx
SQL Mag: http://sqlmag.com/sql-server/investigating-trace-flags
Stack Exchange: http://dba.stackexchange.com/questions/27704/sql-server-concise-list-of-trace-flags