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.
# Script to create database mail
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 13/09/2013
# Script Help :-
#---------------
# Parameter 1 :- "SQL" to run powershell for Single server
#      "File" to give filename with list of servers
# Parameter 2 :- Server name(if -s is the parameter 1) or Filename with fully defined path(if -f is the parameter 1)
#
# Example1:- DBMail.ps1 -Mode SQL -Server servername
# Example2:- DBMail.ps1 -Mode File -Server filename with fully define path like c:\test.txt

Param(
 [Parameter(Mandatory=$True,Position=0)]
 [String]$Mode,
 
 [Parameter(Mandatory=$True,Position=1)]
 [String]$Server
     )
Clear-Host
$ChkAccount = $null
if($Mode.ToUpper() -eq "SQL")
{
 $srt = "Server Name :- " + $Server
 $srt
 Echo "---------------------------"
 $SQLSplit = $Server.Split("\")
 $SQLcompname = $SQLSplit[0]
 if($SQLSplit[1] -ne $null)
 {
  $instancename = $SQLSplit[1]
  $ProfileName = $SQLcompname+"_"+$instancename 
 }else{$ProfileName = $SQLcompname}
 $AccountName = $ProfileName
 $smtpServer = "smtp.yourdomain"
 #Connect to the local, default instance of SQL Server.
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
 #Get a server object which corresponds to the default instance
 $srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server -ArgumentList $Server -ErrorAction Stop
 #Define the Database Mail; reference it using the Server Mail property.
 if($srv.Configuration.DatabaseMailEnabled.ConfigValue -ne 1)
 {
 $srv.Configuration.DatabaseMailEnabled.ConfigValue = 1
 $srv.Configuration.Alter()
 Write-Host -ForegroundColor DarkGreen "Database Mail enabled"
 }
 $DBMail = $srv.Mail
 $ChkProfile = $DBMail.Profiles |where{$_.name -eq $ProfileName}
 $ChkAccount = $DBMail.Accounts |where{$_.name -eq $AccountName}
 #Delete existing profile
 if ($ChkProfile -ne $null)
 {
  Write-Host -ForegroundColor Red "DB Profile $ProfileName Already Exists and will be deleted"
  Try
  {
   $ChkProfile.Drop()
  } 
  Catch
  {
   Write-host -ForegroundColor Red "ERROR [DROP EXISTING PROFILE]:"$_.Exception.Message
   Exit;
  }
  Write-Host -ForegroundColor Red "DB Profile $ProfileName Dropped"
 }
 #Delete existing account
 if ($ChkAccount -ne $null)
 {
  Write-Host -ForegroundColor Red "DB Account $AccountName Already Exists and will be deleted"
  Try
  {
  $ChkAccount.Drop()
  }
  Catch
  {
   Write-host -ForegroundColor Red "ERROR [DROP EXISTING ACCOUNT]:"$_.Exception.Message
   Exit;
  }
  Write-Host -ForegroundColor Red "DB Account $AccountName Dropped"
 }
 #Create Mail Account
 $DBAccount = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailAccount -argumentlist $DBMail,$AccountName -ErrorAction Stop
 Try
 {
 $DBAccount.Description = "SQL Alert Email Account"
 $DBAccount.DisplayName = $AccountName
 $DBAccount.EmailAddress = $AccountName+"@YourCompany.com"
 $DBAccount.Create()
 }
 Catch
 {
  Write-host -ForegroundColor Red "ERROR [CREATE ACCOUNT]:"$_.Exception.Message
  Exit;
 }
 Try
 {
 $DBAccount.MailServers.Item($Server).Rename($smtpServer)
   $DBAccount.Alter()
 }
 Catch
 {
  Write-host -ForegroundColor Red "ERROR [ADD SMTP TO ACCOUNT]:"$_.Exception.Message
  EXIT;
 }
 Write-Host -ForegroundColor DarkGreen "DB Account $AccountName Created"
 #Create Mail Profile
 $DBProfile = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailProfile -argumentlist $DBMail,$ProfileName -ErrorAction Stop
 Try
 {
 $DBProfile.Description = "SQL Alert Email Profile"
 $DBProfile.Create()
 }
 Catch
 {
  Write-host -ForegroundColor Red "ERROR [CREATE PROFILE]:"$_.Exception.Message
  EXIT;
 }
 Write-Host -ForegroundColor DarkGreen "DB Profile $ProfileName Created"
 Try
 {
 $DBProfile.AddAccount($AccountName,0)
 $DBProfile.AddPrincipal("Public",$false)
 $DBProfile.Alter()
 }
 Catch
 {
  Write-host -ForegroundColor Red "ERROR [ADD ACCOUNT TO PROFILE]:"$_.Exception.Message
  EXIT;
 }
 Write-Host -ForegroundColor DarkGreen "DB Account [$AccountName] added to Profile [$ProfileName]"
}
elseif($Mode.ToUpper() -eq "FILE")
{
 $filename = $Server
 $computers = get-content $filename
 ForEach($computer in $computers)
 {
  $SQLServer = $computer -replace '^\s+', ''
  $srt = "Server Name :- " + $SQLServer
  $srt
  Echo "---------------------------"
  $Error_srt = 0
  if(($SQLServer -ne $null) -or ($SQLServer -like '^\s+'))
  {  
   $SQLSplit = $SQLServer.Split("\")
   $SQLcompname = $SQLSplit[0]
   if($SQLSplit[1] -ne $null)
   {
    $instancename = $SQLSplit[1]
    $ProfileName = $SQLcompname+"_"+$instancename 
   }else{$ProfileName = $SQLcompname}
   $AccountName = $ProfileName
   $smtpServer = "smtp.yourdomain"
   #Connect to the local, default instance of SQL Server.
   [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
   #Get a server object which corresponds to the default instance
   Try{
     $srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server -ArgumentList $SQLServer -ErrorAction Continue
     #Define the Database Mail; reference it using the Server Mail property.
     if($srv.Configuration.DatabaseMailEnabled.ConfigValue -ne 1)
     {
      $srv.Configuration.DatabaseMailEnabled.ConfigValue = 1
      $srv.Configuration.Alter()
      Write-Host -ForegroundColor DarkGreen "Database Mail enabled"
     }
    }
   Catch {Write-host -ForegroundColor Red "ERROR [CONNECT SQL $Server]:"$_.Exception.Message;continue}
   #Define the Database Mail; reference it using the Server Mail property.
      $DBMail = $srv.Mail
   $ChkProfile = $DBMail.Profiles |where{$_.name -eq $ProfileName}
   $ChkAccount = $DBMail.Accounts |where{$_.name -eq $AccountName}
   #Delete existing profile
   if ($ChkProfile -ne $null)
   {
    Write-Host -ForegroundColor Red "DB Profile $ProfileName Already Exists and will be deleted"
    Try
    {
     $ChkProfile.Drop()
    } 
    Catch
    {
     Write-host -ForegroundColor Red "ERROR [DROP EXISTING PROFILE]:"$_.Exception.Message
     Continue
    }
    Write-Host -ForegroundColor Red "DB Profile $ProfileName Dropped"
   }
   #Delete existing account
   if ($ChkAccount -ne $null)
   {
    Write-Host -ForegroundColor Red "DB Account $AccountName Already Exists and will be deleted"
    Try
    {
     $ChkAccount.Drop()
    }
    Catch
    {
     Write-host -ForegroundColor Red "ERROR [DROP EXISTING ACCOUNT]:"$_.Exception.Message
     Continue
    }
   Write-Host -ForegroundColor Red "DB Account $AccountName Dropped"
   }
   #Create Mail Account
   $DBAccount = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailAccount -argumentlist $DBMail,$AccountName -ErrorAction Continue
   Try
   {
    $DBAccount.Description = "SQL Alert Email Account"
    $DBAccount.DisplayName = $AccountName
    $DBAccount.EmailAddress = $AccountName+"@yourcompany.com"
    $DBAccount.Create()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR [CREATE ACCOUNT]:"$_.Exception.Message
    continue
   }
   Try
   {
    $DBAccount.MailServers.Item($SQLServer).Rename($smtpServer)
      $DBAccount.Alter()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR [ADD SMTP TO ACCOUNT]:"$_.Exception.Message
    Continue
   }
   Write-Host -ForegroundColor DarkGreen "DB Account $AccountName Created"
   #Create Mail Profile
   $DBProfile = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailProfile -argumentlist $DBMail,$ProfileName -ErrorAction Stop
   Try
   {
    $DBProfile.Description = "SQL Alert Email Profile"
    $DBProfile.Create()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR [CREATE PROFILE]:"$_.Exception.Message
    continue
   }
   Write-Host -ForegroundColor DarkGreen "DB Profile $ProfileName Created"
   Try
   {
    $DBProfile.AddAccount($AccountName,0)
    $DBProfile.AddPrincipal("Public",$false)
    $DBProfile.Alter()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR [ADD ACCOUNT TO PROFILE]:"$_.Exception.Message
    Continue
   }
   Write-Host -ForegroundColor DarkGreen "DB Account [$AccountName] added to Profile [$ProfileName]"
  
  }
 }
}
else
{
Write-Host "Incorrect Paramenter, use -SQL to specify the SQL Server Name or use -FILE to specify the path of the file containing server list" -ForegroundColor Red
$uParameterHelp = "
# Script Help :-
#---------------
# Parameter 1 :- -SQL to run powershell for Single server
#      -File to give filename with list of servers
# Parameter 2 :- Server name(if -s is the parameter 1) or Filename with fully defined path(if -f is the parameter 1)
#
# Example1:- DBMail.ps1 -Mode SQL -Server servername
# Example2:- DBMail.ps1 -Mode File -Server filename with fully define path like c:\test.txt"
Write-Host $uParameterHelp -ForegroundColor Blue
}
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

 
# Credits:- http://geekswithblogs.net/darrengosbell/archive/2010/02/22/ssas-automating-the-scripting-of-an-ssas-database.aspx
# Script to Script a Analysis Service Database
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 09/08/2013
# Script Help :-
#---------------
# Parameter 1 :- "-s" to specify the SQL Server Name
# Parameter 2 :- "-d" if only one database name to be scripted;Specify "databsename" if parameter 2 is -d
#     "-f" if a set of databases needs to be scripted;Specify "UNCFilepath of the file containing databse names" if parameter 2 is -f and "all" if parameter 2 is -f and you want to script all databses in the server
# Parameter 3:-  "-o" tp specify the UNC Folder path where output scripts nees to be stored
# Example1:- SQLASScript.ps1 -s TESTSERVER\INST1 -d TestDB -o d:\SQLAS
# Example2:- SQLASScript.ps1 -s TESTSERVER\INST1 -f C:\TestDBnames.txt" -o d:\SQLAS
# Example3:- SQLASScript.ps1 -s TESTSERVER\INST1 -f all -o d:\SQLAS

############################################
$DatasourceDBEngine = "SERVER1\DBINST"  #-Relational DataSource
$DatasourceASEngine = "SERVER1\ASINST"  #-OLAP DataSource
############################################

Clear-Host
$outputFolder = $null
$dateStamp = $null
$SSASServerName = $null
$SSASServer = @()
$db = @()
$dbs = @()
$xmlfile = $null
$xw = @()
$xml =@()
$ds = @()
$splitds = @()
$dblist = @()
$xmlmain = @()
$xmltopass = @()

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")|Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml")|Out-Null

############################################################## FUNCTION TO CHANGE DATASOURCE NAME ############################################################################
Function script:changedatasource([xml]$xmlobj)
{
 $xmlmain = $xmlobj
 foreach($ds in $xmlmain.Create.ObjectDefinition.Database.DataSources.DataSource)
 {
  if($ds.type -eq "RelationalDataSource")
  {
   $splitds = ($ds.ConnectionString).split(";")
   $splitds[1]="Data Source=$DatasourceDBEngine"
   $ds.ConnectionString = $splitds[0]+";"+$splitds[1]+";"+$splitds[2]+";"+$splitds[3]
  }elseif($ds.type -eq "OlapDataSource")
  {
   $splitds = ($ds.ConnectionString).split(";")
   $splitds[1]="Data Source=$DatasourceASEngine"
   $ds.ConnectionString = $splitds[0]+";"+$splitds[1]+";"+$splitds[2]+";"+$splitds[3]
  }else{Write-Host -ForegroundColor Red "Error:Unknown DataSource Type"}
  $xmlmain.Save($xmlfile)  
 }
}

############################################################## FUNCTION TO SCRIPT THE AS DATABASE ############################################################################
Function script:writexmlfn($SSASSource,$ASDbname)
{
 $SSASServerName = $SSASSource
 $SSASServer = New-Object Microsoft.AnalysisServices.Server
 # Connect to the SSAS server
 Try{$SSASServer.Connect($SSASServerName)}
 Catch{Write-Host -ForegroundColor Red "Error:Executing $vwfile"
    Write-Host -ForegroundColor Red $_.Exception.Message
    EXIT;
   }
 if($ASDbname -eq "-all")
 {
  $dbs = $SSASServer.Databases
 }
 else
 {
  $dbs =  $SSASServer.Databases |where {$_.name -in $ASDbname}
 }
 foreach($db in $dbs)
 {
 write-Host "Scripting: " $db.Name
 $script:xmlfile = "$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla"
 $xw = new-object System.Xml.XmlTextWriter("$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla", [System.Text.Encoding]::UTF8) 
    $xw.Formatting = [System.Xml.Formatting]::Indented 
    [Microsoft.AnalysisServices.Scripter]::WriteCreate($xw,$SSASServer,$db,$true,$true) 
    $xw.Close()
 $xml = New-Object xml
 $xml.Load($xmlfile)
 changedatasource $xml
 }
 $SSASServer.Disconnect()
}

##############################################################FUNCTION MAIN()############################################################################
if($args.Length -ne 6)
{
Write-Host "Incorrect Input Paramenter" -ForegroundColor Red
$uParameterHelp = "
Help:-
******

# Script Help :-
#---------------
# Parameter 1 : -s to specify the SQL Server Name
# Parameter 2 : -d if only one database name to be scripted;Specify "databsename" if parameter 2 is -d
#    -f if a set of databases needs to be scripted;Specify "UNCFilepath of the file containing databse names" if parameter 2 is -f and all if parameter 2 is -f and you want to script all databses in the server
# Parameter 3:  -o tp specify the UNC Folder path where output scripts nees to be stored
# Example1: SQLASScript.ps1 -s TESTSERVER\INST1 -d TestDB -o d:\SQLAS
# Example2: SQLASScript.ps1 -s TESTSERVER\INST1 -f C:\TestDBnames.txt -o d:\SQLAS
# Example3: SQLASScript.ps1 -s TESTSERVER\INST1 -f all -o d:\SQLAS"
Write-Host $uParameterHelp -ForegroundColor Blue
}
<#START:Install MAIN Program#>
elseif((($args[0] -eq "-s") -or ($args[0] -eq "-S")) -and (($args[2] -eq "-d") -or ($args[2] -eq "-D") -or  ($args[2] -eq "-f") -or ($args[2] -eq "-F"))-and (($args[4] -eq "-o") -or ($args[4] -eq "-O")))
{
 $outputFolder = $args[5]+"\"
 $dateStamp = (get-Date).ToString("yyyyMMdd") 

 # Variable for your SSAS server name
 $SSASServerName = $args[1]
 
 if((($args[2] -eq "-d") -or ($args[2] -eq "-D")) -and ($args[3] -ne $null))
 {
  $dbname = $args[3]
  writexmlfn $SSASServerName $dbname
 }
 elseif((($args[2] -eq "-f") -or ($args[2] -eq "-F")) -and (($args[3] -eq "all") -or ($args[3] -eq "ALL")))
 {
  $dbname = "-all"
  writexmlfn $SSASServerName $dbname 
  
 <#$xml.Create.ObjectDefinition.Database.DataSources.DataSource.ConnectionString
 $xml.Create.ObjectDefinition.Database.DataSources.DataSource.ConnectionString = "Provider=SQLNCLI10.1;Data Source=EREIDWSDV01\DWDEV;Integrated Security=SSPI;Initial Catalog=ERSBI_Warehouse"
 $xml.Create.ObjectDefinition.Database.DataSources.DataSource.ConnectionString
 $xml.Save($xmlfile)#>
 }elseif((($args[2] -eq "-f") -or ($args[2] -eq "-F")) -and (($args[3] -ne "all") -or ($args[3] -ne "ALL")))
 {
  if(Test-Path $args[3])
  {
   $dblist = Get-Content $args[3]
   writexmlfn $SSASServerName $dblist
  }
 }
}
else
{
Write-Host "Incorrect Input Paramenter" -ForegroundColor Red
$uParameterHelp = "
Help:-
******

# Script Help :-
#---------------
# Parameter 1 : -s to specify the SQL Server Name
# Parameter 2 : -d if only one database name to be scripted;Specify "databsename" if parameter 2 is -d
#    -f if a set of databases needs to be scripted;Specify "UNCFilepath of the file containing databse names" if parameter 2 is -f and all if parameter 2 is -f and you want to script all databses in the server
# Parameter 3:  -o tp specify the UNC Folder path where output scripts nees to be stored
# Example1: SQLASScript.ps1 -s TESTSERVER\INST1 -d TestDB -o d:\SQLAS
# Example2: SQLASScript.ps1 -s TESTSERVER\INST1 -f C:\TestDBnames.txt -o d:\SQLAS
# Example3: SQLASScript.ps1 -s TESTSERVER\INST1 -f all -o d:\SQLAS"
Write-Host $uParameterHelp -ForegroundColor Blue
}

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.

# Script to Reapply scripted Analysis Service database
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 12/06/2013
# Script Help :-
#---------------
# Parameter 1 :- "-s" to specify the SQL Server Name
# Parameter 2 :- "-p" to specify folder path of the script files
# Example1:- ScriptDB.ps1 -s "SQL Server Name" -p "Folder Path"
# Example2:- ScriptDB.ps1 -s MyTestServer -P U:\SQLServer1


Clear-Host
Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
import-module "sqlps" –disablenamechecking -ErrorAction SilentlyContinue
Import-module "sqlascmdlets" -ErrorAction SilentlyContinue


<#*************************************************START:Main Program***************************************************************#>
<#Command Line Argument Verification#>
if($args.Length -ne 4)
{
Write-Host "Incorrect Paramenter, use -s to specify the SQL Server Name and -p to specify folder path of the script files" -ForegroundColor Red
$uParameterHelp = "
Help:-
******
# Parameter 1 :- -s to specify the SQL Server Name
# Parameter  :- -p to specify folder path of the script files
# Example1:- ScriptDB.ps1 -s "SQL Server Name" -p "Folder Path"
# Example2:- ScriptDB.ps1 -s MyTestServer -P U:\SQLServer1"
Write-Host $uParameterHelp -ForegroundColor Blue
}
<#START:Install MAIN Program#>
elseif((($args[0] -eq "-s") -or ($args[0] -eq "-S")) -and (($args[2] -eq "-p") -or ($args[2] -eq "-P")))
{
$SQLInstance = $args[1]
$basepath = $args[3]
 if ((Test-Path $basepath) -eq $false)
 {
   Write-host -ForegroundColor Red "Script folder specified in the Argument cannot be found"
   EXIT;
 }
 Write-Host "Creating Databases....."
 Write-Host ""
   foreach($asdbfile in Get-ChildItem $basepath)
   {
    $asdbfullpath = $basepath+"\"+$asdbfile
    Write-Host ""
    Write-Host "Executing XMLAScript " $asdbfile
    
    Try{Invoke-ASCmd -InputFile $asdbfullpath -Server $SQLInstance -ErrorAction Continue}
    catch{ 
            Write-Host -ForegroundColor Red "Error:Executing $asdbfile"
         Write-Host -ForegroundColor Red $_.Exception.Message
       }
   }
}
else
{
Write-Host "Incorrect Paramenter, use -s to specify the SQL Server Name and -p to specify folder path of the script files" -ForegroundColor Red
$uParameterHelp = "
Help:-
******
# Parameter 1 :- -s to specify the SQL Server Name
# Parameter  :- -p to specify folder path of the script files
# Example1:- ScriptDB.ps1 -s "SQL Server Name" -p "Folder Path"
# Example2:- ScriptDB.ps1 -s MyTestServer -P U:\SQLServer1"
Write-Host $uParameterHelp -ForegroundColor Blue
}
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

# Script to find OS Edition and Physical RAM
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 22/07/2013
# Script Help :-
#---------------
# Parameter 1 :- UNC File Path with Server list
# Example1:- ServerOS.ps1 "Server List File Path"

Clear-Host
$List =@()
$List1 =@()
$final = @()
$filename = $args[0]
$computers = get-content $filename
foreach ($computer in $computers)
{
if($computer -ne $null)
{
$srt = "Server Name :- " + $computer
#$srt
#Echo "---------------------------"
$List = Get-WmiObject Win32_OperatingSystem -ComputerName $computer| SELECT CSName,Caption
$List1 = Get-WMIObject Win32_PhysicalMemory -ComputerName $computer| Measure-Object -Property capacity -Sum |select @{N="Total_Physical_Ram"; E={[math]::round(($_.Sum / 1GB),2)}}
$objvalue = $List1.Total_Physical_Ram
$List|Add-Member -Name Total_Physical_Ram -MemberType NoteProperty -Value $objvalue
$final += $List
}
}
$final|ft -AutoSize

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="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>SQLServer1\TEST2012</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>T:\SSIS\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>
 
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.