How Intelligent is SQL 2012 IntelliSense??

I’m assuming whoever reading this post is already aware of SQL Server 2008 SSMS brand new Enhancement “IntelliSense”. Being said that, was “IntelliSense” really Intelligent enough upto SQL Server 2008R2? IMHO, It’s not really smart enough to read our minds! But WHY?? Because, It’s not smart enough to recognize a word in middle of your Object name! For Example, if you need information regarding DB Mirroring Endpoints, you’ve to actually start your typing with  “SYS.DATABASE_MIRRORING_*******”. With all the new hundreds and hundreds of DMO’s available, it’s really difficult to remember each and every starting word of a DMO. It would be really nice if SSMS is smart enough to return all the Objects if it finds a word anywhere in the Object name. I mean, in the above example It should display a list of all DMO’s even though we just type “SYS.MIRR” irrespective of where the word “MIRR” actually present.

You know what, with SQL 2012 that is how it works 🙂 This Simply Rocks!! It makes our DBA life so simple…Yeyyyyyy 🙂

See below Screenshots for better understanding what I meant in the above paragraph.

SSMS 2008R2:

As you can see in the above Screen cap, it’s no where close to Mirroring DMV’s 😦 In other words, I’ve to remember the actual starting words in that DMV!!

SSMS 2012:

Here you can see a list of all the DMO’s which contain a word “Mirr” any where in the Object! This is what I call it as really “Intelligent”. This is something which is really really Cooooooool!!!

Still running on SQL Server 2000/2005 today??

Are you running your business still on Backend SQL Server 2000 or SQL Server 2005 Databases? Well, let me tell you this. Go ahead and Upgrade to SQL Server 2008R2 now, at least give it a consideration right now. Plan for it, work with your architects and DBA’s for options you’ve.

Beware, Microsoft has announced End of Extended Support(April 2013) as well for SQL 2000. In other words you’ve to run your business on Unsupported SQL Server Version with Self-help Online Support only!!!!

If you are running on SQL 2005 and unaware of support policy, SQL 2005 is already running on Extended Support as of now. Mainstream support has already been ended on April 2011.

For more information and for understanding the options you’ve in place if your shop is still running on Older Versions of SQL Server, please refer to

 http://www.microsoft.com/sqlserver/en/us/support/support-updates.aspx.

DB Mirroring from SQL 2008SP2 to 2008 RTM or from SQL 2005 to SQL 2008?

Have you ever thought about this? Can we Mirror a Database from SQL Server 2008SP2(Principal) to SQL Server 2008(Mirror) RTM? Well, how about Mirroring from SQL Server 2005 to SQL Server 2008??  Let me show you the prior case.

Scenario 1:

Principal DB Engine Version: SQL Server 2008 SP2

Mirror DB Engine Version: SQL Server 2008 RTM

DB Name: DB_Mirror

Answer: Yes, we can! You can see below screenshot.

You should not see any Issues with Failover/Switching DB Roles from Principal to Mirror and Viceversa.

Scenario 2: 

Principal DB Engine Version: SQL Server 2005 SP3

Mirror DB Engine Version: SQL Server 2008SP2/2008R2.

I don’t have a SQL Server 2005 Instance to show you how it works, but Yes, we can! Very Important point to consider in this case is, Once you failover(Switch the Mirroring Roles) the Database from SQL 2005 to 2008, you can’t failback! This is One-Way approach. 

Note: You can’t setup Mirroring from 2008(as Principal) to 2005(as Mirror). Well, DB Version can’t be downgraded. In other words, we can’t restore a DB to lower Versions!!

Hope this is Informative and something which you can think about as an option for upgrading/Migrating Large Databases with very less acceptable downtime! ( Make sure, you understand all the caveats before trying this in your Prod Environments).

Installing(Adding) feature(SSRS) to an existing SQL Installation!

In this post, let’s see how one can add a new feature to your existing SQL Installation. I’ve chosen SSRS(Single Server Deployment, not scaled out) to be Installed on my machine. This Server already has DB Engine and SSIS Installed on it. Please do not do the same on your Production environment unless you clearly understand all the odds of doing the same on an already busy SQL Server.(This is for my Lab purposes) Below Screenshot shows the current status at a very high level of my Server which I’m going to Install SSRS on.

Insert your SQL Server 2008 Media and DC setup.exe, Now navigate to Installation tab and click on 1st link(New Standalone Installation or add features to an existing Installation…) as shown below.

Support Rules/Run check will be kicked off automatically by Installation Wizard. Fix any Errors/Warnings and click on “OK”. The next phase will be installing “Setup Support Files” by SQL Server Installation wizard. This might take couple of minutes.

Now, make sure to select Add features instead of “New Installation” as shown below.

Select SSRS in features(see below) and click Next.

Now, select your Service account(I’ve chosen Network Service) as shown below and click Next.

Now, the Interesting part of SSRS Installation process pops up.(See below Screenshot for available Options we’ve)

Since I’m installing on a machine which already has SQL Server Instance(In other words, adding feature to existing Named Installation of my SQL Server) Option 1 is greyed out for me. Also, Since I don’t have MOSS, Option 2 is greyed Out. So, at this point Report Server S/W will be Installed, but we’ve to manually configure SSRS by navigating to SSRS Config Manager.

After couple of mins, I was greeted with the below Screen 🙂

So, are we done with Configuring SSRS? Nope! Not yet. We are just done with the Installation, not the configuration. For configuring SSRS Service, you’ve to go to Reporting Services Configuration manager under your Configuration tools.

Once Web Service URL has been setup, navigate to Database tab, select on Change Database Button. This is the place where you can select the Database(s) aka Report Catalogs names and select your Instance and respective credentials. I’m going to select SREEDR Instance.  At this moment, there are no databases on this Instance and no SSRS related Logins!

Step1: select Create a New RS Database(Since, this is my first Installation).

Step2: Select your SQL Server(For Named Instance, you’ve to specify ServerName\SQLInstance)

Step3: Enter your preferred DB Name and your Mode.(I left it to default)

Step4: Enter Credentials how SSRS Service will communicate with your Backend Databases. Since both my SSRS and DB Service are running on a same box, I can leave it to default(Service Credentials). Any ways, I’ve a domain account(It can also be a SQL Server Login) which I can use, for this purpose.

Once everything goes Green, You should see the Databases being Created on the Selected Instance and also the Login will be created automatically with the required Permissions as well, which is awesome:)

Now, go to report Manager URL and click Apply! That’s it… Now your Basic SSRS is all setup and configured. As you can see now, I can open my Report Manager URL!

Tada…Yes, it’s that Simple 🙂

Cluster Logs in Windows Server 2008??

In this Blog post, let’s focus on where/how we can review Failover Cluster Logs on Windows Server 2008 and above. As most of us know on Windows Server 2003 Cluster, we used to have  “cluster.log” file on each node participating in cluster, which contains debug information. FYI, One can locate these files in “%systemroot% \ cluster” Folder. But how about cluster log files in Windows Server 2008/2008R2?? Uhuhh…It’s not something which you can review directly by navigating to systemroot folder. Below is the screenshot of that folder in my cluster.

You can see a folder called “REPORTS” in the above screenshot where all the cluster Validation Reports will be stored by default. attaching below Screenshot Just to prove, that cluster.log file can’t be located in the “reports” folder as well 🙂

Starting Windows Server 2008, cluster logs are managed by something called as “Windows Event Tracing“. Just an FYI, If you are interested, You can pull all the current  running traces by opening “perfmon” and navigating to Data Collector Sets. (Shown Below in the Screenshot)

So, as any other logs, cluster logs are stored in “C:\Windows\System32\winevt\Logs” folder with “etl” extension as you can see below.

Well, so How to read those .ETL files??

For that, we have to use “cluster.exe” command with “/gen” switch. Basically this will generate a human readable text file in your “Reports” folder.

Syntax: Cluster log /gen

Output:

 

As you can see in the above Screenshot, it will communicate with all the nodes in your cluster. In my scenario, Node2 is offline(Powered down).  BTW, even though Node2 is down, it will create “Cluster.txt” file in your Reports Folder with related information.

So, how to generate Logs related to a specific Node?

You have to use “/NODE” switch with your cluster log syntax. Please see below Screenshot.

As you can see, this time, we had no RPC Errors.

So, there is lot to explore/learn in 2008 Failover Clustering, if you are using 2003 since long time, things got changed drastically. There are lot of other options/switches available with cluster.exe. Even you can limit the size if you are interested. BTW, everything which I’ve shown here can be achieved via Powershell Cmdlets as well!

Hope this is informative….