Archive for the ‘SQL Server’ Category

This past weekend I was rebuilding a storage array to increase the storage capacity and also reconfigure the drives to optimize the arrangement for better performance on our SQL Server.  The original arrangement was two Dell PowerEdge 1950 servers connected to a  Dell MD3000 Storage array.  This setup was using Windows Server 2003 Clustered for High Availability.  We had a single Raid 5 array in the MD3000 and on it were three partitions, one for the Quorum, One for Data and one for Log files.

Our new configuration was designed to increase the drive space and also split off the partitions onto separate raid arrays.  So, we created a RAID 10 for the Log Files, a RAID 5 for the Databases, and then another Raid 10 which contained the Quorum partition and also a partition for the Temp DB.

When we brought up the server after the reconfiguration we discovered that the Cluster Service would not start.  This was due to the fact that the Quorum drive had been moved and was no longer at the location that Cluster Service expected it.

After several minutes of searching I found this TechNet article http://technet.microsoft.com/en-us/library/cc738770(v=ws.10).aspx.  Basically, in order to start up the Cluster Service without the Quorum resource you need to edit the service and put in a switch.

Edit the ClusterService and add “/fixquorum” as a startup parameter and then start the service.  Once the service has started up with the /fixquorum option you can then run clusterrecovery.exe.  Clusterrecovery.exe will allow you to substitute a new disk for the Quorum disk.  Once you have done that and its replaced the quorum disk you will then be able to restart your ClusterService with the /fixquorum option and it should start up correctly.



Read Full Post »

Have you ever had to run a job in SQL Server Agent and needed it to run with different permissions than the login that runs SQL Agent?  I have come across this a few times.  Most recently, I was trying to run a SSIS Package as a SQL Agent Job and one step of the job was trying to launch WinSCP as part of an “Execute Process Task”.  The task was able to run and could launch WinSCP but it was running with the credentials as the SQL Agent Service.

The problem with that was that WinSCP stored their sessions in the registry as part of HKey\Current_User.  So, when the job launched using the SQL Agent Service Account, it could not access the values in the registry because it didn’t have the stored session in its hive.

Now, I know there are a few ways to fix this issue but I wanted to choose setting up a SQL Proxy.  A SQL Server Agent proxy gives the SQL Server Agent access to the security credentials for a Microsoft Windows User, which is to say it allows the Agent to impersonate a user when it runs that step of the job.

There are a few steps to using a Proxy.  First you need to set up Credentials in SQL Server Management Studio.  A credential is a record that contains the authentication information for connecting to resources outside of SQL.

Usually credentials consist of a Windows login name and password.

Here are the steps to create a credential.

In SSMS Object Explorer open the Security folder and right click on Credentials and choose “New Credential.

Once you open the New Credential window you are given the option to Name the credential. This can be anything you want to call it. Its for your reference. Choose something that will make sense to you.

Then in the Identity field type the name of the account you are going to use. I used a domain account so when I filled it in I put the format DOMAIN\UserName.

Then I typed the password and typed it again to confirm and clicked OK.

Now you are ready to use these credentials in creating a Proxy.

In Object Explorer scroll down to the “SQL Server Agent” and expand the folder.

Then go to Proxies.  In my example I need to be able to run command line applications so I choose the “Operating System (CmdExec) Proxy.


Right click on the proxy you want to use and choose “New Proxy”.

Once again you are given a window where you enter the name, and description for the new proxy.  Then you select the “Credential name”  by clicking on the three dots.


After that you just select the options for what this proxy is allow to access.  A good explanation of what each subsystem is can be found here http://msdn.microsoft.com/en-us/library/ms187100(v=sql.105).aspx

Once you have clicked OK you should now be able to use the Proxy in your SQL Agent Job.


Just go to the pull down for “Run As:” and select your new Proxy.
That is all there is to it.  Now your SQL Agent Job will run that step using the credentials you’ve supplied.



Read Full Post »

Using sFTP in SQL Server SSIS

On several occasions I have had to use SSIS to export data from a SQL database, and then transfer that data to a third party via sFTP.  SQL Server SSIS has an FTP task built in but it does not natively support sFTP.  There are several commercial products (Eldos Software, /n Software, CozyRoc) out on the market that can be used to add that functionality into SSIS but I am going to talk about using a free utility called WinSCP to accomplish this task.

The first step is to download and install WinSCP, you will need to do this on the SQL server that will be running the SSIS package.  Once you have installed WinSCP you should create a connection to the distant sFTP server in WinSCP so you can test and confirm that you can connect to it.  Create and name a saved session so that you can call that session from your script later.  I will name this session “MySecureFTP”.


Once you have done that you will need to create a WinSCP script to transfer your files.  For examples of scripts see the WinSCP documentation.  Here is a very basic script that I created to copy a csv file.

option batch on

option confirm off

open MySecureFTP

put c:\outbound\*.csv /inbounddirectory/


After testing the script by running it from a command prompt and confirming that it works, you are ready to step into SSIS and call it from your workflow.

In Microsoft Visual Studio in your SSIS Package, go to the Control Flow tab and from your toolbox add an “Execute Process Task” to the workflow.  It should look like this after you’ve added it.


Double click the Execute Process Task to open the Task Editor.  You should now have a window like this.


Go to the Process section on the left and then you will need to fill out some information.


In the executable line you will need to fill out the correct path to the WinSCP executable.  Mine was in the (x86) directory but it may be different for your computer.

Then in the Arguments line you will need to put the /script= switch and then the path to that script.  I also included the /log= switch to log the execution to a file.  That way I can go back and review the FTP connection and make sure everything was a success. 

You may need to add an entry for the WorkingDirectory if you do not use absolute paths in your script.  I didn’t require it. 


Now you can build your SSIS package and deploy it.  That’s all that you need to do. 


One last comment that I’ve found is that if you’ve created a stored session in WinSCP it stores the value in the registry under the HKey\Current_User folder and depending on what credentials you use to run the SQL Job you may not have access to that registry key.  One option to fix this is to switch WinSCP to use an INI so all the stored sessions are stored in the INI.  That way the script can still access the stored session.  Another way to fix this is to set the SQL Job to run as a different user but that involves setting up a Proxy in SQL Server Agent which I will cover in another post.    

Read Full Post »

This is a script I wrote to use when I need to get the quarter begin and end dates for SQL.

declare @date datetime

declare @begdate datetime

declare @enddate datetime

set @date = getdate()

--get first day of CURRENT qtr 

set @begdate = dateadd(q, datediff(q, 0, @date), 0)

--get last day of CURRENT qtr 

set @enddate = dateadd(q, datediff(q, 0, dateadd(q, 1, @date)), -1)

select @date as today, @begdate as QtrBegin, @enddate as QtrEnd

Read Full Post »

Once you have kicked off your report subscription you might want to see the status. Again, if you don’t want to log onto the SQL Server Reporting Services web interface you can do this with a simple SQL Query.

/*Active (Running) subscriptions*/



rs.scheduleid AS jobid,



FROM reportserver..catalog c

INNER JOIN reportserver..subscriptions sub
ON (c.itemid = sub.report_oid)

INNER JOIN reportserver..activesubscriptions asub
ON (sub.subscriptionid = asub.subscriptionid)

INNER JOIN reportserver..reportschedule rs
ON (sub.subscriptionid = rs.subscriptionid)

This query will return the Report Name, the JobID, description and the LastStatus.

Read Full Post »

The previous post showed a sql script that is useful for finding all your Reporting Services jobs. Now that you have that information, lets say you’d like to kick off a scheduled report that either already ran, or you’d like to run again but you don’t want to have to step through all the settings for that subscription in the Reporting Services web interface. Instead you can use the Job ID that you got in the previous result set and use it to start the report job. I use this script every month to manually kick off a scheduled report that generates client invoices.

/*  Report Server Rerun subscription  */

--Execute Monthly Web Statement Subscripion

EXEC msdb..Sp_start_job @job_name = ‘A251BE76-0453-4A8F-8C98-69439B881B23’

--Client Holdings

EXEC msdb..Sp_start_job @job_name = ’57AD46EF-A46F-41A7-83F3-9430B51CEDE8′

--Standard subscription

EXEC msdb..Sp_start_job @job_name = ‘DC1C9E50-D18E-4A69-9452-2274D21232D7’

Read Full Post »

This script is a useful one that I have at my office.  I use it often to find all the SQL Server Reporting Services scheduled reports that exist on a SQL Reporting Server instance.  It list the job name, job description, the scheduled date and time and the status of the last time it was run.

/*Information about the subscriptions on your Report Server*/

SELECT c.name,

       rs.scheduleid as JobID,






FROM   reportserver..catalog c WITH(nolock)

       INNER JOIN reportserver..subscriptions sub WITH(nolock)

         ON ( c.itemid = sub.report_oid )

       INNER JOIN reportserver..reportschedule rs WITH(nolock)

         ON ( c.itemid = rs.reportid

              AND sub.subscriptionid = rs.subscriptionid )

       INNER JOIN reportserver..schedule sch WITH(nolock)

         ON ( rs.scheduleid = sch.scheduleid )

       INNER JOIN msdb..sysjobs sj WITH(nolock)

         ON ( CAST(rs.scheduleid AS SYSNAME) = sj.name )

       --sysname equivalent to nvarchar(128)

       INNER JOIN msdb..sysjobschedules sjs WITH(nolock)

         ON ( sj.job_id = sjs.job_id )

       INNER JOIN msdb..sysschedules ss WITH(nolock)

         ON ( sjs.schedule_id = ss.schedule_id )

-- WHERE c.Name like ‘ISS %’
-- A good way to filter by report name.


Read Full Post »