A closer look at everything related to SQL Server

Who absolutely look forward to annual performance reviews at work? I do not consider myself in this group. I hate the logistic part of it. Tons of rows to fill in. Sometimes questions are ambiguous or repetitive or just plain senseless. Long story short, I got my annual performance review and lets say it was above average but my manager suggested one development goal for me and it  was to get training on Advanced T-SQL queries.

Do not get me wrong. I am a DBA for more than 10 years and I can write tsql and keep myself updated with the addition and changes in tsql syntax with the new versions of sql server. I do lot of reading and researching in my areas of expertise (High Availability, Disaster Recovery and performance tuning) almost daily. I do not consider myself to be a DBA developer. I never have to write complex stored procedures or views or triggers etc. According to my manager, this is where I can grow more. Let’s keep the discussion of whether I agree with him or not for a later time. I planned to take this as a challenge. I have grown in so many areas, why not excel in this one too? Real challenge is to work on something which is outside of your comfort zone and you have to make yourself do it.

So I decided to blog my journey of Advanced T-SQL Learning. I searched the net but have not found any classroom training that meets my need. But there is lot of material (Blogs, YouTube videos, books, white papers, BOL etc ) available. And this was one of the reason, I never concentrated on learning development stuff because whenever I needed something I always find that T-SQL or PowerShell  script online written by MVPs and other very intelligent people of the SQL World (Wait this sounds like name of my Blog!)  available. With little tweaking I was able to use it and it definitely served the purpose.

Anyways, this is how I plan to learn and blog about Advance T-SQL. I will select one item such as advanced error handling or CTE or DMVs etc an d will work on each piece for a week. So I will write one blog per week for 4 weeks. Then I will summarize my 4 weeks of learning in the 5th week blog with examples or maybe little project or something fun. Then again I will start the cycle of learning to get to the next T-SQL concept.

As for now, my intention with this blog is that it is for my own eyes only. It will also serve as a reminder\repository\evidence etc of what I have learned and achieved. This will also be a tool down the road, when going gets tough, to remind me how persistent I was and as a result I will get new motivation to move forward.

Cheers to Learning and Blogging!


TSQL 2012 IFF Function

In a nutshell, IFF function is a shorthand for Case function. Here is the example to illustrate its use and syntax.

Create database myDB;
Use myDB;
Create table Employees (id int, name nvarchar(30), city nvarchar(30), region nvarchar(30), country nvarchar(30));

Insert some records to play with. Read the rest of this entry »

Here are the #TSQL2sDay prompts from Erin Stellato.

Here is how my day look on Thursday July 12th 2012.

7:00 AM

Came in 2 hours early to patch a critical sql server. Installation of 25 windows patches took 30 minutes.
Completing it with verifying everything another half hour. I used the waiting time to check my outlook inbox and my Hotmail emails.

8:00 AM

Replied to several emails that needed my attention. Spoke to my manager (offsite) about the issues
that happened yesterday as he and other Team members have to pitch in to patch and restart 12 servers yesterday. Those were my responsibility but I had an off day yesterday.

9:00 AM

2 days ago, I have ugraded 8 OLAP servers to SQL server 2008 R2 (SP1) from SQL server 2008. Today the team lead is saying that  they are experiencing and issue and it looks like a bug introduced by upgrade. He wanted to know how difficult it is to undo the upgrade. DBA’s life is always challenging in that way. So the normal looking day turned into an interesting one. I kicked myself into high gear and started my research.

10:00 AM

Attended a webcast on sql 2012 Availability Group from Pragmatic Works by Ross LaForte.

11:00 AM

 We recently had a new Dell SAN called Compllent attached to 3 servers each of them have a DB of 10 TB. They are exactly same and used for reporting. They are loaded from staging DB in parallel. Ops informed me they need to create a snapshot and I have to detach DB one by one on each serve. Steps involve: lien the server, detach db, stop sql server, ops did the snapshot, I have to start the sql server and attach DB. Verify that everything is fine. This database has monthly filegroups (for easier backup strategy) so need to make sure all FGs are attached. Let the attach create the new log file.

12:00 PM

Went to grab a sandwich and did my lunch at my desk. Logged my WebMD fitness and food log. Checked tweeter and followed couple of links to read if it fascinated me. Tweeted what I found interesting.

1:00 PM

 I have patched one server on Tuesday on which we use 3rd party high availability tool called Neverfail. It is very different then Microsoft failover cluster or Microsoft replication but both these terms are used in it. Neverfail cluster has 3 nodes namely Primary, Secondary and Tertiary. The replication Usually takes 20 hours (in our environment) to complete but it was still running after 60 hours. Opened ticket with them which they will Work with me tomorrow but in the mean time I gave 4 gb more to OS so replication can move faster. Reviewed Logs to find out any other hardware related issue. Also I have to do lot of communication with users of this server.

2:00 PM

 One development server was scheduled to be upgraded to sql server 2012 today. I had prepared by running upgrade advisor and informed the interested party about its report showing some issues with the code after the upgrade. Still they wanted to move forward with in place upgrade. But I have to postpone it after I received installation error because C: drive had only 2 GB free (total C: drive was 25 GB, old 2950 dell server). Requested Ops team to create a VM replacement for this server. There was nothing that can be deleted from C: drive. SQL 2008 R2 install was on C: drive and sql 2012 in place upgrade install needs atleast 6 GB of free space on C: to work. My plan is to install sql 2008 R2 on VM. Restore user databases and logins and jobs and linked server and whatever packages user need to migrate. Then do the in place upgrade to SQL 2012.

3:00 PM

Helped Junior DBA (remote), hired 2 months ago, to install sql server in a step by step fashion according to our

SOP. Helped her via IM, phone and email.

4:00 PM

 There were few requests pending since morning to give permission on certain object to certain groups and users. I completed that and informed the users. Next I completed this log to record my day today.

5:00 PM

 Wrote email to ops to change the registry setting on one of our backup servers. I have researched the errors on Tuesday and had 2 documents prepared. Over the weekend most of our backup jobs run on most of our servers and goes to one backup server. Sometimes backups (usually transaction log backups) fail with OS error 87 ‘setendoffile’ that end of file not found. Apparently the error occurs when OS estimates certain space for backup but the compressed backup actually takes different amount of space so when OS tries to correct the end of file it sometimes times out. Solution we will try is to add a registry key of SessTimeout = 300 seconds. Default is 60 seconds.

“Everyone knows what attention is. It is the taking possession by the mind in clear and vivid form, of one out of what seem several simultaneously possible objects or trains of thought…It implies withdrawal from some things in order to deal effectively with others.” (Principles of Psychology, 1890)

Working with Deadlines

My #memeMonday post is here.

As a DBA, aside from scheduled and unscheduled maintenance tasks, we often have projects with deadline to work on. I usually break down my projects into smaller deliverable tasks. For example, a 3 month project may have 3 to 6 deliverables or  tasks, depending upon the type of project. These interim tasks give me confidence that I can deliver the project on time. This also helps me see any potential problems early on which could otherwise impact my final deadline. For each of these task, I try to anticipate and visualize the final deliverable and how it will fit with other tasks of the project and keep on tweaking as I go along. Another important thing is not to keep everything in mind. I write down and document as much as I can. Having broken down big project into manageable chunks, I make sure to address any problem in a timely manner. Last but not least, if all else fell, I do not fail to communicate early on if I am not going to meet the deadline. Not only that, but I also communicate the reason and the action I am going to take to resolve the problem that is sitting between me and my deadline.

Good DBA Qualities

Which are the top 5 qualities of a Good SQL Server DBA these days in your opinion?

1.     TeacherMentor

2.     StudentLearner

3.     Data StewardGaurdian

4.     Diplomat

5.     Designer

6.     Master Planners

7.     Trouble-shooter

8.     Patient

9.     Good listener

10.  Diligent

11.  Manager

12.  EthicalHonest

13.  CommunicatorPresenter

14.  Technical

15.  WriterBlogger

MemeMonday – November

My entry for November #memeMonday to @sqlrockstar on #sqlfamily:

What is a family? It consists of a group of people in which some are adults and some are children. What they do? They nurture each other, encourage, entertain and sometimes make each other angry as well. But they know that all family members are there for each other in thin or thick. Similarly #sqlfamily is a group of people who are passionate about SQL Server technology and they help each other through the problems and they learn from each other, they meet and greet each other at events and keep the connection after the meetings. There are some clashes of opinions also in this #sqlfamily like any other family, but they know that members of #sqlfamily are always there to cheer them up. They always find a common ground.

Pending Recovery

Today one of our application team requested to build new sql server 2008 R2. I asked them about the edition. They said only requirement is it should be able to handle 32 GB of RAM, other than that we are not using any high availability features. I installed sql 2008 R2 Standard. It supports up to 64 GB of memory. We moved the 6.5 TB database from the older machine having SQL2008 Developer Edition to new machine. This is how it was done. We first detached the existing database, shutdown sql server and moved the powervault containing data and log files to the new server. We brought the new server online. Sql server service was set to automatic start on the new server. Now we can see the database but cannot see it properties, cannot drop it or detach it either. Error said “The database is suspect. In SQL Server 2005 and later versions, a suspect database cannot be detached; before you can detach it, you must put it into emergency mode.”  I checked the state of database. “Select state_desc from sys.databases where name = ‘mydatabase’ “. It showed “Recovery Pending”. Another error said something like this “This database uses compression and this version of sql server does not support compression. Need to Upgrade”. We got the point. We tried to put the database in emergency mode  “Alter database mydatabase set emergency” but it complained “ it has to be in single user mode”.

So we put it in single user mode. “Alter database mydatabase set single_user”. Then we put it in emergency mode. Again I checked the state of database. “Select state_desc from sys.databases where name = ‘mydatabase’. It showed “Online”. Now I tried to detach the database and it complained “database is in single user mode, cannot detach”. Ok, we put it in multi_user mode and then detached successfully using GUI.

After this I upgraded using the maintenance option of setup GUI and upgraded it to Enterprise Edition. Then I attached the database using the GUI and 6.5 TB database came on line in less than 15 seconds. Altogether it took me 20 minutes. I like to give credit to Paul Randal’s (@PaulRandal) article http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx whithout which it would have taken us much longer.

Meme Monday

Deadlock blocks lock. Optimize indexes and rock!.

I attended sqlsaturday67 and attended Brent Ozar and Tim Ford’s sessions. Bill is president of sql server user group in Chicago and I helped him on the registration desk on the day of the event. So I added them as my tags.