Speaking at SQLKonferenz, Darmstadt

sqlkonferenz_banner

Jump to English Version

Ich spreche auf das größte SQL Server Konferenz im deutschsprachigem Raum, SQL Konferenz in Darmstadt in Februar!

Obwohl ich seit 13 Jahren in Deutschland lebe ist das nur das zweite Mal, dass ich als Sprecher in Deutschland auftrete. Ich kann zwar Deutsch (fast) fließend sprechen, bin dennoch mehr aufgeregt als wenn ich auf Englisch vortragen muss.

Meine Session heißt “Real World SQL 2012 -> SQL 2014: Migration einer AlwaysOn FCI/AAG Umgebung” und soll anhand eines Kundenprojektes, die tatsächlichen Überlegungen, Schritte und Stolpersteine aufführen, die zu einer Systemmigration gehören.

SQL Konferenz ist ein Konferenz über zwei Tage mit einem “Pre-Conference” Tag davor, wo einzelnen Themen etwas tiefer erläutert werden können. Schaut einfach in die Agenda rein um alle Sessions zu sehen. Es sind wirklich erfahrene und sehr gute Deutsche wie auch internationale Sprecher angemeldet.

Zudem, gibt es ein “Early Bird” Rabatt. Damit kann man €150 sparen wenn man sich bis Heiligabend anmgeldet, also schenkt euch ein frühes Weihnachtsgeschenk und registriert euch heute!

Wir sehen uns dann in Darmstadt in Februar :)


English Version

I have been chosen to speak at the largest SQL Server conference in the German speaking world, SQL Konferenz!

Although I have been in Germany for 13 years now, this is only the second time that I have had the opportunity to speak at a conference here. It is also the first time that I have been listed to speak in German (eek)!! I am fluent in German, using it on a daily basis, but this is still making me more nervous than speaking in English.

I will be presenting a new session this time, based on a real customer implementation of a system migration/upgrade from SQL 2012 to SQL 2014, including the addition of AlwaysOn Availability Groups. I thought it would be nice to offer a look at how a real-world project of this kind has actually been done, rather than sticking to the theory.

SQL Konferenz is a two-day conference with an additional pre-conference day with deep-dive, day-long sessions on a few different subjects. Check out the agenda to see what sessions are on offer – there are 40 sessions on the two conference days, with plenty of extremely good national and international speakers.

Also, there is an “Early Bird” offer available which can save you €150 on the standard registration price. This offer is only available until Christmas Eve, so go and treat yourself to an early Christmas present and register now!

So if you are planning your training for next year and would like to visit Germany, combine the two and come and visit SQL Konferenz.

Wir sehen uns dort :)

Speaking at SQLSaturday 356 Slovenia

I have been selected to speak at one of my favourite SQLSaturdays – in Ljubljana. I will be presenting my session “Replication: What, How, Why” which I have presented before at a few other events.

As I understand it, the event is actually full up. This means two things:

  1. You are registered and will be attending: Yay for you, please make sure you do attend. If you can’t attend, please make sure to de-register so that other people may have the chance to attend instead.
  2. You are not registered and would like to come: Put your name on the waiting list and hope that you can attend.

I’m really looking forward to coming to Ljubljana again, it is one of my favourite cities and is especially beautiful in the winter.

If you can make it to SQLSaturday Slovenia, come and say “Hi”. I’ll only be on a flying visit, landing in Slovenia on Saturday and leaving again on Sunday, but I intend to enjoy myself for the brief time I’m there.

See you in 9 days!

Speaking at SQL Server Days 2014 in Belgium

I’m Speaking at SQL Server Days 2014 in Belgium

Back in the spring I met two Belgian guys when I spoke at SQL Saturday Exeter, those guys were Pieter Vanhove ( b | t ) and Steve Verschaeve ( b | t ) and they told me about a conference that they are involved in: SQL Server Days

This annual conference is one of the larger SQL Server conferences in Europe, with over 300 attendees and has a whole host of world class speakers providing full day pre-con sessions and the standard 60 minute sessions.

Over a beer (or three) Pieter and Steve convinced me that this conference is one of the “must attend” conferences, so when their call for speakers was announced I submitted a couple of sessions.

A few weeks ago, the first (pre-con) speakers were announced. I saw the names and realised that Pieter and Steve meant business – the pre-con line-up is extremely good. Some of the biggest names in the SQL Server community are in attendance and there are subjects to account for anyone’s taste.

After seeing the pre-con speaker names I wasn’t going to hold my breath on being chosen to speak. However, last night I got the confirmation that my session “Consolidation – Resistence is Futile” has made the list!

I look forward to visiting Belgium for the first time (I’ve driven through Belgium often enough, but never stopped) and have been promised that Belgian beer is the best in the world…… we’ll see……

So if you are thinking of attending, make sure to book soon, The early bird offer (expires 15.07) will give you a discount and the site says that the first 100 registrations are eligible for a buy two get one free offer.

So don’t wait, get registered now and I’ll see you there at the end of September.

SQLHangout 21 with Boris Hristov – SQL Community: Why Bother?

SQLHangout 21 with Boris Hristov – SQL Community: Why Bother?

I met up with Boris Hristov ( b | t ) online again last night to talk with him on his regular webcast SQLHangouts.  We talked for about half an hour about the topic “SQL Community: Why Bother?”

This was the second time that I have spoken with Boris in the SQLHangout format after our first talk back in January and I really enjoyed it again.  We chatted a bit about how getting involved in the community is really easy and also really important.  You can get personal gratification from helping people out (it really feels good to help solve problems), but you are also investing in your career at the same time.

One point that I tried to squeeze in at the end of the talk was involvement in the community at a non-technical level.  This is an area that is full of “unsung heroes” and I always try and make a point of at least saying thanks to the group of people who do all the work behind the scenes.  Booking locations, arranging schedules, liaising with speakers, cleaning/setting up rooms etc. These people do a lot of work that just doesn’t get seen.  Having helped out as a non-speaking volunteer with a couple of events, I know that there is a *lot* of leg-work involved in making an event run smoothly.  So even though I only give them a small shout out, I really appreciate their commitment and support and if you don’t know how to get involved in your part of the SQL (or any) community, start out with volunteering.  There is always something you can do, and it is a very nice starting point.

Thanks to everyone that takes part in the SQL Community, it is a really strong group of people and gets stronger each day.

My call to action here is: Get involved, go and speak with your local UG and see how you can help. Register for your nearest SQL Saturday and tick the box as a volunteer and offer to do anything you can think of to help them out. Submit sessions to your UG or a SQL Saturday, then present – it is a really fun and exciting experience and can lead to greater things. Whatever you decide on doing, just get involved!

Thanks again to Boris for inviting me to his webcast, I’m more than happy to join you again in the future.

So, enough of my waffling on! Here is the recording of SQLHangout 21:

 

SQL Server 2012 SP2 – Making SIMPLE recovery simple again

Yesterday morning I blogged about how SQL Server 2012 RTM and SP1 are both flawed with regards to the model database and SIMPLE recovery model usage.  The error is fixed in a Critical Update, but many customers cannot/do not want to install CUs as they are less tested than Service Packs.

In that post I mentioned that SQL Server 2012 SP2 would include the fix for the issue I mentioned, but there were no details on when it would be released.

It seems Microsoft read my blog, because they released SQL Server 2012 SP2 late last night/early this morning!

This rolls up an entire set of CUs that have been released since the last SP in November 2012. So go out, download the SP and install it on test machines and then, after testing, onto your production boxes.

SQL Server Simple Recovery Model Requires Log Backups

“SQL Server Simple Recovery Model never requires a log backup!!!” I hear you say. Well sit back, relax and let me show you that it does…… sometimes…… although it shouldn’t!

Issue Description

I ran into a confusing situation yesterday which has a published solution, but I had never heard about it. It is one of those things that should stick in your mind considering how much havoc this may cause.

I am talking about what happens if you setup your model database so that future new databases are created in a way you want. If you stray from the SQL Server defaults, then this bug might catch you out. The issue I am talking about is the recovery model of the model database. By default SQL Server has a model database set to FULL recovery and with (in my opinion) vastly outdated initial size and auto-growth settings.

My department’s policy is to set the model database to SIMPLE and to set initial size and auto-growth to something more sensible than 1MB and 10% (for argument’s sake 128MB initial size and 128MB growth).

With SQL Server 2012 (RTM and SP1), setting the model to SIMPLE looks fine on the outside, but under the covers that is not what happens. A new database will be created using model, setting the new DB to SIMPLE recovery and you won’t immediately notice any issues. However, as soon as you start running DML you can see that something isn’t quite right by interrogating sys.databases and looking at the log_reuse_desc column for the database.

You can tag along for the ride by running the following code on a TEST!!! 2012 RTM or SP1 instance

SET NOCOUNT ON
GO

USE master
GO

IF EXISTS (SELECT * FROM sys.databases AS D WHERE name ='MyTestDB')
 DROP DATABASE MyTestDB
GO

ALTER DATABASE model SET RECOVERY SIMPLE
GO

-- Create a test database using defaults from model
CREATE DATABASE MyTestDB
GO

-- Verify that the database is in SIMPLE recovery model and log reuse is 'NOTHING'
SELECT name,
 recovery_model_desc,
 log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyTestDB';

-- Create a table in the test db and add some data (creating log file entries)
USE MyTestDB
GO
CREATE TABLE dbo.TestTable
 (Col1 int NOT NULL IDENTITY (1,1),
 Col2 char(256) NOT NULL
 DEFAULT REPLICATE('a', 256))
GO

INSERT INTO dbo.TestTable
 (Col2)
VALUES (default)
GO 2000

-- Now look at the log reuse information again - all is fine......
SELECT name,
 recovery_model_desc,
 log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyTestDB';
GO

-- Perform a backup of the database, because that is what we DBAs do regularly (here to nul so we don't fill up disk space)
BACKUP DATABASE MyTestDB TO DISK = 'nul'
GO

-- Add yet more test data
INSERT INTO dbo.TestTable
 (Col2)
VALUES (default)
GO 2000

-- Now look at the log reuse information again - oh look..... apparently we need a log backup!!!
SELECT name,
 recovery_model_desc,
 log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyTestDB';
GO

As you can see from running that code block, the log reuse description is now ‘LOG_BACKUP’ which is not possible for a SIMPLE recovery database! In fact, you can run a log backup if you like too, it will run successfully but won’t clear the log out for re-use!! So if you don’t do something about this, you will either fill your log file (if it isn’t set to auto-grow), or you will fill your disk drive where your log file resides. How fast this will happen depends solely on what activity is going on in that particular database. Your monitoring processes for drive space will warn you, but if the growth is fast enough, even these early warning systems may not be enough.

Solutions to the problem

As I mentioned at the beginning of this post, there is a solution to the problem (or rather, there are multiple solutions).

  1. Don’t set model to SIMPLE in the first place, making sure it is a step you make after creating databases that don’t require FULL recovery.
  2. Continue with model set to SIMPLE, but incorporate a process that “fixes” your databases after creation.
  3. Install at least CU4 for SQL Server 2012 SP1 or CU7 for SQL Server 2012 RTM both of which incorporate the bug-fix for this problem (along with a whole host of other fixes).

The steps required to follow option 2 would be:

  • Set the affected database to FULL recovery model
  • Perform a FULL backup of the database
  • Set the database back to SIMPLE recovery model

Here is the code to achieve that and also prove the title of this blog post:

-- Now look at the log reuse information again - oh look..... apparently we need a log backup!!!
SELECT name,
 recovery_model_desc,
 log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyTestDB';
GO

-- Perform the "required" log backup
BACKUP LOG MyTestDB TO DISK = 'nul'

-- The log has been cleared...... So we have FULL recovery in disguise
SELECT name,
 recovery_model_desc,
 log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyTestDB';
GO

-- How about we fix this permanently. Change to FULL, run a backup, change back to SIMPLE

ALTER DATABASE MyTestDB SET RECOVERY FULL
GO

BACKUP DATABASE MyTestDB TO DISK='nul'
GO

ALTER DATABASE MyTestDB SET RECOVERY SIMPLE
GO

-- Now fill our table with more data and check the log reuse column again
INSERT INTO dbo.TestTable
 (Col2)
VALUES (default)
GO 2000

SELECT name,
 recovery_model_desc,
 log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyTestDB';
GO

As for option 3: I realise that using RTM is something people just shouldn’t be doing, but the fact that this bug is in SP1 is a nasty one. Also, not everyone will install Cumulative Updates (CUs) because they are not as rigorously tested as Service Packs (SPs). I know of companies that have policies strictly forbidding installing CUs on production machines for this reason alone. This makes it all the more serious that it is a bug that managed to get through to SP1. Obviously, as soon as SP2 for SQL 2012 comes out (but when will that be?) this bug-fix will be available, but until that time, you have to live with CUs or other workarounds.

I have to say again, I am surprised I hadn’t heard about this issue before – it flew under my radar or was just not picked up by the “normal news channels”. Either way, I thought I’d throw this out there to help the google juices for anyone who has similar issues.

And so, that is how the SIMPLE recovery model sometimes does need a log backup……… sort of.

Have fun :)

SQL Saturday Dublin 2014 – I’m speaking!

I have been fortunate enough to have been chosen to speak at SQL Saturday Dublin. I am especially excited about this one for two reasons:

  1. I will be delivering a different topic to my other speaking engagements of late, although not too far from the usual.  This time my session will be “Replication Troubleshooting and Monitoring“. This is a topic that I wrestle with on a regular basis and know how difficult it can be when you are staring at seemingly useless error messages.  I hope to be able to show some common errors that I have encountered and how to go about solving them.

  2. I will be returning to Dublin! I spent an excellent weekend in Dublin in Decemeber 2013, where I got the chance to take part in a mini-conference at the Dublin SQL Server User Group along with André Kamman ( t | b ) and Mladen Prajdić ( t | b ). Going by that weekend, SQL Sat 310 should be one hell of a time.

So, now I have to get back to making these demos work. I’ll see you in Dublin on the 20th of September!