sreekanth bandarla

SQL Server Denali CTP3 and SQL Server 2008R2 SP1 – Released:)

Most awaited SQL Server Denali CTP3 has arrived atlast:) Microsoft has announced a public release of CTP 3 today. Also SQL Server 2008R2 SP1 was released Yesterday!
Please note- this is a 180 day Eval edition which Microsoft released today.
You can download from microsoft SQL Server Site or from https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/

Thought of posting all versions and Service Packs in one single place.
SQL Server 2005 SP1:
http://www.microsoft.com/download/en/details.aspx?id=22954
SQL Server 2005 SP2:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=9969
SQL Server 2005 SP3:
http://www.microsoft.com/download/en/details.aspx?id=14752
SQL Server 2005 SP4:
http://www.microsoft.com/download/en/details.aspx?id=7218
SQL Server 2008 RTM:
http://msdn.microsoft.com/en-us/sqlserver/bb671149.aspx
SQL Server 2008 SP1:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20302
SQL Server 2008 SP2:
http://www.microsoft.com/download/en/details.aspx?id=12548
SQL Server 2008R2:
http://msdn.microsoft.com/en-us/evalcenter/ff459612
SQL Server 2008R2 SP1:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26727

Important Note:Please consider cpu (32 Bit vs 64 Bit/Itanium vs Intel/AMD) before downloading. Choose IA64 or x86 or x64 while downloading depending on your Server.

What a Fail Over Cluster can do for us?

Clustering:

Cluster/ActivePassive/ActiveActive/SingleInstance/MultiInstance/Failover/Nodes/Services/Offline/Online….all these are the words which you will be listening very often if you are working with Windows Servers. (I’ll not be explaining all those terms and terminologies here in this blog post. They will be covered in another Blog post under Installing SQL Server Cluster.)

So what exactly is a Cluster? Let me try to explain it in understandable English language :-p

Let’s assume you’ve a very critical Application which your company CEO needs all the time to make his critical decisions for your company growth and it has SQL Server as its Backend and you are that awesome DBA who is supporting that SQL Server. Let’s say on one unfortunate day your Windows Server where you installed your SQL Server (let’s call it as ServerA) went down for some reason and Windows team is working on to bring server UP. Throughout this period when server was down your CEO will not be happy because SQL Server is not available implies the application is impacted.

Imagine if you had another Windows Server (let’s call it as ServerB) which takes ownership of the services and Applications in matter of seconds when your ServerA has any issue automatically. Isn’t it awesome??

Imagine multiple servers as a group always communicating with each other regarding their health and if one server is not healthy (In our case it was shutdown), next server which is available in that group takes care of unhealthy Server. Isn’t it awesome?? That’s the concept of Clustering in Windows. Nothing scary and nothing confusing right…See how simple it is to understand when we speak English J

PS we are only focused on Failover Clustering, Not NLB (Network load balancing) in this context.

So Clustering is nothing but a Hardware Solution for High Availability for your critical applications, making sure that your Apps are always available to users.

By this time you should be thinking about how expensive is this solution?? Unfortunately it’s pretty expensive solution because you need multiple Physical Servers (Nodes – I’ll refer Servers as Nodes from here). All the nodes which are participating in clustering should be exactly same in all aspects (for example OS Edition/Version, Service Packs, patches, Drivers Installed, hardware plugged in etc)

In Windows Server 2003 it used to be called as MSCS (Microsoft Clustering Services).You can launch this in administrative tools – Cluster Administrator or just Run-Cluadmin. Starting Windows Server 2008 they renamed it as Failover Cluster Management Services. You can launch this in administrative tools – Failover cluster management.

Quick Glance of Windows Server 2003 MSCS:

Image Source: http://msdn.microsoft.com/en-us/library/ms952401.aspx

Quick Glance of Windows Server 2008 FCMS:

This is from my Test Lab which I created on my laptop. I’ll post a blog post on how to setup a cluster on a laptop pretty soon. Stay tuned!! FYI you can see, I’ve powered Off NodeB and you can see all the services, Applications, Resources being taken care by NodeA (another Node in my Cluster).

That’s pretty much it what a cluster can do for us. Congratulations! You learnt a drop out of Clustering ocean for now;)

SQL Connectivity Error

Early in the morning, one of my developers called me at my BlackBerry saying “Our UAT Server is down, application is not able to connect to backend, We need your immediate assistance”.
Well, a typical start for the day with a typical SQL Server connectivity Issue!
The Error msg what they were getting when they tried connecting using SSMS is as shown below

Well, my first thought is to ping the Server. It’s ping able and interestingly i was able to connect to SQL Server using my account from my client SSMS. So it ruled out most of the probabilities right there! Then i went into SQL Server error logs(My Fav Place to look at for any issues, if not yours, make a practice of looking at error logs very often from now on). It was repeatedly saying

Error: 18456, Severity: 14, State: 16.
Login failed for User ‘AppppUser’.
What does this mean to us? State 16 says that the Default Database of the login is not available.
Okay, I glanced Object explorer and also i queried sys.databases to look at all the databases status(es). All were “Online” and were in “Multi_User” mode. Hmmm….had a sip of my coffee and i looked at the login properties and opened user mapping tab and it yelled at me throwing below message

Okay…It’s time to think what’s happening. I was curios to know what exactly is happening on this SQL Instance at that moment and i queried few of my favorite DMVs “sys.dm_exec_requests and sessions” and there was a session which was creating blocks for many other user sessions and i captured the affected Database from results of above mentioned DMV’s. Then i tried to expand the database objects for that Database from Object explorer and it was throwing at me all kinds of timeout errors! By this time i was sure that there is something tied up with this database for the login which is causing issue. Immediately i jumped into login properties and verified for the default Database. Voilaaaaaaaaa….there we go! The default database for that login is the affected database. I changed the Def database to Master and it worked like a charm. Now i started investigating who is that “Bad guy” who is holding LOCKS at database level and not releasing them. It’s the Developer who asked me for help (Poor Guy). It’s his Sleeping Session which created all this mess. Once i killed his session, everything looked clear in my SQL Server and now it was time to grab one more coffee(I’ve a valid reason to enjoy my coffee now:))

Epitome: Don’t just always look at remote connectivity settings of your SQL Server when you face such issues. Start your Investigation from SQL Server Error Logs. Trust me It’s the Best of Best friend for any SQL DBA.

Practice/Worst Practice

Have been to an amazing trip visiting few of my friends and was not able to spend some time in my technical blogging :-p

Okay! Anyways I’m back to business after a loong (No Pun Intended) trip!
Thought of sharing one of my sickest and weirdest experience happened today because of my recent practice of editing tables using SSMS GUI.
Do you love Altering a table using GUI in SSMS?? If you said “yes, I do” then it’s time to think about what you are really doing with your SQL Servers!! If you are learning T-SQL, Unarguably this is a fabulous place to learn syntax-es. But what if you are lazy in typing or used to do it even though you’ve enough exp supporting SQL Servers? – No excuses, you’ve to change your practice!

You don’t love GUI for Altering your tables but are you forced to practice sooo? Well, that’s the case with me. I’ll try to change with this lesson learnt:)

I’m supporting an environment where application team wants columns to be added in middle of the table since past 12 months (I know what you are thinking, it doesn’t make any sense adding a column in middle of table, all it matters how we write our select statements.) I had enough argument with Application team and they are not willing to change the practice:( 😦
For that we need to use GUI thing of SSMS for editing table. Doing those requests since past 12 months i’m kind of get used to it even for increasing column width! I pity myself!!

Okay! coming to the scenario, we’ve a 9 GB table where the request is to change the width of a column from varchar(25) to varchar(40) and as usual i started using SSMS GUI instead of simple ALTER statement and it was timing out again and again. I changed timeout settings from 30 seconds to 120,240,300 seconds, nothing helped and i started to press PANIC button on my Keyboard😦
I wasted 30 mins of my time banging my head and at last i called one of the senior most DBA’s in my team and he was looking for how and what exactly i was trying to do. When i explained what i’m doing, he reminded me about there is something called “ALTER TABLE” in SQL Server!!!LOL….And I was like, what am i doing and i slapped myself saying “what an idiot am i”.
ALTER TABLE / ALTER COLUMN did the magic in matter of seconds.

Epitome: SSMS is very poor and it does lot of unnecessary stuff behind the scenes for altering any table.(look your self by scripting out when using GUI). Believe me you’ll be surprised how SSMS is handling those things and i hope Microsoft will change things in future versions of SSMS.

Mirroring – Things to Consider

DB Mirroring! Very fancy term which we all are hearing starting SQL Server 2005. Your DR Server is often called as “Hot Standby”( Real time Synch) when you are using DB mirroring where as it is considered as “Warm Standby” while your DR DB is Log Shipped!
Gotcha – If you are implementing Asynchronous mirroring Mode, Can you consider this as a real Hot Standby? I would say our all time favorite answer ” It Depends”. yes it really depends and I mean it.

PS I’m not going to explain how to configure DB Mirroring, Modes of Mirroring, Fail over Options in this blog post. That will be covered in another Blog post under the same label “Mirroring”.

So…….
First and foremost, Are the Principal and Mirror Servers are exactly identical in every aspect of windows and your SQL Server? This is a must to avoid surprises when a real disaster hits your data center and everyone expects your DR Database would behave exactly same as production database in an event of Prod DB failure.(Disaster)
There’s a well known so called Best practice saying that for OLTP Databases Mirroring works best and for any OLAP Database Log Shipping works best. I Strongly Disagree with this statement.
So, what would i really need to consider in terms of Database(s)?
Instead of differentiating OLAP/OLTP, try to understand Log Records Generation rate(in other words, your Transaction log Growth) and Size of your individual transactions. yes, very frequent huge Bulk imports will lead to many issues with Database Mirroring.
IMHO, this is something which as a DBA for your application you would(must) know which also covers effective T-Log Maintenance.( Imp – No matter how good network you are on, if your LDF is not properly maintained, your Mirroring session won’t work as you expect and your principal(Prod) Database would be in jeopardy!

Network:
DB mirroring is highly dependent on network( of course it works on TCP End points). So you should be on a high band width and low latent network. Less the latency between your Prod and DR Data center, more efficiently your Mirroring works.
LAN vs MAN vs WAN: Obviously LAN provides best performance in terms of latency.
Typical RTT(round trip times) are as follows:
MAN – 2 ms to 20 ms
WAM > 200 ms

Monitor:
You’ve to always keep an eye on your Mirroring state changes. You’ve to get notified with whatever alerting tool(s) you’ve in place in your enterprise. you can also create manual alerts from using a simple T-SQL Script via SQL Agent( You need DB Mail Profile being enabled on your SQL Server).
You’ve to keep an eye on number of VLF’s( Virtual Log Files) in your Principal Database, in other words keep an eye on your transaction log. VLF’s concept is beyond the scope of this blog post and will be explained in later posts.
Extremely Important: SQL Server will not truncate your Transaction log if log records are not sent to your Mirror Database even though you are taking a frequent log backups on your Principal Database. The Log records will not be copied to your DR Database as long as your Mirroring Session is “SUSPENDED/PAUSED” or “DISCONNECTED” which might grow your transaction log to the point where your Drive has no enough room to accommodate your log and Users start screaming ” Am not able to do any Inserts/Updates/Deletes and it’s an outage/Impact to our Application, What’s happening”.
So….again i want to stress, always keep a special eye on your Databases which are participating in DB Mirroring. How to and where to identify “Mirroring is causing not to truncate your Log”? – Will be covered in a later blog. Quick Tip – do “select name, log_reuse_wait_desc from sys.databases”. If you see any thing saying “mirroring” in your result set beside your database, that’s it 🙂
Always monitor Unsent portion of the log in Principal and Redo portion of the Log in your Mirrored databases. You can set up all kinds of Cool thresholds from your DB Mirroring Monitor in SSMS and you can set your own Alerts accordingly.

Last but definitely not the Least – Practice your DR Scenario as many times as you can. Convince your Management and your Application Partners for frequent DR(Disaster Recovery) Drills aka BCP(Business Continuity Plan) Exercices.