My upcoming speaking schedule

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

The summer goes by and in a few weeks the conference season starts over again. The last weeks were a little bit stressful, but now I had finally some time to prepare my sessions for upcoming SQL Server conferences, where I’m speaking over the next months.

24 Hours of PASS, September 12

At this online event ( I’m doing one session titled “Fast your Seatbelt – Troubleshooting the hardest SQL Server Problems“. I’m talking here about things like Thread Pool Starvation, Spinlock Contention, and VMware Memory Balloon Driver issues – expect to be this a Level 400 session.

SQLdays, September 20 – 21, Rosenheim/Germany

At this German speaking conference ( I’m doing 3 sessions about various SQL Server topics:

  • Troubleshooting SQL Server with Extended Events
  • Fast your Seatbelt – Troubleshooting the hardest SQL Server Problems
  • Migrating to SQL Server 2012 AlwaysOn

PASS SQLRally Nordic, October 1 – 3, Copenhagen/Denmark

It’s my first time that I’m speaking at a SQLRally event (, and I’m really looking forward going to Denmark – I’ve never been there! In Copenhagen I’m presenting one session about Troubleshooting SQL Server with Extended Events.

SQLPASS Summit, November 6 – 9, Seattle/USA

It’s now my 6th SQLPASS Summit where I’m presenting (2006, 2007, 2008, 2010, 2011, 2012) and I’m really proud to be able to do a day long precon about Practical SQL Server Performance Troubleshooting. In this precon I’m using a TPC-E workload to generate an initial baseline for performance improvements, and over the day I’m showing various techniques to improve SQL Server performance. But read yourself through the precon abstract:

Learn how to configure the Windows OS, SQL Server, and your database for optimal performance. In this pre-conference workshop, we’ll start by taking a default installation of SQL Server and running an OLTP workload with several hundred users to generate our initial baseline for performance tuning and troubleshooting. Throughout the day, we’ll work with various areas of SQL Server to implement different performance optimizations and then see how those changes impact the throughput of our initial test workload. At the end of the day, we’ll have a well-performing SQL Server that can handle a much larger workload than the initial (default) installation.

Topics for the day include:
• Windows OS settings
• Storage configuration
• SQL Server instance settings
• Database settings
• Index and statistics maintenance
• Locking, blocking, and deadlocking
• Memory management

Besides the precon I’m presenting the following 2 regular conference sessions:

  • Advanced SQL Server Troubleshooting
  • ColumnStore Indexes – the Turbobooster in SQL Server 2012

Besides all this conference engagements, I’m also running my “SQL Server Performance Tuning & Troubleshooting Workshop” in Austria, Germany, Switzerland, and Ireland! See for further details. You can also expect some new SQL Server Quickies over the next weeks at my YouTube channel: Autumn 2012 will be a very busy for me (the same as last year), and I’m looking forward to see you at one of these great SQL Server events!

Thanks for reading


Do you like this post? Why not share it with the SQL Server community?

Initialize a Transactional Replication from a Database Backup

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

During the last days I’m working on Replication – especially with Transactional Replication. The main idea of Transactional Replication is that you have a so-called Log Reader Agent on your Publisher SQL Server database running, that is analyzing the Transaction Log File and synchronize changes on so-called Articles through a Distribution database down to your Subscription databases.

When you setup your Transactional Replication through SQL Server Management Studio, you can only initialize the Subscription database through a so-called Snapshot. A snapshot is just a point-in-time “picture” of your database, which is dumped out to a file share through bcp.exe. Imagine now that you want to publish parts of a VLDB (Very Large Database) to various Subscribers. In that case Transactional Replication must make a snapshot on the Articles that you want to replicate from your VLDB. That’s not very practical, because it can take a very long time to make that snapshot and initialize the Subscribers from that snapshot.

Fortunately there is also an option to initialize a Subscriber from a backup file, but unfortunately this option isn’t available through SQL Server Management Studio – you have to choose that option through the Replication Stored Procedures that are used in the background by SQL Server to implement the actual Replication. The idea of initializing a Transactional Replication from a backup file is not very new, because there are also other blog postings on this topic, but I wasn’t able to find a step-by-step tutorial how to implement this approach. The most important blog posting is from Chris Skorlinski ( With the information provided in that posting I was finally able to do the initialization from a backup file. In this blog posting I want to show you now step-by-step how you can achieve that functionality.

In my distributed scenario Transactional Replication is running on 3 different VMs:

  • SQL2008HADR1: Publisher
  • SQL2008HADR2: Distributor
  • SQL2008HADR3: Subscriber

Running Replication in a fully distributed scenario is more realistic, but the setup of the needed security is also a topic on its own. Fortunately Joe Sack from has written a few weeks ago a very nice article how to setup security – see for further details on that.

To get started I have created a completely new database on SQL2008HADR1 which acts as the Publisher for Transactional Replication:

As you can see from the code, I have created a simple table called Foo, and inserted several records into that table. I have also created a full database backup and afterwards I have inserted several other records, which are currently stored in no backup. In the next step you have to create your Publication on the Publisher. This can be done completely through the UI provided by SQL Server Management Studio. The only thing that you don’t have to do is the creation of an initial snapshot, because we don’t need that snapshot.

In my case I have scripted out the creation of the Publication, which resulted in the following T-SQL code:

In that publication I’m just replicating the whole table Foo. I have not applied any filter. After you have created your Publication, you must change one setting through the UI of SQL Server Management Studio. I was not able to figure it out, if this change can be also done through T-SQL. You have to allow the initialization from backup files for subscribers. To change that setting you are doing to the local publication on the Publisher, then you go to the Properties-Window, and finally to the page “Subscription Options“. And here you are setting the option “Allow initialization from backup files” to “True“.

This setting allows the Subscribers to be initialized by a backup. In the next step I’m making a final Transaction Log Backup, so that all transactions are backed up.

Note: You need to do a backup after the Publication was configured on the Publisher. Otherwise the initialization from backup will not work!

When you are using Replication in a distributed scenario you also have to make sure that the Distribution Agent can access your Publisher database.

In the next step you can restore the taken database backups on the subscriber, in my case SQL2008HADR3.

Now you are ready to create the Subscription. When you create a new Subscription you have to make changes on both the Publisher and also on the Subscriber. Therefore you have to execute the following T-SQL code on the Publisher (SQL2008HADR1) to create the Subscription.

When you are calling the stored procedure sp_addsubscription on the Publisher you can now specify that you want to initialize the Subscribers through a database backup. For that reason you set the parameter @sync_type to “initialize with backup“. You also have to supply the *last* backup that you have restored on the Subscriber through the parameter @backupdevicename. In my case this is the Transaction Log backup that I have done previously. This backup is then opened by sp_addsubscription to get the last LSN (Log Sequence Number) of the backup. This LSN is used by the Distribution Agent to replicate commands from the msrepl_transactions table to the Subscriber that have occurred afterwards.

Note: You have to call sp_addsubscription directly from T-SQL, because setting these options is not possible through SQL Server Management Studio.

When you have created the Subscription on the Publisher, you are finally ready to create it on the Subscriber (SQL2008HADR3).

After completing these steps you have a fully working Transactional Replication which was initialized by a database backup instead of a snapshot. For VLDBs this can be a huge advantage instead of creating a very big snapshot that must be transferred over the network to the Subscriber, and finally applied. The drawback of this approach is that you have to restore your whole database on the Subscriber, and not only the Articles that you are publishing. Additionally you also have the whole data on the Subscriber, even when you are replicating Articles with Filter. For these reasons you have to delete the unnecessary data on the Subscriber afterwards manually.

Thanks for reading!


Do you like this post? Why not share it with the SQL Server community?

SQL Server Quickie #2 – Extents

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Yesterday I have uploaded my 2nd SQL Server Quickie to YouTube. In this episode I’m talking about Extents, and how SQL Server manages those through GAM and SGAM pages.

Thanks for reading & watching!


Do you like this post? Why not share it with the SQL Server community?

SQL Server Quickies – a new way to learn about SQL Server

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

We are all living in a fast moving world, especially in the IT industry. We have to learn new technologies every few months and must keep our knowledge up-2-date to be competitive. A lot of us are learning through reading, and a lot of us are also learning through watching web casts and training videos. I’m also a big fan of the later one, because it is a completely new way to learn.

Unfortunately web casts and training videos always lasts for at least an hour, and you are just staring at prepared Power Points, while listening to your trainer in the background. And sometimes it’s just impossible to watch web casts, because you just have a few minutes, but not a whole hour – like when you are on the road and waiting for a train. For that reason a new idea was born: SQL Server Quickies!

The goal of my new SQL Server Quickies video series is that you can learn about specific advanced SQL Server topics within 5 – 10 minutes by listening to me working hard on the flipchart explaining various SQL Server topics. And by the end we are digging into SQL Server Management Studio exploring the illustrated concepts with a practical example.

Today I’m very proud to announce my first SQL Server Quickie about Data Pages in SQL Server and how you can analyze them within SQL Server Management Studio. My goal is to deliver around 2 – 3 SQL Server Quickies per each month about various more advanced SQL Server topics – so stay tuned and enjoy the new learning experience. You can find all posted SQL Server Quickies at my YouTube Channel at

Thanks for reading & watching!


Do you like this post? Why not share it with the SQL Server community?