SQL Server 2012 SP2 RTM got released :)

Yesterday, microsoft released Service Pack 2 for SQL Sever 2012. They made Slipstream Installation packages available as well to the customers :)

And hey, you know what….CU1 for SQL Server 2012 SP2 is scheduled to release in July 2014. It’s time to upgrade all your SQL Server 2012 Instances to SP2!

SP2 can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=43340

For the list of bugs which are fixed in this Sevice pack, visit http://support.microsoft.com/kb/2958429

Couple of very critical bugs which got fixed: ( they are critical to me, YMMV) 

SQL Server crashes when LDF file for Tempdb is full.
Rollback causes SQL Server Database to go to Suspect mode.
No error message when Log backup fails in SQL Server 2012.

Cheers!

The Wait is Over-Much awaited SQL Server Service packs are just around the corner.

Hurray…! At laaaaaast Microsoft officially announced today that they are planning to ship one last Service pack for both SQL Server 2008 and 2008R2. Timing of the release will be little odd though. They will be available to public after July 8th 2014(After Mainstream support ends). This is defenitely a good news (In fact great news)  for companies which don’t let DBA’s to Install CU’s and just rely on Service Packs.

If you are curious, SQL Server 2008 SP3 was released back in 2011 October and 2008R2 SP2 was released in July 2012. Since then microsoft is releasing Cumulative updates.
Get ready for SQL Server 2008 SP4 and SQL Server 2008R2 SP3 folks  :)

See this blog post from SQL Server Team for more information.

Idera’s SQL Extended Events Profiler – concise and Elegant tool for SQL DBAs!

In this blog post, I would like to introduce all of you to a very cool and concise Free tool from Idera.

Note:  I Do not work for Idera. Idera has nothing to do with this blog post. Am expressing just my opinion with my experiences with this free tool.

Okay, so….what is this tool al about?

If you are a SQL Server DBA, you might have already been in a situation where you had to run SQL Server trace on your SQL Server(s) per a developer request or for investigation purposes or some thing else.  Profiler, which comes with SQL Server installtion is a very heavy tool, especially if you are  not filtering out your events when running your trace. To avoid any overhead this graphical tool(profiler) causes, we could use “Server Side tracing” which basically runs the trace server side and stores the results either in a file or a table without the need for using profiler(Which constantly displays the results, which is very resource intensive). Impressive…But not as impressive as “Extended Events”. MSFT introduced “Extended Events” in SQL Server 2008 which is the low level/High performance eventing solution. Slowly and steadily…I am seeing people moving away from traces and Profiler which is nice.  If you are a SQL Server Profiler person, I’ve got a sad news for you, Profiler is depricated in SQL Server 2014(Instead, microsoft is asking you to use Extended events session(s) which is embedded in your SSMS under management Node). You might not like this initially, especially the way how you’ve to interact with XEvents from SSMS. This is where this free tool from Idera comes handy. This is very similar to how you would interact with Profiler(Selecting templates, events, filtering columns etc), but instead of starting a trace, it pulls info from Extended events which are very light weight and has significantly less over head on your SQL Server.

See below Screenshots on what am talking about…If you are already familiar with Profiler, this doesn’t need any further explanation on how to get started with this free tool.

1

Once connected, choose your template

2

Filter out columns as needed…

3

As you can see this could be very helpful tool to track down few things on your SQL Server without hindering it’s performance. Once you’ve got what you are lookin for, you can export the results as well.  This is a free tool, you can download it from idera and play with it and see if it meets your requirements.

PS Discussing the true power of Extended events which microsoft introduced for us is way beyond scope of this blog post.

Cheers!

SQL Won’t come up after Installing Service pack!!

Hello folks! In this blog post, I would like to share a strange experience which I’ve never experienced till today while Installing Service packs for SQL Server.

Well, I’m in the process of Installing new SQL Server(2012) on Windows Server 2012. Installed RTM…Everything went well. Now I kicked off applying Service pack 1 for SQL Server 2012. Erkk…..SQL services won’t come up and the event viewer says “Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.” and the one which is shown below!!!…

1

 “Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.”

Whatttt….?? Really? Nothing useful found in the SQL Error logs as well :( My immediate thought was to run Repair and I kicked off the repair wizard. It took its own sweet time. 25 mins passed and I got this stupid message:(

2

Agian, Nothing useful found in the SQL Error logs :( End result is it couldn’t fix it :(

3

This is not good…!

So what’s the work around/Fix for this issue which worked for me in this case?

I changed the Service account for SQL Server to Local System and Boom…it worked :) SQL Server DB Engine Service started and the build number has been updated as well! Once am done with Installing Service pack and the CU, I changed it back to the appropriate domain account(as it was earlier).

So, this sounds to me like some missing permissions issue but am not 100% sure what exactly it was looking for. Please let me know if you’ve encountered this issue in the past and what was the resolution.

SQL Server 2014 RTM released!

SQL Server 2014, MSFT’s cloud first data platform, released to manufacturing today(18th of march 2014) and will be generally available on 1st of April 2014 as per the SQL Server blog.

Alright, the wait is almost over and am waiting to get my hands dirty, exploring all the features 2014 has to offer, in real world work loads :)

Cheers!

Importance of reviewing your SQL Server Error Logs.

Few weeks ago I posted a poll here asking for how often do you review your Error logs. As promised, here comes the follow up post:)

Unfortunately, 45.45% out of 11 people said “Never and I’ll open Error log only If I want to investigate something” . IMHO, this is not a good practice(Especially if you want to be an outstanding DBA). Please don’t get me wrong…Am not saying that you are not an awesome and outstanding DBA! ( Well, People call you a DBA because you are already outstanding :) )

Okay…….let  me say this guys, Start reviewing your Error logs daily as the first thing when you get into your cubicle/desk/office. Trust me, there will be lot of surprises(at least a few) based on number of Servers(Instances I should say) you manage. SQL Server Error log is really one of the best buddies a SQL DBA has got for free besides default trace :) . I simply love these two things in SQL Server which provide so much useful information in case of investigating something(At no additional cost/overhead).

A simple real life use case: SQL error log records all the failed login attempts by default, if you keep an eye(or even Just glance over it) you should know if there are any unwanted/unsafe login attempts(Which are failed) hitting your SQL Instance. For example…I see quite a few times, incoming connections from a particular host attempting to connect as “sa”. Error Log records IP address of the machine and the time stamp of course. I can just do a NSLOOKUP and check with respective owner of the machine on what’s happening. This is just one useful scenario, there are tons of use cases which you can come up with!

Believe me guys, there will be lot of surprises for you and lot of things you’ll discover about your own SQL Server Instance which you don’t know, just by reviewing your error logs on daily basis for a week or two!

Most of the times, I hear people saying ” I am responsible for managing multiple Instances, I really don’t have time to get into each of my SQL Server and review error log. It will eat up my entire day“. Well, you really don’t have to do this. All you have to do is just setup a job and automate which basically reads all the error logs from all of your servers and generates a fancy report and sends you an email.(All you need is to just create a simple SSIS package and SSRS for fancy reports) or may be Powershell could help you.

If you just have handful of SQL Instances to manage, see this blog post.

Basically, This will allow us to be “Proactive” and not being “reactive”.  Preventing something bad happening to your database as opposed to fixing will really make you stand tall in front of your management and your peers. Am not saying, by reviewing Error logs daily you can literally prevent any issue from being happening. But you will be definitely able to reduce number of “OOPS…” moments!
IMHO, Even though part of DBA job is to troubleshoot/fix database related issues, our prime time goal as a DBA should be  trying to prevent those issues from occurring at the first place!
Let me know if you need any help automating this process. I will be glad to point you in the right direction.

Cheers! Happy friday :)

SQL Server Database Snapshots – Things to remember!

In this blog post, let’s see what SQL Server database Snapshots are and how they can be created and how to revert to a DB Snapshot and few important things to keep in mind before making use of them.

Microsoft introduced database snapshots in SQL Server 2005 as an enterprise only feature(I believe, that’s true even with SQL 2014…but I could be wrong). The basic concept of a database snapshot is to create a read-only, transactionally consistent(as of the moment of snapshot creation) static view of a given database. Even though they are named as “DB Snapshots”, they have got nothing to deal with Snapshot replication or Snapshot Isolation.

When could a DB Snapshot be useful?
Most common use case:  Let’s say your DEV/Testers are doing some testing(which makes whole bunch of changes to your database) in your test environment and they want the database to be reverted to a state how it was prior to the testing once they are done. A Snapshot works awesome in this case. Reverting is waaaay faster than restoring your database(especially when you are dealing with huge databases).

See this for more info on how they work behind the scenes, when will they grow in size, other use cases and Gotchas to remember while using Snapshots. Let me brief a few important things here.

  • DB Snapshots are not substitutes for your DB backups. Irrespective of whether you’ve snapshots or not, you should have healthy restorable backups in place. Don’t treat DB Snapshots as DR or HA solution.
  • Snapshots will introduce IO overhead, depending on workload on your Source Database.
  • You can’t backup a DB Snapshot and can’t attach/detach a snapshot; you can’t create snapshot for System Databases.
  • No GUI for creating Snapshot(Only T-SQL).
  • You can’t drop Original Source Database as long as a referring snapshot exists for that database.
  • No FAT32 support(Snapshots work on NTFS Sparse files technology).
  • Since they are read-only, static copies…new users can’t be added.
  • Make sure you’ve enough space on the drive holding your Snapshot file. If the drive runs out of space, Snapshot will be marked as “SUSPECT” by SQL Server.
  • Reverting to a Snapshot will break Log chain. Take either a Full or a Diff Backup(assuming you’ve got your latest Full backup) to bridge the Gap in your log chain) immediately after you revert. If not, all the log backups will fail from now on…
  • If your DB is not in SIMPLE recovery mode, Take a log backup before you perform Reversion to secure everything since your last log backup.
  • Full Text Catalogs will be lost once you revert your database. Be careful, if your database uses Full Text features as this could be an unpleasant surprise.
  • Both the source DB and the snapshot will be unavailable when the actual reversion process is in progress.
  • Source DB should be Online,No read only file groups should be present in your Source DB to be able to revert from a snapshot.
  • If your source DB gets corrupted, You can’t use Snapshot for reverting. ( See, they simply can’t replace your Backups)

Now it’s Demo time :)
I’ve a database called “Tst_restore” which has 3 tables and this will be my source database for this demo purpose. I’ve created Snapshot as shown in the below screenshot.

1

T-SQL:

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Tst_Restore_SS')
DROP DATABASE Tst_Restore_SS
GO
CREATE DATABASE Tst_Restore_SS ON (
NAME = Tst,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SREE\MSSQL\DATA\Tst_Restore_SS.ss')
AS SNAPSHOT OF Tst_Restore;

Now you can see I’ve a snapshot created.

2

See below for the Properties(files section).

3

As you can see, it has got no ldf file. (If you’ve noticed, while creating the Snapshot I didn’t provided ldf file in my syntax. Yes, that correct…you should provide only (all of) the data files, not the log file while creating DB Snapshot)

Revert a Database to a snapshot:

Now, let’s see how to revert a database to a Snapshot. Assuming something went wrong and you want to revert to snapshot which you’ve taken in the above step, you can use below syntax.

USE master
GO
ALTER DATABASE Tst_Restore SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE Tst_Restore SET ONLINE
GO

-- Reverting Tst_Restore to Tst_Restore_SS

RESTORE DATABASE Tst_Restore
FROM
 DATABASE_SNAPSHOT = 'Tst_Restore_SS';
 GO

As you can see above, to be able to revert we need exclusive access to the database(same as restore). Hence I am taking DB offline and bringing it back online immediately.
Note: Don’t forget to bridge the gap in backup chain right after you revert to a Snapshot. As I’ve already mentioned reverting to a Snapshot will break Log chain. Log backups will fail if you forget this step.

Hope this is informative…!