Friday, 29 November 2013

SQL Server Server Side Trace

Often I get request by my clients to run SQL traces in odd hours for performance analysis, SQL Traces are resource expensive operation and we have to be careful how we run the SQL Profiler.
It is not recommended to run SQL Trace through SQL profiler GUI for a long time over the network, You may end up creating performance issues with the server you are running the trace for,
The most effective way to run SQL traces is running a Server side trace and most importantly we can automate this and schedule it as when required though SQL Agent. Let us see in this article
we can run a server side SQL trace.

First thing we need to prepare for a server side trace is the trace template, It can be easily prepared for your events and columns using SQL Profiler as below,

Select the event and columns you require in the trace,

Add the appropriate column filter to reduce the unwanted data in the trace,

Now Run the trace for couple of seconds and stop it but do not close the window. Go To File->Export->Script Trace Definition->For SQL Server 2005 – SQL11…  and save the template as .SQL file locally.


Now Open the trace definition file, edit the sp_trace create parameters to give the output trace file names and rollover parameters ( ) and save the file.


/* Created by: SQL Server 2012  Profiler          */
/* Date: 14/11/2013  10:44:08         */
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'D:\Vinoth\TestServer_SQLTrace', 20, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted

If you note at the bottom of the trace definition you may find the filters you have added in the GUI and a store procedure exec sp_trace_setstatus @TraceID, 1.

This procedure is used to start/stop and delete traces automatically ( ).

 Now that we have set up the template, we can copy the trace definition create a SQL Agent Job. This Job will start the trace you have define on the server to the trace file as provided in the definition.

Use the below system table to find if the trace has started successfully,

select * from sys.traces

we have now set a Job to start the trace successfully we can schedule the job as per the requirement. Let us now look into how we can stop the trace in an automated way.I use the below TSQL to stop your server side trace using the sp_trace_setstatus store proc again with the stop parameter.

DECLARE @tcid int

if exists(select *  from sys.traces where path like '%<your Trace Definition base path>%')
select @tcid = id from sys.traces where path like '%<your Trace Definition base path>%'
exec sp_trace_setstatus @tcid, 0
exec sp_trace_setstatus @tcid, 2


You can set up this code in a SQL Agent Job again and Schedule it to run when you want to stop the trace thus making the whole process automated without manual intervention and running as server side trace takes much less resource to SQL Profiler.

Thursday, 21 November 2013

Executing powershell script in a SQL Agent job - Host errors

Recently I created a SQL Agent job that’s runs a PowerShell script, I tested the PowerShell script in my PowerShell editor and the script was running fine, but when I ran the same script in SQL agent job it failed with the following error

The corresponding line is '$space.ForegroundColor = $host.ui.rawui.ForegroundColor'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception setting "ForegroundColor": "Cannot convert null to type "System.ConsoleColor" due to invalid enumeration values. Specify one of the following enumeration values and try again. The possible enumeration values are "Black, DarkBlue, DarkGreen, DarkCyan, DarkRed, DarkMagenta, DarkYellow, Gray, DarkGray, Blue, Green, Cyan, Red, Magenta, Yellow, White"."

This error happens when you have a cls or clear-host command on your script as PowerShell run under SQL Agent job does not like any of the host commands. Once I cleared I received another error which was related to Host commands,

 A job step received an error at line 107 in a PowerShell script. The corresponding line is ' if($sleepcount -ne 2){write-host "Waiting for 10 Seconds...........";Start-Sleep 10}'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot invoke this function because the current host does not implement it.  '.  Process Exit Code -1.  The step failed.

Similar to our first error this was due to write-host commands in my script I had to comment all my write-host commands in my script to run the job successfully.

Thursday, 24 October 2013

Find Orphan SQL Users and Fix using ALTER USER

For years i have been using syslogins and sysusers tables in a cursor to find orphan users and sp_change_users_login to fix orphan users in SQL Server database.

With sp_change_users_login depreciated from SQL 2014 and with new system objects post SQL server 2005 I thought i would be a good time to post the new version of the TSQL i follow.
I have 2 versions of query to find Orphan users in a SQL database,

Version 1:-
WITH login_CTE (name, type, sid)
Select name, type, sid from sys.database_principals where type = 'S' AND name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA','sys')
select a.*,'ALTER USER '' WITH LOGIN =' As Fixusers from login_CTE a
LEFT JOIN sys.server_principals b ON a.sid = b.sid
where IS NULL

Version 2:-

Note:- This version had some issues with databases with non standard collation to master database, So I have used the COLLATE statment to match the collation. 

WITH login_CTE (name, type, sid)
Select name, type, sid from sys.database_principals where type = 'S'
select *,'ALTER USER '' WITH LOGIN =' As Fixusers from sys.server_principals a JOIN login_CTE b ON a.sid <> b.sid and = COLLATE Latin1_General_CI_AS

Both versions will will have a column with name Fixusers with the ALTER USER statement instead of sp_change_users_login to fix the orphan users. Just copy the column and execute the query to fix all the orphan users in the database.

Eg: ALTER USER TestLogin1 WITH LOGIN =TestLogin1

Note:- This query is database specific and needs to executed in all databases required to be fixed.

Friday, 11 October 2013

Store Perfom Data Collector data to SQL Server

As a DBA we frequently run perfmon on our servers to monitor performance of the SQL Servers, In this section I am going to write how to schedule a perfmon data collector to collect the perfmon data to a SQL server destination.
Step 1:-
Open Perfmon from the SQL server you want to collect the counters, Perfmon can also be collected remotely but i ave used local server in this exercise.




Right Click Data Collector Set->User Defined -> New -> Data Collector set


Step 3:-
Select a name for the collector and chose manual configuration as below

 Step 4:-
Choose Create data logs->Performance Counter


 Step 5:-
 Choose the Interval in which the data needs to be collected and Click Add to select the counters 

 Select the count you wish to monitor and Click OK

Step 6:- 
Click Next, Don't worry about the Root directory at this point


Step 7:-
Choose the  account in which the collector jobs needs to run, preferably the account which has access to SQL server and WMI and Finish the configuration



Now you will be able to see the Data Collector set up in the Perfmon


Step 8:-
Next step is to create a ODBC for SQL server where the data needs to be stored, Go to Start->Administrative Tools->ODBC Data Source and choose System DSN tab.



Step 9:-
Click Add to create a new data source and select SQL Server data source, Please do not choose the Native client data sources, there is a known error with the Native client accessing Permon SQL server data and usually error out as "Call to SQLExecDirect failed with %1" when you start the data sets so make sure you choose SQL Server and proceed to create the DSN.



 Choose the database to which the Perfmon data needs to be stored as default database.




Step 10:-
Now go back to Permon data Set, go to you collector set "SQL Server Usage" in my case
and right click on DataCollector01 and properties.



Step 11:-
In the Properties screen choose the format as "SQL" which should enable your Data Source Name drop down box, Check if the system DSN you created is available in the dropdown box and choose it as the data source and Click apply and OK.


 Step 12:-
Now all the setup has been completed just right click on your collector set and click start which should start collecting the permon data to SQL Server.


To Verify the SQL data go to the SQL database to which the data is collected and execute the following query,
a.counterid,CounterDateTime,b.ObjectName,b.CounterName,b.InstanceName,DefaultScale,CounterValue from CounterData a
CounterDetails b ON a.CounterID = b.CounterID
You can make this database as DW and use BI tools to create reports to SSRS and or any other reporting tools

Tuesday, 17 September 2013

PowerShell to Enable and Configure Database Mail across various SQL servers

Recently I was asked to configure Database Mail on all SQL Servers in one of my client environment, Instead of using SSMS or TSQL I tried to use PowerShell with SMO objects to accomplish the task faster, Find the script below I created for the same, I took reference from and created my version of script, The Script can be run for one server or set of SQL Servers listed in a file. By default the Account and Profile name will be ServerName or SQLName_InstanceName in case of named instance which you can change by just assigning right  value to the variables in the bleow script.
Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Wednesday, 4 September 2013

Analysis Service connectivity error due to SQL Browser service

Today some of my SSIS packages were failing to connect to Analysis servicesserver with the following error, 
Analysis Services Processing Task: A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running.
This was a new package and source and destination analysis services were installed recently, Once I got the error I double checked and found the browser service was running fine on both source and destination even weird was I did not configure any port for SSAS and it was connecting to default TCP 2383 which should not have caused any issue. I double checked the SQL Port configuration again on the servers and it looked as below,

The Port configuration ‘0’ means the connection should go through the default port 2383; next thing I did was to find out if the TCP port 2383 was blocked under any firewall but it wasn’t.
Then I tried to connect to the SSASthrough management studio without forcing the port number it error out again with a similar error but what was interesting was it said the SSMS was trying to connect to <SERVERIP>:2382 and this port 2382 was refusing connection.
On analysis from MSDN TechNet’s I found 2382 was the port ID to which client connect to browser service to resolve SSAS ports similar to UDP port 1434 in database services. But still why are we not able to connect to this port?
My last option was to try connecting using explicit port number in SSMS like <Servername>:2383 instead of instance name and I was able to connect successfully. 
Now it eventually came down to the browser service connectivity issue, after several trial and error and reading many articles; it all pointed to the service account of the browser service, The Browser Service in my system was configured under local service, I changed it to a domain account with Admin access in the system and again it was erroring out with the same error. As a last try I changed the service account of Browser Service to “Local System“ VOILA!!!!! Connectivity was fine now and all my packages were running fine. I am not sure why this happens but thought it be of help to write it down for others having similar issue or someone to shed me some light on why this happens. 

After this error, I ended up changing the service account of Browser service on all my servers to Local System!!!!

Thursday, 15 August 2013

PowerShell to Script SQL Analysis Service Database(XMLA Script) and ReApply on a different Server

Similar to my  post last month to script and reapply DB service database objects ( ), I was working on a project migrating Analysis services databases from SQL Server 2008 to SQL server 2012 and my client did not want to go the backup and restore route due to some changes to the base database schema structure. So we decided to script the AS database and re-apply it on the destination before we process the cubes. There was also a requirement to change the data sources after the source DB script was created as the server names has changed in the new SQL 2012 farm.
 I took the method mentioned in as my base for scripting and created a wrapper to script the databases and change the data source as per the server name mentioned in the script as below. 

1. ScriptASDB.ps1


I have used Invoke-ASCMD mehod to reapply the xmla scripts, invoke-ascmd method can be found in sqlps module of powershell and unfortunately this model comes only with SQL Server2012 client, so you need a SQL Server 2012 client installed to run this script but the script will execute perfectly for SQL 2008 AS, we only need the SQL 2012 client only for the invoke the command.

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Monday, 22 July 2013

PowerShell Script to find OS Edition and Total RAM on a Server

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Tuesday, 25 June 2013

Configure SSIS to SQL Server 2012 Cluster

SQL Server Integration services are not cluster aware by default, the following article shows a documented steps by Microsoft on how to configure SSIS to Cluster.

Install SSIS on all nodes of a cluster, If SSIS is installed after the SQL DB Cluster is built you need to select Stand-alone installation route(As shown below) as you cannot add SSIS to an existing SQL cluster during installation.

Open Failover Cluster Manager, connect to the cluster role to which SSIS need to be added as resource(usually SQL Server Role), Right Add Resource->Generic Service

Select SQL Server Integration Services and Click Next

Click Next

Click Finish, Now you should see SSIS added to as a resource of the Cluster Role.

Now Right Click on SQL Server Integration Services 11.0 Resource and select properties, In the Properties box go to Dependencies Tab and add SQL Server Resource and a disk resource where SSIS config file will be placed (We will look about config file in the next step)

Go to C:\Program Files\Microsoft SQL Server\110\DTS\Binn\ copy MsDtsSrvr.ini.xml to the drive(Drive must be a part of SQL Server cluster role) and folder you have selected, I have chosen T:\SSIS.
Open this file in notepad modify <ServerName>.</ServerName> to <ServerName>SQLServer1\TEST2012</ServerName> with the SQL Server instance name in the cluster and <StorePath>..\Packages</StorePath> to some UNC path on the clustered drive if you store packages to file system and save the file. Sample file below
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="" xmlns:xsi="">
    <Folder xsi:type="SqlServerFolder">
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
Go to Registry and Update the value of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\ServiceConfigFile to the UNC path and file name of the service configuration file on the select clustered disk. In my case T:\SSIS\MsDtsSrvr.ini.xml
Once the registry is updated SSIS has been successfully updated to cluster. Try starting SSIS resource in the Cluster and try failing over to other nodes on the cluster.


Thursday, 13 June 2013

PowerShell to Script SQL Database Object and ReApply on a different Server

This week I had a requirement to move SQL 2012 database to a SQL server 2008 R2 server and the two servers reside in a different domain with no trust. Since SQL 2012 backups are not compatible with lower version servers the only way to move the databases was to script database with and apply the script on the destination, doing this manually took a lot of time so I decided to automate this process in PowerShell.
I took MSDN blog as reference for scripting a database object using SMO in PowerShell and created my script the database using EnumScript() function instead of Script() function to accomplish my requirement. 

1.       ScriptDB.ps1 will script all the objects with data and will save it in a folder with ServerName->DBname. I have base path to save the script file is U:\, If you want to save It another location just change the following variable in the first line of the script $script:basepath = "U:\" to wherever you like or you can get the value as an argument.  The Script will create a folder in< ServerName>-><Dbname> and sub-folders Tables,Views,SP and Functions in the <dbname> Folder. 

2.       ReApply.ps1 will reapply the scripts saved in step one on the destination server. You have to pass the base folder path as Argument.

Script Database (ScriptDB.Ps1) -
ReApply Database (ReApply.Ps1) -
Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Thursday, 6 June 2013

Create Availability Group in SQL Server 2012 Step by Step

The following are the steps needs to create an Always on Availability Group.

The SQL Instance used for this exercise are SQL Server1 and SQLServer2.
I going to use 3 user databases (ContainedDB, ReportServer and AGTest) in my SQLServer1 for creating Availability group

1.       Take a Full Backup followed by T-Log backup of all the databases on the primary server (SQLServer1) which needs to be included to availability group.

BACKUP DATABASE [ContainedDB] TO DISK = 'T:\Backup\ContainedDB_full.bak' WITH INIT,STATS = 1
BACKUP DATABASE [ReportServer] TO DISK = 'T:\Backup\ReportServer_full.bak' WITH INIT,STATS = 1
BACKUP DATABASE [AGTest] TO DISK = 'T:\Backup\AGTest_full.bak' WITH INIT,STATS = 1

BACKUP LOG [ContainedDB] TO DISK = 'T:\Backup\ContainedDB_trn.bak' WITH INIT,STATS = 1
BACKUP LOG [ReportServer] TO DISK = 'T:\Backup\ReportServer_trn.bak' WITH INIT,STATS = 1
2.       Restore the Full backup and Log backup taken in step1 in NORECOVERY mode on the secondary server(SQLServer2), You can ignore Step 1 and 2 and choose Availability group wizard to take the backup and restore but I find this way quicker and efficient.

RESTORE DATABASE [ContainedDB] FROM DISK = 'H:\Backup\ContainedDB_full.bak'
WITH MOVE 'ContainedDB' TO 'G:\Data\ContainedDB.mdf',
MOVE 'ContainedDB_log' TO 'P:\TLog\ContainedDB_log.ldf',NORECOVERY,STATS = 1
RESTORE LOG [ContainedDB] FROM DISK = 'H:\Backup\ContainedDB_trn.bak' WITH NORECOVERY,STATS = 1

RESTORE DATABASE [AGTest] FROM DISK = 'H:\Backup\AGTest_full.bak'
WITH MOVE 'AGTest' TO 'G:\Data\AGTest.mdf',
MOVE 'AGTest_log' TO 'P:\TLog\AGTest_log.ldf',NORECOVERY,STATS = 1


RESTORE DATABASE [ReportServer] FROM DISK = 'H:\Backup\ReportServer_full.bak'
WITH MOVE 'ReportServer' TO 'G:\Data\ReportServer.mdf',
MOVE 'ReportServer_log' TO 'P:\TLog\ReportServer_log.ldf',NORECOVERY,STATS = 1

RESTORE LOG [ReportServer] FROM DISK = 'H:\Backup\ReportServer_trn.bak' WITH NORECOVERY,STATS = 1

3.   Connect  to SSMS of SQLServer1 expand AlwaysOn High Availability-> Right Click on the folder ->select New Availability Group Wizard

4.  Give a name to the Availability Group you want to create and Click Next, 

5.  Select the databases which need to be a part of Availability group and click next

6. Click Add Replica and connect to the secondary server, repeat the step if you want to add more than one replica.

7. I always keep the secondary databases read-only, select appropriately in Readable secondary column.

8. Select the Backup preferences menu on the page and select the appropriate backup preference, I leave it as default(Prefer Secondary)

9. Listeners can be created after the AG is set up.

10. Since we have restored the databases in secondary earlier I select Join option.

11. Click Next

12. Click Next

Availability group has been created now; let’s go back to the server to check it,

As shown in the picture above Availability group AGTest can be seen in both Primary and secondary server with corresponding Replica Details.
Now let’s see how to create a listener for the Availability group, Go to AlwaysOn High Availability -> AGtest->Availability Group Listeners; Right Click on the folder and select Add Listener.

Now fill the port number and Listener group Name(Note:- This Listener Group name and Port number are the details which will be used to connect to SQL Server by the users)


I usually chose Static IP Network mode to avoid any firewall and network issues, you can use DHCP if you like.
Click OK

Now that the Listener AGTestInstance has been created let’s see how users need to connect to AGTest.
In SSMS connect to the <ListenerName,Port Number> as the server name and connect to the AG.