In this Quick and dirty post, let’s see a simple fix for one of the annoying error messages you’ll see in SQL Server 2005. Basically, You’ll see below mentioned error in few cases when you try to change the sa password using SSMS.
The Simple trick is to check the box “Map to credential” as shown below after providing a new password and click Okay.
This should work…Hope this helps!
In this short Blog post let us see how to move(Failover) the Cluster Group from one node to other in a Windows Server 2008 Failover Cluster. See here for more details on locating your Cluster Group for your Cluster.
Let’s get into the content! As you can see in the below Screenshot, I’ve my SQL Server running on Node2.
But How about my Cluster Group? I would simply issue “Cluster Group” from my cmd prompt for getting that info. As you can see below my cluster group is running on Node1.
Now, How to Failover the Cluster Group from Node1 to Node2 without any SQL Services Interruptions?
Sol 1 – Using CMD: Simply Issue the below command from your cmd prompt.
Sol2 – Using PS: Simply use Move-ClusterGroup “Cluster Group”. You might get the below Error if you are using Powershell for your Cluster for the First time.
This is because you haven’t imported Cluster Module yet for your PowerShell. You should be good to go once you import Failover Cluster Module. For More Information on Cluster CmdLets please see here.
Btw, There is no way you can do Cluster Group Failover from your Failover Cluster Manager GUI unlike Windows Server 2003!!
Hope this helps!
In this short Blog post let’s see how to fetch results in a Custom/pipe delimited format file directly from SSMS without any fancy SSIS Packages involved. In fact you can use SSMS for fetching result set as with any delimited value.
As you all know with all the default settings of your SSMS without any options enabled/disabled, we’ll get results of any SELECT Statement in a grid fashion as shown below. 
In the above screenshot I pulled few columns from “AdventureWorks” Database with the default Settings. You can copy the results including column headers and paste into excel and create a delimited file. Or if you are looking for CSV or Tab Delimited file, you can achieve without much pain by just right click in the result set and choosing “Save results as” and selecting the required file format. Let’s assume the requirement is to create a Pipe(|) delimited file with column names as the first row in the file, or some other custom delimiter in the file. You can’t see those options while saving the results by default. But, it’s very simple to achieve this just by turning on few options in your SSMS.
Go to Tools->Options->Query Results->SQL Server->Results to Text as shown below
In the top right corner drop down the list and select Custom delimiter as shown below.
Once you choose Custom Delimiter, you’ll have an option to enter the Delimiter as you can see below.(I entered “|” for Pipe Delimited files in this example)
Click Ok and now the trick is to select “Results to Text” in your SSMS while executing the Query and you get a result set with a clean pipe delimiters as you can see below:)
Or choose “results to file” and execute the same to save the results directly to a file. You can see in the below Screenshot where I’ve selected the required option in my SSMS and saving the file as “Pipe”.
Now, I’ve navigated to that folder and opened the file with notepad where I got the file exactly as I was expecting to be as you can see below 🙂
As you can see we don’t need any fancy tools to get custom delimited results…All it takes is exploring our own SSMS 🙂
Hope this helps!
This will be a pretty short blog post where I would like to share my thoughts on new Licensing policies of Microsoft SQL Sever 2012. Last year, when Microsoft announced that they are moving to Core Based Licensing from Socket based starting SQL 2012, I still remember many conversations we had within our team that “Microsoft is probably taking a wrong step by increasing the price, we might need to pay more than 2 to 3 times just for licensing on what we are paying currently”. Also there are huge ripples all over the internet regarding this change. My thoughts on this are “Nope! This doesn’t apply in all cases”. Few people just started to panic without properly understanding the new licensing terms and conditions. Actually, there is no need for creating such an attention all over the internet. Oracle has core based licensing since ages(come on guys, we are not running on SQL Server 6.5 or 7….we are taking about the best of its kind, the awesomely awesome SQL Server 2012 :D)
Why am I saying that this doesn’t apply in all cases?
Well, Core Based licensing limitation is only for High end Enterprise Edition(around 6900USD/Core). You can get BI Edition(around 8500USD/Server+around 200/CAL/user) license on Server based+CAL. Standard Edition has both the flavors – You can go with either Core Based(around 1800USD/Core) or Server Based(around 900USD+ around 200 USD/CAL/user). So If your shop is running on Standard Edition, you should be good straight away!
Note: We must buy minimum of 4 core licenses if we choose core based as per MSFT! Remember, this is just list price. Depending on your corporate agreements with Microsoft, you’ll be having special discounts in most of the cases…. 🙂
So, First we’ve to identify our needs, what exactly we really need in our environments. Do we really need Enterprise editions on each and every box? Do we really need each and every Physical server in our data center to be a 4 socket Quad Core Server? Gathering this crucial information and planning accordingly will save millions of dollars for any large SQL Server Shop.
For Virtualization – we’ve to license all the Virtual CPU’s. This might sound little harsh to few folks! How ever, if you are planning for SQL Server 2012 Enterprise, once you license all the CPU Cores in the physical Server(Host), you can have unlimited number of VM’s 🙂
So….My humble request is, stop blaming Microsoft and understand your real requirement and only pay for your real needs. Please do not just blame without really understanding the underlying nuts and bolts of licensing. I totally agree…licensing any enterprise product is always a pain and very confusing with especially with each and every release/edition. Touch base with your procurement department(if your team is not responsible for direclty dealing with MSFT) within your organization to know more about your corporate agreements you’ve with MSFT for licensing.
Visit this for more info. Have a wonderful weekend all!
Hey Folks! I know…..It’s been a long time since I wrote something technically related to SQL Server(well, am playing with my new DSLR Camera 😀 a lot now a days in leisure times) and so I’m here back with a very interesting and confusing topic for many DBA’s. Recently I had a discussion with one of my buddies on issues she had with a newly added drive to Windows Server 2008R2 Failover cluster and I was able(at least I think I was :D) to explain over our phone conversation! Later thought about coming up with a write up which might help even others in a similar situation. So, In this blog post let’s see how to add a new drive to your existing SQL Server Failover Cluster(Just an FYI : Win Server 2008/SQL 2008 in my case).
Before going any further, I’ll be wearing 3 hats in this Blogpost(a SAN Admin hat, Windows Admin hat and obviously a SQL DBA hat). Let’s start with wearing a SQL DBA hat!
Below are the screenshots of my current SQL Server Failover Cluster which I’m going to add a new Drive.
As you can see I’ve 6 SAN Drives dedicated to this cluster. Let’s see how to add a new drive with a name aaaaahhhh……say “SQLBacks2” to our SQL Server. First thing is your SAN Admin should create/present a new Drive for you to be able to add to our cluster.
Okay, let me wear my SAN hat. Now am a SAN Admin and I’m going to create a new SAN Drive for my Windows/SQL team.
Creating a SAN Drive:
Note: If you didn’t followed my earlier Clustering Series, I use Starwind for all my SAN Stuff. Just refer to my previous posts in clustering series to understand this tool more in depth.
step1: Add a new Target.
Step 2:
Step3:
Step 4: placing and sizing the Drive( I chose just 1 GB for this example)
Step 5: Making it a ISCSI aware disk(Mandatory for Clusters)
Okay..now Am done with SAN guy role and now I’m wearing a Windows Admin Hat to initialize the new disk which my SAN Admin just created for me from my Nodes.
Wearing a Windows Admin hat:
Went to ISCSI Initiator and all I have to do is Initiate this new drive as shown below. you can see our new drive as Inactive as of now in the below screenshot.
Once you click ok, you will be seeing this drive as connected in your ISCSI Initiator, but still this is not available for Windows. Now we’ve to go to Server manager->Storage and Bring it Online->Initialize->Create new Simple Volume(Format the Drive) so that you can see it physically in your My Computer. (See below Screenshot, once I did created the drive, I selected K$ as my volume)
Once, this drive is logged on both the Nodes basically now we’ve to add this drive to our Windows Cluster using Fail Over Cluster manager(am on Node1) as shown below.
It will search for all the disks which are suitable for clustering for a while…After few seconds, You’ll see below.
Once added, you can see this Drive has been added to our Cluster as Available Storage(Note: At this Point SQL Server Service is not yet ready using this Drive).
Now…Right Click on your SQL Server and select “Add Storage” as shown below.
So…Are we done yet? Nopee…….Here comes the most Important Part and the piece which I’ve seen people missing a lot while adding a new Drive to existing SQL Server Clusters. So what are we missing here? The Answer is SQL Server Dependency. Didn’t quite understood what I’m saying? Well, see the below Screenshot where Drive 7(new drive which we just added) is floating in air with no relation to SQL Server Service.
How to verify that the Drive has not yet added successfully 100% to our SQL Server Instance from SSMS? It’s very simple, Just try to access this Drive from your SSMS. In this example I’m just trying to take a backup of one of my Databases and looking for $K to place the backup, which I can’t see(But I can see it from My Computer) unless I add the dependency Manually as you can see below.
So…What to do now?? Just make an “AND” Dependency for your New Drive. But how??
Wearing SQL Admin hat now! Well, this could be even a Windows Admin depending on your company!
Navigate to your SQL Server and Right Click and select properties and go to dependencies tab as shown below.
As you can see, there is no Drive 7(DriveK) as dependency. Now you have to add the new drive as “AND” dependent as shown below and you are all set to go 🙂
Now see below Dependency report, where Drive 7 is no longer floating in the air 😀
and of course a happy SSMS as you would expect to locate the Drive for your Instance!
Now you are all set guys…Just try to move the Service to other node(s) and double check that everything is working as expected from all the nodes. Hope this helps! Cheers…
MSSQLTREK 