sreekanth bandarla

Installing SQL Server 2008/2008R2 Failover Cluster- Part6

In part5 of this series I’ve shown you how to create a windows cluster. In this part of this Series, let’s proceed further installing and configuring Active-Passive configuration of your SQL Cluster. Before proceeding any further, let me tell you, I’ve created 2 domain accounts called “sqldbengine” and “sqlagent” in my Domain controller machine(in my case SANDC) with non-expiring passwords. I’ll be using these accounts as my service accounts for SQL installation. Also, I’ve created a Group for DBAops and created few dummy accounts and added to that group just for simulating a real environment in my lab.

Okay…Being said that, at this point we are all set to go ahead and create SQL Cluster, but we’ve to actually install “APPLICATION ROLE” on all the nodes which are participating in our Cluster. So…Go to your Serve manager in both the nodes and enable Application role as shown below.

Next->Next and choose Distribution transactions as well(this is needed if your SQL has to deal with Distributed Queries(MSDTC) using your Linked Servers) as shown below.

Next-> and Install. Go ahead and do the same on Node2 as well. This might take a while depending on your server capabilities and it may require server reboot. You should be seeing something similar to

Once you are Done with Installing application role. Now go to your Node1 and create MSDTC clustered service prior to SQL Server Installation. So, on NODE1 go to your failover cluster manager and follow the below screenshots sequentially.

Note: MSDTC clustered service has it’s own IP and service name & we already created a dedicated LUN M$ for MSDTC if you remember. It will validate and now it’s time to choose the LUN which we’ve already created. In my case it’s LUN M$. I’ve selected accordingly as shown below

you should see the final confirmation as shown below.

Click on Next and It’ll configure all the required Resources in our windows cluster. Please note, we haven’t yet came to actual SQL Server cluster installation. We are just preparing our Box for SQL Cluster Installation. If every thing went smooth, now you should see

You can add preferred Owners by just right clicking on winclustdtc in your left pane and going to properties. FYI I’ve chosen Node1 and Node2 in the same order as preferred owners.

Okay, now let’s start SQL Installation. I’ve inserted my SQL Server evaluation edition DVD into my DVD drive. You can also provide an ISO path in your VMWare Workstation if you have your installation media as an ISO file. Go to Node1 in your VMWare Workstation and

Once you click Ok, now you should be seeing that DVD being inserted in your Node1. Run Setup.exe and at this point you might get an annoying message saying that you need .Net frame work and latest version of Windows Installer(KB942288) even though you’ve already installed it. Just click Ok and it’ll install and asks for a system reboot. Just click OK. Once rebooted, launch Setup.exe again and now you should be welcomed with the SQL Installation Center. Select New SQL Server Failover Cluster Installation as shown below

It’ll do a very high level basic rule check and let you know if finds any potential issues which might prevent successful installation.

Click OK to proceed further. Choose your Edition and License key(if you’ve any). I’ve chosen Eval Enterprise Edition and accept the License terms and Click on “Install Setup Support Files”. This basically performs some windows installer actions,Normalize and validate Registry permissions and creates the required SQL Setup Bootstrap files under path “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap” by default. Once you click OK, it again does a detailed Rule check. You should not see any errors or Warnings in your real environment(here in my lab I actually got Network Binding Order Issue, we can safely ignore that here in lab). Click Next and choose

It’s upto you what to choose. In this Demo I’m selecting only bare minimum Database Engine and SSMS components considering my Server Configuration. Click Next and you will be asked for Instance Configuration. here I’ve chosen “sreeprod” as my SQL network name and i’ve selected named Instance(prod1) as shown below, which makes my work easy if i’ve any plans to make it an Active-Active cluster in future.

Click Next. It checks for Disk Space requirements. Click Next. Now, It’s time to select our SQL Resource Group name. You can Just click Next for defaults or you can choose your own fancy name if you’ve any standards for your naming conventions.

Click Next and now it’s time to configure our Disk Drives, In the below Screenshot, you can see Disks 1 and 2 are already reserved for Quorum and MSDTC, so you’ll have an option to select from remaining Disks. In this case, I’ve chosen all the available disks for me.

Click Next and now, it’s time to configure our Networks. Uncheck “DHCP” and provide a Static IP address for your SQL Network as shown below. I’ve chosen 192.168.1.118

Next and choose Service SID’s as shown below. In Windows Server 2003, we’ve to provide Domain Groups.

Click Next and Choose your Service Accounts. Remember I told that I’ve created two accounts prior to starting this installation. I’ll use those accounts as my service accounts as shown below.

Click Next and now it’s time to configure our DB engine. I’ve selected Mixed Mode Authentication and added my SQLDBA group as Administrators on this SQL Instance as shown below.

Now Don’t click Next, Instead go to Data directories tab for configuring your Data, Log and Backup Drives as shown below as per our LUNS created.

That’s pretty much it…..Just clik Next ->Next -> and Install as shown Below.

That’s it Guys!! This will take decent amount of time depending on your server configuration. Go and grab a cup of hot coffee(btw, don’t forget to keep your fingers crossed ;p). After 17 minutes, i’m welcomed with below screenshot:)

Hurrayyyyyyyyyyyyy..We did it!! See how simple it is…. Okay let’s go to our failover manager and also open SSMS and see how it looks like. See below Sreenshots!

Note: I’ve manually added Node2 to our Preferred owners List(Optional). Once, you add Node2 to our cluster, you will be seeing Node2 and Node1 as possible owners for your SQL-Sreeprod.

Let’s see how to add a node to our SQL Server Failover cluster and also I’ll show you how to patch your Clustered SQL Instance by applying SQL Server 2008 SP1. Stay tuned!

Installing SQL Server 2008/2008R2 Failover Cluster- Part5

In part-4 , I’ve covered how to configure SAN using Starwind software and we’ve successfully attached our LUNS to Node1. In this part of the series, let’s Install Windows cluster. Before proceeding any further, let us initiate ISCSI service on Node2 as well and add our SANDC machine as portal for discovering targets, very similar to what we’ve done on previous part.

Okay, now let’s start creating our windows cluster.
Very first step should be Enabling “Failover Cluster Feature” in all your nodes which are participating in clustering. In our case Node1 and Node2.
Go to Server Manager and follow the below shown on both the nodes.

Next-> Install. It’ll do some mumbo jumbo behind the scenes and you should be seeing failover cluster management option on both the nodes in your Administrative tools once this feature has been enabled successfully.

Now, let’s go to Node1 and open Failover cluster management from Start->Administrative Tools->Failover cluster management.

Let’s do a validate cluster configuration before installing our actual cluster. This is a very very useful tool which will identify any potential issues with our current cluster configuration. Please consider this as mandatory while configuring clustering in your real environments.
Note: The user which you are currently logged in should have administrative privileges on all the nodes of the cluster.

Step1:

Step2:

Step3:

Step4:

Once validation is completed(you should be seeing all green check marks in the validation report) we can proceed to create actual cluster as shown below.

Now instead of choosing validate a cluster, select on create a cluster and add node1 and node2. Now, give a name for your windows cluster and select a ip address as well.

you should be seeing something similar to following if everything has been done accurately upto this point as mentioned in this part of the series.

Click NEXT and that’s all we need to do for creating a Windows cluster. Basically it’s two Next-Next buttons:) Remember how pain it was creating a cluster in Windows Server 2003?? Aaaah…what a relief starting Win Server 2008:)

Tadaaaaaaaaaaaaaaaa…..that’s it for today! If you observe closely, you are seeing a red and warning signs in the above screenshot, that because i powered down Node2 just to show you all that our cluster installation is really working as expected! Let’s see SQL Server Cluster Installation in the Next part of this Series.

SQL Server Default Ports

Most of the times…We, the DBA’s are concerned about the TCP port which SQL Sever is running under and UDP Port under which Browser service is running under. So…what about the rest of the SQL Services? What is the Def port for SSIS? How about SSAS?

Thought of keeping all together in a single place which might be helpful. (I can use my own post as a single point of reference myself ;p)

Default Ports for SQL Server Services:
SQL Server Default Instance – TCP 1433
SQL Server Browser Service – UDP 1434
SQL Server DAC (Dedicated Admin Connection) – TCP 1434
–You can look into SQL Server Error logs at Start up for this. Eg: sqlcmd –Stcp:<server>,<port> or sqlcmd –S127.0.0.1,1434(if your SQL Server is configured to use only Local DAC Connections).
SSIS/MSDTC/T-SQL Debugger/RPC – TCP 135
SQL Server Service Broker – TCP 4022
SSAS – TCP 2383
SSAS Browser Service – TCP 2382

References:
http://support.microsoft.com/kb/968872#LetMeFixItMyselfAlways

Note: These are the default Ports which comes with the default Installation of your SQL Server. They can be always reconfigured as required. In fact, Default Port 1433 should be changed and should be included in your SQL build process, Since it is highly vulnerable to attacks.

Let’s see how can we verify our SQL Server Port…..
Verifying Port from T-SQL:
By using the DMV ‘sys.dm_exec_connections’. This will return the current connection details. I mean, If you are connecting to SQL Server remotely via TCP, it will return the TCP port, If you are connecting locally It won’t return anything since you might be most probably connecting using Shared Memory.(If you explicitly connect to your SQL Server using TCP/IP as your Network Protocol even locally, it will return the TCP port details.)

Please see the below Screen shots which i hope are pretty much self explanatory.

Using Configuration Manager: To avoid the confusion of what network protocol currently you are are on, You can always make use of our SQL Server Configuration manager to verify the port details as shown below.

Hope this Post is Useful!

Restoring a Database-SQL Server DENALI SSMS

Today i was playing with SSMS in Denali and found few Very Very Interesting enhancements with Restore Task. Let me show you what i’m saying….

Let’s play with Adventureworks Database for demo purpose. FYI I’ve a folder on my D$ where i placed all my Full, Diff and T-Log backups for this database.
First, let’s see what SQL Server 2008R2SP1 SSMS offers us when we try to do a point in restore on the same instance.
In SSMS go to TASKS and Restore and Database as shown below.

By default it chooses from Database where it automatically picks the Backups(Full/Diff and Log) accordingly what it needs to perform a Restore to most recent possible point in time as shown below.

Perfect, this is awesome….But, what if we choose from device, where we’ve to select our Backup files manually..Let’s see what 2008R2SP1 offers to us.

Ummm…Basically your are missing an option where you can select an entire folder and It automatically picks the required individual backup files as per the LSN’s…..So, we’ve to pick all the individual backup files required manually.

Let’s see Point In Time Restores(Recovery) for SQL 2008R2 SP1:
I’m choosing from Database for ease in demo purposes. I’ve chosen 11.00 AM in the first scenario, and it basically chooses the required as shown below.

This is So Nice…..But again if you’ve to choose from device, you’ve to point to individual Back up files manually(Which is Okay Most of the times, but irritating in few cases..It all depends).

Now, Let’s see what DENALI SSMS can do for us in Restore operations
At the very first glance…..You can see, Now we can do PAGE Restores using SSMS as shown below. Upto 2008R2 we’ve to use T-SQL Commands to Restore a single page.

Note: I was creating a Maintenance Plan for Backups and I found one interesting enhancement where we can select “which account” do you want to run this Plan under( As per the below Image).

In SQL Server 2008R2, we didn’t had this option. We’ve to go to Agent Jobs and change the Job owner and Execution Context. One more interesting thing to note.

Okay..Let’s actually do the Point in time restore from SSMS in Denali. I RC’d on the database which i want to perform a Restore ->Tasks restore -> Database. Let’s see what exactly DENALI is doing for us in this process.

See the highlighted areas in the above screenshot. Greatest feature of all is…..Now DENALI is smart enough to take a Tail Log Backup automatically, which should be applied as a the very last backup in our restore sequence. Huhuuuuuuuuuuuu…..I love it..Simply Superb!!We also have a Button at the bottom right to verify our Backup Media:)

Also, observe in the above screenshot I’ve chosen the source as Database. Let’s see what it has if we choose Device.

Now we can choose a folder which has all our Backup files:) Once you click OK..actually it is automatically choosing only the required backup files and ignoring the rest, which greatly saves our time.

Let’s see what Timeline option offers to us:

Oh My God…I can’t express how impressed I’m with this little graphic which is pretty self explanatory in all terms. Kudos to Microsoft!! Great Job guys. Lot better than few Third Party tools our there which are available to us!

Try Changing the timelines and you can see the above graphic changes accordingly!

Okay! Now let’s go to Files Tab and see how it appears.( As of now, in SQL Server 2008R2 SSMS there will be no FILES tab when you are performing a Restore Operation.(Actually we’ve to define everything in OPTIONS tab)

SSMS DENALI SSMS SQL 2008R2

So, if we want to change the Physical Location of Files or change logical file names, they offered us a separate tab, basically saying that all the file operations are done under FILE tab, which makes more sense to me!

Let’s go Options tab and see what it has for us.

In the Tail log backup Section it’s giving us where it is storing our tail log backup file. You can change it by clicking ellipses button.

Real Cool thing – It has now an option to close all the existing connections before actual restore starts…yeyyyyyy!! Also, an option to Prompt you before restoring each backup file(In this case, we’ve multiple files).

This is no less than any Third Party tool out there currently in the market!!(I’ve to agree that there’s no encryption offered while backing up and no network resilience features, But still we got all the cool enhancements for Freeee…..Anyways we’ve compression feature from SQL Server 2008). Over all I’m very impressed and glad to share this with our SQL DBA community.

Happy testing Denali:)

SQL DENALI SSMS – Few Interesting Facts/improvements.

This DENALI version of SQL Server is really driving me crazy with all the cool features/enhancements and even few funny things which you can do using your Management Studio!! Am getting soooo addicted to it:)

Let me ask you something. How many times do you open your Browser while you are working on something?Are you a Facebook addict? You know what….you can browse internet in your Denali SSMS. Yes..!you heard it right…..You can open a new tab in your SSMS and access your Facebook while executing a Script against your Production Database in another Tab…..(Hope you will never do this ;p). Okay let me show you what i’m saying.

Open your DENALI SSMS and hit Ctrl+Alt+R or follow the below Screenshot.

By Default you’ll be welcomed with SQL Home page –
http://www.microsoft.com/sqlserver/en/us/default.aspx

Okay! Let’s try accessing Facebook (just for fun).

I was able to successfully login to my account and also watched few videos..huhuuuuuuuuuuu…..
Anyways, i think i’m not going to use my SSMS for browsing and I hope you won’t too!

Now let’s see if we’ve anything to tweak this settings in our Tools->OPTIONS.

You can change your Homepage and your default search page. Also, see at the bottom for internet explorer options!! I can’t believe my own eyes!

Also, you can see lot more options in DENALI SSMS to tweak and make more flexible as shown in the above image. You can see how OPTIONS look like in SQL Server 2008R2 SSMS below.
SSMS DENALI

SSMS SQL Server 2008R2

Zooming Query Pane:

One Cool feature(zoom), really helpful when you are giving a DEMO of something or Mentoring others in a live meeting session or net meeting or by any other means. Once you hit the New Query button on your Menu Bar, if you look at the Bottom Left corner on your Query Pane you’ll see something to Zoom your screen. See yourself in the image below what i’m talking about.I guess there are lot more Cool enhancements and features available for us. Go ahead and explore all the Options from your TOOLS Menu and get familiarize yourself with DENALI SSMS.
Hope this Post helps! Happy testing Denali 🙂