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 (http://technet.microsoft.com/en-us/library/ms190362.aspx ) 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 (http://technet.microsoft.com/en-us/library/ms176034.aspx ).

 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>%')
BEGIN
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
END

 

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)
As
(
Select name, type, sid from sys.database_principals where type = 'S' AND name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA','sys')
)
select a.*,'ALTER USER '+a.name+' WITH LOGIN ='+a.name As Fixusers from login_CTE a
LEFT JOIN sys.server_principals b ON a.sid = b.sid
where b.name 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)
As
(
Select name, type, sid from sys.database_principals where type = 'S'
)
select *,'ALTER USER '+a.name+' WITH LOGIN ='+a.name As Fixusers from sys.server_principals a JOIN login_CTE b ON a.sid <> b.sid and a.name = b.name COLLATE Latin1_General_CI_AS
GO

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.
 


 

 


 

Step2:-
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,
 
 
select
a.counterid,CounterDateTime,b.ObjectName,b.CounterName,b.InstanceName,DefaultScale,CounterValue from CounterData a
JOIN
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 http://www.sqlservercentral.com/articles/Database+Mail/74429/ 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 ( http://www.sqltechnet.com/2013/06/powershell-to-script-sql-database.html ), 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 http://geekswithblogs.net/darrengosbell/archive/2010/02/22/ssas-automating-the-scripting-of-an-ssas-database.aspx 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

2. REAPPLYAS.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.