As you might know, I'm running my "SQL Server 2012 Deep Dive Days Workshop" from May 28 – 30 in Central London. You can find out further information about the detailed agenda here: http://www.SQLpassion.at/SQL2012_UK.pdf

Next week you have the amazing possibility to win a free seat in that workshop! It's very easy: Just register as the first person throughout next week (May 13 – May 18) for the workshop and get an additional seat for one of your colleagues for free!

Here are the exact eligibility requirements to get the free seat:

• You have to be the FIRST person who registers for my SQL Server 2012 Workshop in London, during the week from May 13 – May 18.
• You have to use the discount code TWO4ONE during the registration.
• You have to register the second person also through the discount code TWO4ONE.
• Both registrations must have the same invoice address.
• This offer is only available for persons/companies who haven't yet registered an attendee for the workshop.
• Everyone else who registers throughout the week from May 13 – May 18 with the discount code TWO4ONE, and has not won the free seat, receives a 10% discount of the original price of GBP 1,290.
• The 10% discount does not apply to the first person who wins the additional seat for the workshop.
• The registrations have to be done through the registration site available on http://www.SQLpassion.at/events.html

So let's be very fast, and register for my upcoming workshop in London, and if you are the first one, you will get an additional seat for free!

See you soon in London 

-Klaus

As I have announced announced in my sessions at the SQLConnections conferences in Berchtesgaden/Germany, you can find my session materials (slides & samples) here for download

• SQL Server Query Tuning
• SQL Server Memory Management
• SQL Server 2012 Distributed Replay

Thanks for attending my sessions J

-Klaus

As I have announced in my DevWeek and SQLbits sessions, you can find my session materials (slides & samples) here for download:

• Understanding SQL Server Execution Plans
• SQL Server Query Tuning
• Troubleshooting TempDb

Thanks for attending my sessions, it was really awesome speaking for such a large crowd of people, and I'm looking forward to come back very soon to London 

-Klaus

As I have announced in my session last Saturday in Dublin, you can find here the slides & samples for download.

Thanks for attending my session and enjoy the download J

-Klaus

In the last weeks and months I have prepared tons of SQL Server 2012 content for my own SQL Server 2012 Deep Dive Days workshop that I'm currently running across Europe. Because this content shows in a very good way how you can use all the various new features of SQL Server 2012, I have decided to share over the year some of that content through my weblog. Today I want to start with SQL Server 2012 AlwaysOn Availability Groups, because that's one of the hottest features in SQL Server 2012!

I want to take you from a zero knowledge level and show you step by step how you can configure, deploy, and manage a SQL Server 2012 AlwaysOn High Availability solution. In today's first part I want to talk about the basic architecture of AlwaysOn and how you can prepare SQL Server for running an AlwaysOn solution.

Overview

AlwaysOn Availability Groups is the big new feature in the High Availability stack of SQL Server 2012. Simply said it is the successor of Database Mirroring that was introduced with SQL Server 2005 SP1. Database Mirroring is a great technology for achieving HA (High Availability) and DR (Disaster Recovery), but has some weaknesses and limitations:

• You can only mirror 1 database through a Database Mirroring session. When you have an application that consists of several database (just think of SharePoint Server), then you need to have several distinct Mirroring sessions.
• The Failover is done on the Mirroring session level, which means you can only failover 1 database at a given time. Again – if you have more than 1 database, you need different Mirroring sessions and you have to coordinate a multi-database failover at your own.
• There is only 1 Mirroring partner possible. You can't mirror to different partners. The Mirroring partner can be run with Synchronous Commit for achieving High Availability or with Asynchronous Commit for achieving Disaster Recovery. If you are using Synchronous Commit you can get an Automatic Failover if you deploy a Witness instance. This witness instance is used for acquiring a quorum and avoiding a so-called Split-Brain scenario.
• The Mirroring database is always replaying Transaction Log records, which means that you are not able to access the database for read-only access (the undo part of Recovery has not yet run). The only possibility is to use Database Snapshots to get a consistent view of your database at a given point in time and refresh the Database Snapshot on a regularly basis, but again – you have to do this at your own.

Beginning with SQL Server 2012 Microsoft provides us now AlwaysOn Availability Groups which are the successor of Database Mirroring.

Side Note: Database Mirroring is still available in its original feature set, but it is marked as a Deprecated Feature, which means, that it will be removed in a future release of SQL Server.

AlwaysOn Availability Groups offers you the following advantages over traditional Database Mirroring:

• Multi-Database Failovers
• Multiple Secondaries
• Active Secondaries
• Integrated HA Management

The following figure gives you a basic overview about AlwaysOn Availability Groups.

As you can see from the previous figure, an AlwaysOn Availability Group is created between several standalone SQL Server instances. At least you need 2 different SQL Server instances. All instances that participating in an AlwaysOn Availability Group don't need any Shared Storage – each SQL Server instance has its own local storage. It's the same concept as with Database Mirroring. The only requirement is the fact that each participating SQL Server instance must be in the same Windows Domain, because you must create a Windows Server Failover Cluster (WSFC) that contains all participating SQL Server Instances. SQL Server uses underneath several WSFC features like the Quorum Model. Database Mirroring uses its own Quorum Model by using a dedicated Witness instance.

Side Note: In my opinion this one of the biggest disadvantages of AlwaysOn Availability Groups. I have a few customers which wanted to migrate from Database Mirroring to Availability Groups, but they can't go that direction at this time, because their SQL Server instances are not in the same domain. Some even have deployed SQL Server instances in separate workgroups… But that's the way how AlwaysOn Availability Groups are working, and that's the price that you have to pay for that new feature.

An Availability Group itself can contain several databases, which can failover in a whole set – that's a huge difference and improvement compared to Database Mirroring. Each participating SQL Server instance hosts a so-called Replica. There is always one Primary Replica and up to four Secondary Replicas. Up to 2 of these Secondary Replicas can use Synchronous Commit, and the other 2 Secondary Replicas can use Asynchronous Commit. When you run 2 Replicas with Synchronous Commit you can also configure an Automatic Failover between them. They are forming a so-called "Automatic Failover Pair".

Another big advantage of the Secondaries is that they are readable. You don't need a Database Snapshot anymore, as you would have done it with Database Mirroring. But you must be aware that there is an overhead when you are using Active Secondaries – but we will look into that topic in more detail at a later stage of this weblog series. You can also use Active Secondaries for doing backup jobs, which will be also a very nice way to move backup workload away from your Primary Replica.

As with Database Mirroring, Transaction Log records are transferred from one Replica to the others. To minimize network latency, SQL Server 2012 uses Build in Log Compression to transfer Transaction Log records as fast as possible.

Preparing your SQL Server for AlwaysOn

If you want to deploy an Availability Group solution you have to prepare your SQL Server instances in the first step. For this weblog posting I'm assuming that you have setup 5 different SQL Server installations across 5 physical servers/Virtual Machines. As an Operating System I'm assuming Windows Server 2008 R2 SP1 x64. My current Hyper-V based deployment looks like the following:

• Node1
  • DNS Name: ag-node1.sqlpassion.com
  • IP Address: 192.168.1.211
  • SQL Service Account: ag_node1_sqlsvc@sqlpassion.com
• Node 2
  • DNS Name: ag-node2.sqlpassion.com
  • IP Address: 192.168.1.212
  • SQL Service Account: ag_node2_sqlsvc@sqlpassion.com
• Node 3
  • DNS Name: ag-node3.sqlpassion.com
  • IP Address: 192.168.1.213
  • SQL Service Account: ag_node3_sqlsvc@sqlpassion.com
• Node 4
  • DNS Name: ag-node4.sqlpassion.com
  • IP Address: 192.168.1.214
  • SQL Service Account: ag_node4_sqlsvc@sqlpassion.com
• Node 5
  • DNS Name: ag-node5.sqlpassion.com
  • IP Address: 192.168.1.215
  • SQL Service Account: ag_node5_sqlsvc@sqlpassion.com

Each VM node is also part of my sqlpassion.com Windows domain, and I have performed a default SQL Server 2012 installation with a default instance of SQL Server 2012. Furthermore each SQL Server default instance uses a Windows Domain account for the SQL Server Service Account. You must also make sure during the installation that you place your MDF/LDF files on the same physical location on each VM node, otherwise you will run into troubles with the wizards provided by AlwaysOn Availability Groups.

The first step that you have to perform is the creation of a new Windows Server Failover Cluster (WSFC). In the first step this sounds like a huge step, but it is very easy to do because you don't need any shared storage. One prerequisite of WSFC is the fact that you have to install the Failover Cluster Feature on each node. You can perform this task through the Server Manager of Windows Server 2008 R2.

After you have installed the Failover Clustering on EACH node, you have to log into one of these nodes through a Windows Domain user account. Otherwise you are not able to create the Windows Server Failover Cluster itself. To create the cluster itself you can use the Failover Cluster Manager, which can be found in the Administrative Tools of your Control Panel. On the right hand side you can see all available Actions, and here you choose the option "Create a Cluster…". In the first step the wizard asks you which nodes should be part of the new WSFC Cluster. In my case I have selected all 5 VM nodes:

On the next wizard page you have to choose to run the Cluster Configuration Validation Tests. If you want to get support from Microsoft, you have to run them. For AlwaysOn Availability Groups you don't have to include the tests regarding shared storage, because you are not using any form of shared storage for an Availability Group. After you have run your Cluster Configuration Validation Tests, you have to give your WSFC Cluster a name and an IP address:

With all these information in the hand, the wizard will finally create your WSFC cluster. After the successful creation you can see your new WSFC cluster in the Failover Cluster Manager:

As you can see from the previous figure, the Cluster is currently not hosting any Service or an Application yet. We just have created the skeleton so that an AlwaysOn Availability Group can be created and deployed. In the final step you have now to enable AlwaysOn Availability Groups on each SQL Server instance. This can be done through the SQL Server Configuration Manager and the tab AlwaysOn High Availability. You have to enable the checkbox "Enable AlwaysOn Availability Groups". This option is only available when the computer where the SQL Server instance is running on, is part of a WSFC cluster:

After you have enabled that option, you must restart your SQL Server instance. Please make sure to do this configuration change on EACH node that is part of your WSFC cluster.

By now we have created the whole infrastructure that is necessary to run a SQL Server 2012 AlwaysOn Availability Group solution. In the next weblog posting you will then see, how you can deploy your first Availability Group with SQL Server 2012.

If you are interested in SQL Server 2012, I suggest looking on my "SQL Server 2012 Deep Dive Days Workshop", which I run from May 28 – 30 in London/United Kingdom (see http://www.SQLpassion.at/events.html, Early Bird price ends on March 31). This workshop was already running in Vienna/Austria and Munich/Germany with a huge success.

Stay tuned J

-Klaus

As I have announced in my ColumnStore presentation last Saturday in Lisbon, you can find here the slides & samples for download.

Thanks for attending my session and enjoy the download J.

-Klaus

As you might know, Microsoft has released on March 7 the RTM version of SQL Server 2012. If you are an MSDN or TechNet Subscriber, you can already download it and play with it. As with every SQL Server release, SQL Server has also marked several features as deprecated, which means they are removed in a future version of SQL Server.

So if you are starting a new deployment or an application based on SQL Server 2012, you should check the following link which describes all the deprecated features and their replacement: http://msdn.microsoft.com/en-us/library/ms143729%28v=sql.110%29.aspx. In this blog posting I want to mention 2 deprecated features, because there are some big changes in SQL Server 2012.

Database Mirroring:

With the release of SQL Server 2012 and AlwaysOn Availability Groups, Database Mirroring is deprecated. Database Mirroring was introduced with SQL Server 2005 SP1 and allowed you to mirror a database synchronous and asynchronous, and optionally you had also an automatic failover if you used a witness instance (in combination with synchronous mirroring).

AlwaysOn Availability Groups is an enhanced version of Database Mirroring where you can mirror a group of databases, and where your secondary databases are readable. AlwaysOn also provides you synchronous mirroring for HA, and asynchronous mirroring for DR. One requirement of AlwaysOn Availability Groups is that you need to have a Windows Server Failover Cluster (without any Shared Storage!). The Windows Server Failover Clustering functionality is used to provide several clustering infrastructure features like Quorum settings, Intra Node Health Checking, etc.

I already talked with some of my customers, and for a few of them this is currently a big no-go, because they are currently using Database Mirroring where each partner database is not in an AD domain. So they can't just migrate to AlwaysOn, because they are not able to create a Windows Server Failover Cluster. But that is just the way how it works now. Another drawback is that AlwaysOn Availability Groups is currently only an Enterprise Edition feature of SQL Server. Parts of Database Mirroring are also available in the Standard Edition. Microsoft suggests to use Log Shipping of your SQL Server Edition doesn't support AlwaysOn Availability Groups.

SQL Server Profiler for Trace Capture and Trace Replay

Microsoft introduced with SQL Server 2008 Extended Events. With SQL Server 2012 Microsoft has done a lot of improvements in the area of Extended Events, for example you are now getting a very cool UI where you can define your Event Sessions in a wizard based way. On the long term Extended Events is the replacement for SQL Trace and SQL Server Profiler. Therefore those features are marked as deprecated. If you are using SQL Trace/SQL Profiler for Trace Capture and Trace Replay, SQL Server 2012 provides you a new feature called Distributed Replay, which is a high scalable distributed replaying technology.

So, if you have existing applications based on SQL Server, or you are starting a new deployment on SQL Server 2012, please review the deprecated features so that you have fewer problems in the futures.

If you are interested in SQL Server 2012, I suggest looking on my "SQL Server 2012 Deep Dive Days Workshop", which I run from May 28 – 30 in London/United Kingdom (see http://www.SQLpassion.at/events.html, Early Bird price ends on March 31). This workshop was running this week in Vienna/Austria with a huge success and next week I'm presenting this workshop in Munich/Germany.

Thanks for reading!

-Klaus

Today I want to talk about Statistics Enhancements in SQL Server 2012. As you already know SQL Server uses Statistic Objects in a database to estimate the cardinality of queries. The cardinality estimation is the input for the generation of the physical Execution Plan of a given T-SQL query. Data changes over time, data distribution changes over time, so SQL Server is also able to change Statistics Objects in the background automatically for us. This feature is called Auto Update Statistics. SQL Server updates Statistics automatically under the following conditions (when the table is not a temp table):

• A table with no rows gets a row
• A table has fewer than 500 rows and is increased by 500 or more rows
• A table has more than 500 rows and is increased by 500 rows + 20 percent of the number of rows

Whenever SQL Server is updating the statistics automatically, SQL Server Profiler reports the Auto Stats event. On the other hand, SQL Server is also able to create Statistic Objects on the fly, when SQL Server needs to estimate the cardinality for a given query and no index (and therefore no Statistics Object) is available, like in the following one:

SELECT * FROM Table1

WHERE Column1 > 1

GO

When Column1 is not indexed, SQL Server will create a new Statistics Object for you.

All these things are great to improve performance for queries, but what happens when SQL Server can't change the underlying Statistics Object or isn't able to create a new one for you? Just think of databases that are marked as read only, or when you are using Database Snapshots. Data in Database Snapshots can't change, but you can issue completely new queries that leads to the demand of creating new Statistics Objects.

In that case, SQL Server can't create/update the Statistics Object, which means SQL Server can't re-evaluate the cardinality estimation and proceeds with the cached execution plan, which can have a huge performance drawback for your read only workload. Let's demonstrate this problem with a simple example. I'm creating a new database with a simple table in it:

USE master

GO

CREATE DATABASE StatisticsDatabase

GO

USE StatisticsDatabase

GO

CREATE TABLE Table1

(

Column1 INTIDENTITY,

Column2 INT

)

GO

In the next step I'm populating the table with 1500 records:

SELECT TOP 1500 IDENTITY(INT, 1, 1) AS n INTO #Nums

FROM

master.dbo.syscolumns sc1

INSERT INTO Table1(Column2)

SELECT n FROM #nums

DROP TABLE #nums

GO

When we select everything from the table, SQL Server uses the Table Scan operator, because we have not defined a Clustered Index on our table:

SELECT * FROM Table1

GO

To demonstrate the problem with errors in the cardinality estimation I'm creating a Non-Clustered Index on Column2.

CREATE NONCLUSTERED INDEX idxTable1_Column2 ON Table1(Column2)

GO

The following query uses the previous created Non-Clustered Index:

SELECT * FROM Table1

WHERE Column2 = 2

GO

SQL Server creates an Execution Plan with a Non-Clustered Index Seek and RID Lookup (Heap) operator – the traditional Bookmark Lookup operator. By now we have a table with 1500 records, which means we need 20 percent + 500 rows of data changes, until SQL Server will update the underlying Statistics Object of the Non-Clustered Index. In our case these are 800 data changes in our table (300 + 500). So let's insert 800 additional records into the table:

SELECT TOP 800 IDENTITY(INT, 1, 1) AS n INTO #Nums

FROM

master.dbo.syscolumns sc1

INSERT INTO Table1(Column2)

SELECT 2 FROM #nums

DROP TABLE #nums

GO

SQL Server will update the Statistics Object as soon as a new SELECT query will reference it. Prior to that we will now mark our database as read only, so that SQL Server can't change anything anymore:

USE master

GO

-- Mark the database as readonly

ALTER DATABASE StatisticsDatabase

SET READ_ONLY WITH NO_WAIT

GO

USE StatisticsDatabase

GO

Let's now run the query which will trigger an update of the Statistics Object:

SELECT * FROM Table1

WHERE Column2 = 2

GO

Under normal conditions, SQL Server will now re-evaluate the cardinality estimation and create an Execution Plan with a Table Scan operator. But in our case SQL Server can't change anything in the database, therefore SQL Server will reuse the inefficient cached Execution Plan with the Bookmark Lookup! When you look at the Execution Plan you will also see a huge difference between the Estimated and Actual Number of Rows:

That's the behavior of SQL Server 2005/2008 (R2), and you can't change anything about it. The same problem will occur when you are using Database Snapshots.

But with SQL Server 2012 Microsoft has addressed that problem. SQL Server now supports so-called Temporary Statistic Objects which are created in TempDb. Every time when SQL Server wants to create or update an Statistics Object on a read only database or on a Database Snapshot, SQL Server will create the necessary Statistics Object in TempDb. For this functionality SQL Server needs 1 data page per each Statistics Object in TempDb.

So when you run the previous code on SQL Server 2012, SQL Server will create a new temporary Statistics Object in TempDb, and will create an Execution Plan with a Table Scan operator, which makes much more sense, because our query is not selective enough anymore.

Temporary Statistics Objects are also used by Readable Secondaries in AlwaysOn in SQL Server 2012, because Readable Secondaries also have the same problem: they are used for read only workloads, and SQL Server can't change anything in the Secondary Replica, because all Replicas must have the same physical structure in the Availability Group. So every time when you are working with readable Secondaries in SQL Server 2012, you will also have an overhead in TempDb, because of temporary Statistics. And Readable Secondaries are also using transparently Snapshot Isolation, which also impacts the performance of TempDb. But that's a topic for another weblog posting J.

Thanks for reading!

-Klaus

In the last weeks it was very silent on my weblog, but life is pretty fast in 2012. Currently I'm preparing tons of content about SQL Server 2012 for my upcoming event series across Europe. Besides that (which is very time consuming), I'm also happy to get the chance to speak at various SQL Server conferences across Europe within the next weeks. Here's an overview where you can meet me in the near future:

• March 5 – 7, Vienna/Austria:
  I'm doing my own "SQL Server 2012 Deep Dive Days" with brand new in-deep technical content about SQL Server 2012.
• March 12 – 14, Munich/Germany:
  "SQL Server 2012 Deep Dive Days" in Munich/Germany
• March 17, SQLSaturday #115 in Lisbon/Portugal
  I'm speaking about Columnstore Indexes in SQL Server 2012
• March 19 – 21, Zurich/Switzerland
  "SQL Server 2012 Deep Dive Days" in Zurich/Switzerland
• March 24, SQLSaturday #105 in Dublin/Ireland
  I'm speaking about Advanced SQL Server Troubleshooting
• March 28 – 29, London/United Kingdom
  I'm speaking at the DevWeek conference about SQL Server Execution Plans and SQL Server Query Tuning
• March 30 – 31, London/United Kingdom
  I'm speaking at the SQLbits conference about TempDb Troubleshooting
• April 18 – 20, Billund/Denmark
  I'm speaking at Miracle Open World about SQL Server Execution Plans and SQL Server Query Tuning
• May 8 – 9, Berchtesgaden/Germany
  I'm speaking at the SQLConnections conference in Berchtesgaden/Germany, and my session is not yet decided J

There are also 2 – 3 additional conferences, where I have submitted proposals, but didn't have yet received an acknowledgement…

My flight schedule over the next weeks looks also very interesting:

1. March 11: Vienna- Munich
2. March 14: Munich – Vienna
3. March 16: Graz – Frankfurt
4. March 17: Frankfurt – Lisbon
5. March 18: Lisbon – Frankfurt – Vienna – Zurich
6. March 21: Zurich – Vienna
7. March 23: Vienna – Zurich – Dublin
8. March 25: Dublin – Zurich – Vienna
9. March 28: Vienna – London
10. March 31: London – Vienna
11. April 18: Vienna – Frankfurt – Billund
12. April 20: Billund – Frankfurt – Vienna

It seems that I'm spending more time in airplanes than at home…

See you soon J

-Klaus