MVP today

homerToday I became a Microsoft Data Platform MVP for the very first time. Whilst I am delighted to receive the award and immensely grateful to Microsoft for awarding it to me, I won’t pretend that this journey has been easy or expected (which I wrote about here). Some of you will also (by now) be aware of personal problems that my family and I have been dealing with for the last 2 and a half years which I also wrote about in Dear #sqlfamily, so you will appreciate that this news today makes the award extra special to me. It is/was something that I never believed would happen.

My biggest hope with my new found status is that it will help facilitate and encourage my community efforts both public and private so that I can give even more of myself back but also hope that I can exponentially improve my technical skills in any areas that I may be lacking in this ever changing Data Platform landscape. I am flattered to join this community of fellow Professionals who I not only look up to, but which I also consider many to be personal friends.

This post would not be complete without thanking a few very special people.

First and foremost I would like to say a massive thank you to Niko Neugebauer (w|t) a man who I first met in 2012 and someone who has been a personal inspiration to me. Technically brilliant but also a Community champion and if I ever needed a role model (which I do), then Niko is it. Niko has not only believed in me from the beginning but has supported me whenever I have asked for his help or advice. Through his support, it is credit to him above all others that today I can call myself MVP.

Another Community devotee who I have also found inspirational and deserves my thanks has been my good friend and fellow MCM Edwin Sarmiento (b|t), who I first met in 2010 and has been encouraging me to succeed ever since.

I would also like to thank everyone who nominated me for the MVP award over the years. This time around I managed to complete 11 submissions, each taking me approximately 1 hour to fill in, and unfortunately ran out of time on the last 2. I even had 4 nominations that I had missed and had expired. So to those people who nominated me, regardless of whether I managed to fill in your nomination or not I would like to thank you all from the bottom of my heart for believing that I was worthy of the award. Without you this could not have happened.

Thanks also go to SQLPASS and its staff, directors and members who have enabled me to grow both technically and socially over many years.

Finally I would like to say a big thanks to my family who have endured my Community trips for years and continue to do so. This award is as much for you as it is for me.

Posted in Community, Personal Development, SQLServerPedia Syndication | 6 Comments

Incrementing SQL Sequences

Sequences were first introduced in SQL Server 2012 and are a way to improve performance in situations that would have traditionally been implemented using the column based identity property. One of the biggest downfalls of the identity property is that new values are generated on inserts meaning that they are also transactionally dependent. Sequences on the other hand are not and allow for caching and many other benefits which you can compare and contrast in this really good SQL Server Pro magazine article by Itzik titled SQL Server 2012 T-SQL at a Glance – Sequences.

Recently our performance environment was undergoing load testing using pre-loaded synthetic data which (upon execution) started to result in failures from identity conflicts. It was fairly obvious that our current sequence seed values were much lower than the loaded data identities. Not so much of a problem you might think, since we can easily reseed the sequence number via the Sequence Properties dialog (below). Simply select the Restart sequence checkbox and type your new seed number into the entry box next to it and click OK.

sequence properties

The only problem with this approach is that our database was configured (rightly or wrongly) with approximately 250 sequences! Since we could not be sure which sequences would ultimately cause us problems we decided to increment each one by 10,000.

Not being someone who likes performing monotonous tasks and also recognising the fact that this task would probably need to be performed again in the future I decided to attempt to programmatically solve this problem.

How to use it

Disclaimer: Before using the following script, please make sure you understand what you are doing and where you are doing it. The responsibility for its use and misuse is all yours!

The script below is fairly basic and generates a script to update every single sequence within your database (make sure you change context to the correct one) with a default increment of 10000 (feel free to alter as necessary). If you only want to update a range of sequences then obviously you should add a WHERE clause to this script and filter on whatever criteria floats your boat.

DECLARE @increment_sequence INT = 10000
   'ALTER SEQUENCE [' + SCHEMA_NAME(seq.schema_id) 
   + '].[' + + ']'
   + ' RESTART WITH '+ CAST(CAST(seq.start_value AS INT) 
   + @increment_sequence as VARCHAR(max)) + ';'
   sys.sequences AS seq
   LEFT OUTER JOIN sys.database_principals AS sseq
   sseq.principal_id = ISNULL(seq.principal_id, 
   (OBJECTPROPERTY(seq.object_id, 'OwnerId')))
   SCHEMA_NAME(seq.schema_id) ASC,
   seq.[Name] ASC

It creates a script in your query results as below. Simply copy and paste this into a new query window and execute.

ALTER SEQUENCE [DB_Sequence].[TransferID] RESTART WITH 10000;
ALTER SEQUENCE [DB_Sequence].[AccountID] RESTART WITH 68719;
ALTER SEQUENCE [DB_Sequence].[CustomerID] RESTART WITH 1010006;

If you do need to update many sequences in your database I hope you find this script useful and it saves you as much time as much as it has me!

Posted in SQL, SQLServerPedia Syndication | Tagged , , , | 1 Comment

Restoring CDC enabled databases

surprisedPerforming database and transaction log backups (and restores) are the meat and veg of a DBA’s responsibilities and if you are a seasoned professional I am sure you will have performed those operations ad-infinitum. Being complacent is perhaps the biggest mistake you can make as a “Guardian of Data” so you should always be prepared for the unexpected…

Several years ago in a Disaster Recovery scenario, I was asked to perform the migration of a large database which was used for a fairly complex ETL process that was both undocumented and something that the DBA team had no insight or involvement.

Due to a complete lack of a Disaster Recovery Plan for the system and process (how many times have we seen this guys!) I was forced to follow best practices, common sense and draw on my experiences in order to bring the service back to a “best known good” as quickly as possible. Since we had access to all existing backups taken so far, and having the ability to take a tail log backup I was able to recover across to DR with no data-loss (at least as far as I was concerned). I handed the environment back to the Business Intelligence team for their validation, operational testing and re-acceptance.

Of course it passed acceptance and resumed operation in production.

Now of course, what was not known at the time I performed this work was that the Database in question was enabled for Change Data Capture (CDC). Perhaps it should have been a logical assumption for me to make, but I suspect that even if I was aware of its presence, the significance of the situation might have escaped me.

I am not really sure what acceptance testing was performed (if indeed it was!), because after a few hours of activity (just enough time for a scheduled ETL process to execute and fail) it became clear that something was very wrong with the database.

Apparently the Change Data Capture tables were missing!

So now I knew about the existence of CDC in the database (and after digging around MSDN documentation for a few minutes) our mistake was obvious – we had not used the KEEP_CDC option upon restore, meaning that the CDC tables were no longer present. Unfortunately acceptance testing hadn’t detected this problem ahead of time and now our recovered database was live. After some discussion, the Business Intelligence team decided for us to re-create CDC on this warm database and to temporarily restore a copy of the old one (with KEEP_CDC!) to pull out those missed changes.

So what’s the problem with restoring CDC enabled databases?

The main problem with CDC enabled databases is that the KEEP_CDC option is incompatible with the NORECOVERY option.

For instance the following command:

	FROM DISK = 'CDCEnabledDB.bak' 

Will result in:

Msg 3031, Level 16, State 1, Line 1
Option 'norecovery' conflicts with option(s) 'keep_cdc'.
Remove the conflicting option and reissue the statement.

This automatically poses the question of how it is possible to restore a backup chain with CDC? On a database restore, in order to apply differential backups and transaction logs the NORECOVERY clause is required to prevent SQL Server from performing database recovery.

If this option is required but KEEP_CDC in conjunction with it is incompatible, surely this means point in time restores are not possible for restores that require CDC tables to be retained?


The investigation

When I first ran across this problem (and after reading Connect Item 587277 -which has now been updated by me with the correct resolution) I was incensed that Microsoft could be so stupid as to prevent something so necessary under certain situations. I saw it as a huge gaping hole in database recovery for any database requiring CDC. As I (and others on the Connect Item) saw it, if you cannot restore a log chain and KEEP_CDC then this will cause problems for:

  • Database Mirroring
  • Availability Groups
  • Log Shipping
  • Replication
  • Recovery to Standby

Quite infuriatingly the Connect Item was closed with:

“we looked into this but there is no plan as of now to change the current design and extend the support of this in the foreseeable future. Again, thanks for taking the time to share your feedback, this is really important to us.”

And that was that…for a time.

So what has changed?

Several months ago I was investigating the use of CDC enabled tables for a client project when I stumbled across the following statement in the MSDN documentation Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups :

“SQL Server Replication, change data capture (CDC), and change tracking (CT) are supported on AlwaysOn Availability Groups.”

Remembering the problems I faced years earlier I read that statement with interest and decided to spend time playing with the recovery of Change Data Capture when it dawned on me that mine and the other posters to the Connect Item (including the Microsoft respondent) had completely misunderstood the use of the KEEP_CDC clause.

Our misunderstanding was (unsurprisingly) that the KEEP_CDC clause was necessary to protect Change Data Capture tables from being lost on every restore sequence (which is wrong). Instead the clause is required to protect CDC tables upon RECOVERY. Change Data Capture schema and data is transparently restored behind the scenes during NORECOVERY in anticipation that you use the KEEP_CDC clause on RECOVERY -if not then CDC is dropped (again transparently).

Therefore in a restore sequence, recovery is the ONLY time that you need to specify KEEP_CDC in your restore sequence meaning that the incompatibility between this option and NORECOVERY is irrelevant.

For instance the following will work:

	FROM DISK = 'CDCEnabledDB.bak' 

	FROM DISK = 'CDCEnabledDB_1.trn' 

	FROM DISK = 'CDCEnabledDB_2.trn' 

In summary

Regardless of however experienced you believe yourself to be with SQL Server, there is always something that can come and bite you. Not even Microsoft Support and technology owners always fully understand how every single scenario or configuration is going to play out, so it is important that you Do Your Own Research.

While the use of CDC does introduce certain considerations that I will talk about another time, you should rest assured that as long as you understand the use of the KEEP_CDC clause (and make sure you use it when necessary) then you will not go far wrong.

Posted in SQL, SQLServerPedia Syndication | Tagged , | 1 Comment