Category Archives: How To

Lesson learnt – changing the root drive for SQL Server cluster instance

Playing around with your live SQL Server cluster is always something to be extra careful with. Especially when you have to remove some old storage from your Windows Cluster group (Service/Application). Today I had a scenario where I had to move the system databases and folders from one drive to another. So the process is as usual – change the physical file paths for model, msdb (and tempdb in my case), take the SQL Server down, copy the files to the new location (system folders like Log, FTData and database files of msdb and model), edit the SQL Server service from the SQL Server Configuration Manager and then simply boot the SQL Server. Sounds like a fairly easy job. But it has some lessons to learn – let’s explain a bit further the order –

1. Changing the system databases physical file location (MSDB, Model and tempdb) – following this link will guide you through the process. No surprises here.

2. Changing the location of the master database and ERROR log – again use the above link, just note that it is better to have the SQL Server instance down while operating.

3. Take down (if still not) you SQL Server instance. Copy the system folder Fix folder permissions – make sure you fix all permissions and inheritance on NTFS level for the copied. (Depending on your installation you might need to move the DATA, Log, FTData, Repldata folders).

4. Change registry values for the FTData folder – open the registry editor and edit the following path – HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance id>\Setup\FullTextDefaultPath

5. Change the output of your SQL Server Agent error log – here is a great article describing in details how to do it.

6. Start your SQL Server and cross your fingers everything is alright. Personally I am changing/renaming the old location of everything I moved, so I am certain that the SQL Server instance is still not using it for something.

7. At this point you should be ready to go and remove the old drive from the SQL Server cluster group. Now here is a great deal of attention you need to pay! First check this article on how to remove an unused drive from a WS 2008 Cluster. Now here is the deal – you need to take the resource offline before doing anything. Otherwise, you are getting ALL YOUR RESOURCES MOVED TO Available Storage group. Nice, huh? I actually had several more drives to remove, mounted to the root drive for SQL Server, so I took the first one, made sure it is not used (it had no files on it whatsoever) and just tried Remove from the GUI. Imagine my surprise, when an elementary enough (from my perspective) action of removing a disk made my group empty! So this disk was dependent on the root drive, which in terms was a dependency of the SQL Server, which in terms is depending on the IP and VCN (in short – everything is connected in a logical dependency chain). On top of that, the GUI asked if I am really sure if I want to delete it and if I am – then it will remove the dependencies and remove the resource from my group. Well…it did not! So, I have my SQL cluster group sitting there empty. I tried the suggestion from the above article to move the root disk back to my group, however, due to the large dependency chain, there was no simple way of doing it – it simply failed with error “Details: Error code 0x800700b7 Cannot create a file when that file already exists”.

Taking a step back on this error and looking at the logic of things, I decided that I need to move the SQL Server service resource back to the group. Well, that was not an easy task, because the resource itself is not available in the user interface. I used Get-ClusterResource (reference here) to see the exact naming of the SQL Server instance resource and then, using Move-ClusterResource (reference here) I moved the SQL Server resource to my SQL group, which in its turn took all its dependent resources with it. Voila! Everything was back in its place and the SQL Server was up and running. I will definitely make sure to have some lessons before doing anything similar in the future!




How to enable Query Designer for SQL Server in Visual Studio

As strange as it might be, my next post is going to be about how to get the query designer working in Visual Studio.


So first you need Visual Studio of course :-). If you want to use the Query Designer and click through the boxes to create a query, here are the steps you need to take, so it works as you expect.

  1. In the Server explorer create a new connection

VS - Query Designer 0

  1. You see the following window:

VS - Query Designer 1

  1. Then Change the data source – it is really important that you choose Microsoft SQL Server and as Data provider – .NET Framework Data Provider for OLE DB (yes, you need OLE DB, because with the native SQL Server provider, the query designer just won’t work).

VS - Query Designer 2

After you define your connection to the database in that manner, all new queries, that you start, will be using the Query Designer framework (the well-known MS Access-like area :) ).

My personal opinion on that one is that I would always go for the SQL Server native provider. It simply provides me the experience I am used to with SSMS – you do not need to click in order to create a query. After all Microsoft put enough effort in this querying functionality in Visual Studio that it does not really make sense to go back in time with the SQLX query designer. But again – this is just my humble and personal opinion :-)

Cheers and enjoy your databases with Visual Stuido!

How to setup and use the AMR Tool in SQL Server 2014

The Analysis, Migrate and Report tool (AMR) is part of SQL Server Management Studio 2014 and it aims at helping you evaluate your workloads and how effective/time consuming would the transformation to in-memory be.

Under the covers the tool is nothing more than couple of reports in SSMS, integrated as part of Management Data Warehouse feature. So before you start using it, you must enable the MDW and start the collection jobs.

In SQL Server 2014 they are completely rewritten and now instead of having the well-known Server Activity, Disk Activity and Query Activity collections, we have Stored procedure analysis and Table usage analysis collectors:


Once this is implemented, the ARM reports are accessible through the MDW reports in SSMS:


Note: MDW is my Management Data Warehouse database.

Now let me tell you a bit more about the “tool” – there are three reports, that are useful and that aim helping you identify all tables and procedures you can plan transform in to in-memory OLTP.

The Transaction Performance Analysis Overview report:


This report is simply the starting point where you can start your exploring :)


The Stored Procedure analysis report:


This report shows you the most expensive stored procedures in terms of worker time and those are suggested to be the best candidates for migration to in-memory OLTP.

The Table Usage report:


This report is one of my favorite ones – it shows you graphically the best options for tables to transform to in-memory OLTP. On the left axis you have the “Gain” i.e. how much will you gain if you transform. On the bottom axis you have the work that needs to be done in order to transform. So – the best candidates for in-memory OLTP are the top-right ones!

The Table contention analysis report:


This one is basically the same as the previous one, but it shows you the tables that are mostly affected or part of contention. Again you have migration work and gain on the axis.

Well, that’s the AMR tool – nothing more than couple of reports and an activated MDW.

I am now really interested in the logic behind the reports and I’ll start investigating – especially how MS decided how much will it take to migrate a table/SP and what the gain would be. I suppose there are some magic numbers here as well :) But that is for another post ;-)


How to check if your databases are part of a replication

If you need to check if there is any database on your SQL instance, related to replication, you have couple of different options – from manually checking through SSMS to using different options with T-SQL. Now I will share a simple script that you can use if you need to quickly check if your databases are part of a replication.

The script itself is exposing information in sys.databases, so you will need proper permissions to do that (I guess you are a sysadmin already :) )

	name as [Database name],
	CASE is_published 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Published],
	CASE is_merge_published 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Merge Published],
	CASE is_distributor 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Distributor],
	CASE is_subscribed 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Subscribed]
FROM sys.databases
WHERE database_id > 4

Once you have the output you will have to dig up a little bit to see the details about the replication your database(s) is part of. The script is just pointing you the direction. Here is an output:



Note that you have two different columns for the publishing part – one for merge and one for transactional/snapshot replication.

If you want to write your own version of the script, you can also use DATABASEPROPERTYEX function –

What are the minimum permissions for Activity Monitor

Activity monitor is something that I do not use very often – it is just easier for me to work with the DMVs and the rest of the tools out there that show you what really is happening on your server. Although I am not using it, there are DBAs and support guys, who count really much on the Activity Monitor in SQL Server Management Studio. And as every DBA should do, they are all trying to access it remotely instead of RDC-ing the database server with Terminal Services. So far so good. If you are a member of the Local Administrators on the DB Server and sysadmin on the particular instance you want to monitor – everything is just great – you just open a management studio, connect to the instance remotely and start your Activity Monitor. What are you going to do if you are not a Local Administrator and you are still a junior DBA, not blessed with sysadmin permissions over the SQL Server instances? Well here comes the interesting stuff. Here is how to enable Activity Monitor for remote connection for non-admins.  Read More →

Monitoring Data Quality activities on your server

In this short post I will show you how to check for the activities run or being run on your Data Quality Server.

First you need to open the Data Quality Client, placed in Start menu –> All programs\Microsoft SQL Server 2012\Data Quality Services. Once you connect to the DQS server, go to the right-most panel (Administration) and click on Activity monitoring button:


A new window opens and then you can see a list of the activities that your DQS instance is currently busy (or not) with.


In the monitor you can find the following information:

  • Activities, that are performed or currently running on the system (including their start and end time, duration, type, which Data Quality database they have been run against)
  • Activity steps
  • Profiler output (what the activity did in terms of knowledge discovery, data cleansing, etc.)


How to enable PowerPivot and Power View in Excel 2013

I am working on a presentation for our newest local PASS BI Chapter (Bulgaria BI & .NET Geeks) and around the subject I am preparing there are a lot of questions that are popping up. So I decided to show you here how to do some of the stuff I will be presenting.

Before we start though, first we will have to enable PowerPivot and Power View in Microsoft Excel 2013. So now I am going to share exactly this.

So first you open a blank workbook in excel and go to File->Options


Once done, go to Ad-ins page, from the add-ins drop-down menu select COM addins and press GO.



After that there is a window opened where you can see all you COM add-ins (both enabled and disabled). So next you have to check PowerPivot and Power View boxes.



Hit OK and you are done. How to tell that everything is alright? You should check in the INSERT ribbon if you have a Power View button and also if you have a PowerPivot ribbon on top:




powerpivot powerview


That is it. Now you are ready to use both add-ins and author reports you’ve never imagined you can do with Excel :)

Monitoring MDW purge job

During one of my latest investigations on SQL Server MDW, I had to learn a few things the hard way – there are several stored procedures, that run on the central MDW server, such as the sp_purge_data which are designed to give you a constant update on what are they doing, but you need to manually enable it. Most of the stored procedures have PRINT/RAISEERROR statements in their code and in the case of the purge SP, I wanted to see what is going on behind the scenes. The sp_purge_data procedure is called via a SQL Agent job, called mdw_purge_data. So in my case I was lucky – what I needed to do is to simply set an output of the job step (called Step 1) like this:



Then just save the job and run it – the log is immediately created and now you can see what is going on:


How to check if your database has been upgraded

A month ago I noticed a very neat post about finding if a given SQL Server database has been upgraded or not. The original post shows what to use and where to look for the info. In short, DBCC DBINFO reveals two fields – dbi_createversion and dbi_version, which can ultimately help us understanding if our database has been upgraded or not.

However, imagine if you need to do this for all your databases (usually a lot more than 5-6 :) ). This would not be an easy task, so I took the script I am using for checking the last successful run of DBCC (original post here) and modified it a bit, so now the output is a lot more readable.


The entire script you can find below:

       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)


    SELECT [Value],
FROM #temp WHERE Field IN ('dbi_dbname'))
    ,CHECKDB2 AS ( SELECT [Value], 
FROM #temp WHERE Field IN ('dbi_createVersion'))
	,CHECKDB3 AS ( SELECT [Value], 
FROM #temp WHERE Field IN ('dbi_version')
SELECT CHECKDB1.Value AS DatabaseName
        , CHECKDB2.Value AS DatabaseCreateVersion
		, CHECKDB3.Value AS DatabaseCurrentVersion
		, CASE 
			WHEN CHECKDB2.Value < CHECKDB3.Value THEN 'Yes'
			ELSE 'No' 
			END as DatabaseUpgradedStatus
	ON rn1 =rn2
	ON rn2=rn3


Probably you wonder why do you need to know this info – to be honest, so far I do not have  a reason besides that it is cool and geeky. Have fun!

Change compatibility level on multiple databases and multiple instances

Following my previous post on mass change of PAGE_VERIFY option, I want to share one other script that you can use if you are using a Centralized Management Server and multi-server queries in order to change the compatibility level of multiple databases and instances.

With the below script you can change the compatibility level of all databases and in my example I am setting it to compatibility_level = 100

--mass change the compatibility level option for all instances and 
--databases that are not read only and have current comp. set to 80 or 90
sp_MSforeachdb '
use [?]
if (select count(*) 
	from sys.databases 
	where compatibility_level <100 and 
	name=''?'' and database_id>4 and
	is_read_only = 0
alter database [?] set compatibility_level = 100
PRINT ''compatibility changed to 100 for database = '' + DB_NAME()