Create an Extended Events Session Using the Wizard

You can create Extended Events sessions to diagnose SQL Server tracing, which enables you to resolve issues such as the following:

·         Find your most expensive queries

·         Find root causes of latch contention

·         Find a query that is blocking other queries

·         Troubleshoot excessive CPU usage caused by query recompilation

·         Troubleshoot deadlocks

You can create an Extended Events session by using the Query Editor, or you can create a session in Object Explorer. In Object Explorer, Extended Events provides two user interfaces you can use to create, modify, and view event session data — a wizard that guides you through the event session creation process, and a New Session UI that provides more advanced configuration options.

If you have installed SQL Server 2012 Management studio then you can view extended event session mentioned in screen. You can use XE (extended event) for older versions of sql servers using SSMS 2012 or add in to install for 2008 R2 Management studio.

SQL SERVER 2012 Enhancements summery

SQL SERVER 2012 Enhancements

Cloud-ready platform

Database Administrators

·         AlwaysOn Availability Groups

·         AlwaysOn Failover Cluster Instances (FCI)

·         Support for Windows Server Core

·         Recovery Advisor  (Scroll bar to show when and which backup will take place)

Scalability and Performance Enhancements

·         Column store Indexes

·         Partition Support Increased ( up to 15000)

·         Online Index Create, Rebuild, and Drop ( Max data types)

·         Achieve Maximum Scalability with Windows Server 2008 R2 ( support up to 256 processors and  2 TB memory in single OS instance)

Manageability Enhancements

·         SQL Server Management Studio

·         IntelliSense Enhancements

·         A new Insert Snippet menu to insert template (CTRL+K)

·         Transact-SQL Debugger

·         Resource Governor Enhancements (dm_resource_governor_resource_pool_affinity)

·         Tight Integration with SQL Azure

·         Easy Startup Options Relocated

·         Data-Tier Application (DAC) Enhancements (single unit of deployment containing all of the database’s schema, dependent objects, and deployment requirements used by an application)

Security Enhancements

·         User-defined server roles for easier separation of duties

·         Audit enhancements on all SKUs to improve compliance and resiliency

o   Stop, continue and fail audit on network failure

o   User define audit events like application users  using sys.sp_audit_write

o   Extended event can filter data in audit saving.

·         Simplified security management, with a default schema for groups

·         Contained Database Authentication, which provides database authentication that uses self-contained access information without the need for server logins

o   Self-copy of database, no orphan users

o   Used in migration and not in replication, change data capture

·         SharePoint and Active Directory security models for higher data security in end-user reports

Programmability Enhancements

·         FileTable It builds on FILESTREAM technology. FileTable offers organizations Windows file namespace support and Application compatibility with the file data stored in SQL Server.

·         Statistical Semantic Search  Three new Transact-SQL rowset functions to query unstructured data

·         Full-Text Search Enhancements finding author, title etc from file without open it

Editions ( 32 and 64 bit)

·         Enterprise edition

·         Standard edition

·         Business Intelligence edition

·         Specialized editions

o   Web

o   Developer

o   Express

Contained databases in SQL Server 2012 ( How to implement)

Contained databases are new in 2012 and we can define as "A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database." 

SQL Server 2012 helps user to isolate their database from the instance in 4 ways.

1-         Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)

2-         All metadata are defined using the same collation.

3-         User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.

4-         The SQL Server environment (DMV's, XEvents, etc.) reports and can act upon containment information.

The following terms apply to the contained database model.

Database boundary

The boundary between a database and the instance of SQL Server. The boundary between a database and other databases.

Contained

An element that exists entirely in the database boundary.

Uncontained

An element that crosses the database boundary.

Non-contained database

A database that has containment set to NONE. All databases in versions earlier than SQL Server 2012 are non-contained. By default, all SQL Server 2012 databases have a containment set to NONE.

Partially contained database

A partially contained database is a contained database that can allow some features that cross the database boundary. SQL Server includes the ability to determine when the containment boundary is crossed.

Contained user

There are two types of users for contained databases.

·         Contained database user with password

Contained database users with passwords are authenticated by the database.

·         Windows principals

Authorized Windows users and members of authorized Windows groups can connect directly to the database and do not need logins in the master database. The database trusts the authentication by Windows.

Enabling contained database support from server properties

Or

XEC sp_configure 'show advanced', 1
GO
RECONFIGURE
GO

EXEC sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO

Set option to Partial

Some features of partially contained databases, such as storing metadata in the database, apply to all SQL Server 2012 databases. Some benefits of partially contained databases, such as database level authentication and catalog collation, must be enabled before they are available

Creating a user that has no access outside its database is contained database user

User can create User, database role, application role, schema and audit specification for only one database and it runs within this database. Migrating of this database to other place only needs to backup and restore and you do not need to migrate users and other options with it.

You can create multiple types of users

Creating user with password to reside in local database

Assigning proper database rights, for admin user, it can only be access this database and perform all tasks.

Connecting to a contained database

You need to mention database name before connected to contained database

And you will only get your database. This is the best case for testing on production machine before deployment.

Resources: 

msdn  : http://msdn.microsoft.com/en-us/library/ff929071.aspx/css

SQLCoffee: http://www.sqlcoffee.com/SQLServer2012_0009.htm

Server Configuration Option (optimize for ad hoc workloads)

During exam preparation I was asked to answer a question that there are a lots of execution plans generated during querying a database and used only once. How can I reduce that and I got following answer

Server Configuration Option (optimize for ad hoc workloads).

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When option is enabled only regularly used queries are cached that are used more than once. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

Let's understand how it works. Run the following text to enable advance option

sp_CONFIGURE 'show advanced options',1RECONFIGUREGO

sp_CONFIGURE ‘optimize for ad hoc workloads’,1RECONFIGUREGO

Now run the following SQL 

/* Clean Cache and Buffers */DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERS
GO/* Run Adhoc Query two  Time */SELECT * FROM HumanResources.Shift
GO 5

/* Check if Adhoc query is cached.
It will return result with Adhoc Query ran two times*/SELECT usecounts, cacheobjtype, objtype, TEXT
FROM sys.dm_exec_cached_plansCROSS APPLY sys.dm_exec_sql_text(plan_handle)WHERE usecounts > 0 ANDTEXT LIKE '%[your sql]%'ORDER BY usecounts DESC;GO

Instead of show 5 different execution plan. It will comes up with only one plan and user count is 5. It means when this option is enabled same plan will execute again and for rarely used queries that are only used for one time will not be cached.

This option is very good for memory utilization. Microsoft mentioned that only advanced options should be changed only by an experienced database administrator or certified SQL Server technician.

SQL SERVER 2012 HIGH AVAILABILITY SOLUTION (Always On availability Group Configuration)

In my last post I have covered how to install Windows Cluster environment and Windows Server 2012. To review have a look at this link

1. SQL Server 2012 high availability solution (Windows Server Cluster Configuration)
2. SQL Server 2012 High availability solution ( SQL Server 2012 Installation)

SQL SERVER Always On availability Group Configuration

Once SQL Server installed on both nodes (SQL01, SQL02). For testing purpose I have created AlwaysOn_TestPSO database on primary node SQL01. Database must be created with full recovery mode

A SQL Server Availability Group enables you to specify a set of databases that you want to fail over together as a single entity. When an availability group fails over to a target instance or target server, all the databases in the group fail over also. Because SQL Server 2012 can host multiple availability groups on a single server, you can configure AlwaysOn to fail over to SQL Server instances on different servers. This reduces the need to have idle high performance standby servers to handle the full load of the primary server, which is one of the many benefits of using availability groups.

An availability group consists of the following components:

·         Replicas, which are a discrete set of user databases called availability databases that fail over together as a single unit. Every availability group supports one primary replica and up to four secondary replicas.

·         A specific instance of SQL Server to host each replica and to maintain a local copy of each database that belongs to the availability group.

Replicas and failover

The primary replica makes the availability databases available for read-write connections from clients and sends transaction log records for each primary database to every secondary replica. Each secondary replica applies transaction log records to its secondary databases. All replicas can run under asynchronous-commit mode, or up to three of them can run under synchronous-commit mode.

Before starting Always on availability group you need to enable this feature from SQL Server service. 

Now try to create availability group

Error still shows there is some problem. We need to restart sql server service.

Make sure you will perform this step on both nodes.

For testing purpose I have created a table and then take a full backup of this database. Place this backup on share folder \\DomainServer\ClusterShareFolder path

Start setup using Availability Group Wizard

Specify name of availability Group.

Primary instance is already there you need to add other replicas and all your replicas are from different nodes as in our case SQL02 is secondary node and act as replica. Check options and look at the last column Readable Secondary that is selected as [No]. If you select it as [Yes] then your secondary replicas and then 3rd and 4th replicas are in readable form as well. This approach can be used for reporting purposes

If port 5022 is enabled or firewall is blocked then this step will automatically fill are data

These options are for Backup references, by default [Prefer Secondary] is selected I will leave as is.

You need create listener either user DHCP or TCP. You can get this from your network person

You need to set DNS name, default port and IP address that is not using in domain for any other VM or server. Network person will help you in this. You need to remember

1. DNS name must not be any system or VM name in domain
2. Port must be enabled through firewall
3. IP Address must not in use

All validation steps are complete

Backup location is displayed in this screen. I have missed out its screen shot during installation wizard

Once setup is completed you can view primary database as Synchronized. Notice availability group objects

1. SQL_Group: is our availability group for this database. You can add more groups that use different databases to handle
2. Replicas: can be added more up to 4 replicas
3. Database: more than one database can be grouped here under SQL_Group and all follows combined point of failure i.e if one database fails all other will also moves to its next replica
4. Listener: This listener verifies database sync state and moves data between its objects