July 2011 – Page 3

Installing SQL Sever Denali

Am all excited to let you know that I’ve downloaded SQL Denali CTP3 today and I’m installing it on my test laptop. I would like to share it with the community:)
PS this is a Standalone installation.

Workstation Config:

System Type: x64-based PC
OS Name: Microsoft Windows 7 Home Premium
Version: 6.1.7601 Service Pack 1 Build 7601
Installed Physical Memory (RAM): 4.00 GB
BIOS Version/Date: Dell Inc. A08, 11/7/2009

Okay! Let’s start Installing SQL Server. Insert your DVD(ISO) or just double click setup.exe file which you’ve downloaded. You will be welcomed with the below screen.

Select Installation Tab on your Left side and click on new SQL Server Standalone Installation as shown below.

It does a basic validation and you should pass all the tests as shown below. If any of the Requirements are not met, it will let you know what action is needed to proceed further.

Choose Evaluation Edition as shown below for testing all the Enterprise Features and Read/Accept the License terms.(As mentioned in another blog post under the same label it’s a 180 Days eval version).

After accepting License terms and Next, you are shown with the below progress screen of SQL Server Installation.

Now as you can see it has done a detailed rule check. You can see it also checked for SQL Server 2008

BIDS Installation. FYI Denali CTP1 had lot of issues with BIDS when the machine already had another Instance of SQL Server 2008. It’s good to see Microsoft included this test before proceeding further:)

Note: BIDS is a shared feature on a given Server unlike Database Engine.

Next screen is interesting as shown below – It has an option to Install All features with Defaults (Ideally, Which you don’t go for this on your Production Servers). For more realistic feel I’ve chosen Feature Installation.

Time to choose your components and features as shown below. My machine already has 2 instances of Database Engine and i believe one SSRS too…So I don’t want to hurt it any more 🙂

I’m choosing my Instance configuration as follows:

Next, time to choose your Service accounts. These will be domain accounts which your Windows Server Admin will provide you with the Password. You can leave it to default for test installations or change it accordingly. I’ve chosen my own accounts which i created for this installation purpose as shown below.

Now it’s time to choose your Authentication mode for your Denali Instance. I’ve chosen Mixed mode(this can be changed very easily even after installation). This is the place where you’ve to provide all your MDF/NDF and LDF file locations in the “Data Directories” tab. You’ve to choose atleast one account as a Sysadmin on this SQL Server. I would recommend to add Current User and SQL Server Service Accounts here as shown below.

It will do a final Config Rule check and creates a Config file depending on the features you’ve selected as shown in the below two screen shots.

It took me around 15 minutes to finish the installation and got the below screen saying “COMPLETED”:). Am all set to Play with Denali Database Engine Now.( Except HADR, which requires DENALI to be installed on a Windows Cluster)

Wowwww…….It’s a Brand New Look of SSMS.

Personally I love Blue and i feel like MSFT designed this for me:)

Well, That’s it Folks! Hope this info will be helpful and useful to you. Happy Testing Denali CTP3.

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.