Automate SQL Server Management

I have been working for a while in administering and developing for SQL Server, MSDE, even with the new SQL Server Express. I have put in place a resident database to store tools I use to perform administrative tasks and statistical collection tasks. I also have an automated system to run them for me in appropriate situations as well as an automated notification system to notify me when something is wrong. All this for free. I can define the structure I use and share with you some of the things you can do using this approach. As a start this article presents the base of the automation.

Presentation

I have been working for a while in administering and developing the database side of many applications for companies in the software development market. Enough to know that it is always important to limit costs of any kind as much as possible. This is to be able to offer affordable products in a competitive market.

I have put in many efforts to limit costs in the past by developing in house automation to control change management for development as well as putting in place automated performance maintenance routines to control all aspects of SQL Server health and behavior once our products get installed for a customer. All this of course, keeping in mind SQL Server compatibility issues between versions and flavors. Since Microsoft came up with MSDE it opened an even more cost effective possibility to us. Now, the opportunity to sell application products with a free relational database system exists. But when your applications does not come with a full blown SQL Server enterprise version it also means that there is no in house DBA that you can rely on to maintain your application performance to an acceptable level. And your reputation can suffer.

In the past I always relied on SQL Agent to be the motor for all the automation I needed for the SQL Server databases. This was always a good choice for me because I could also maintain any SQL Server versions and flavors using this automation. Now that SQL Express features (and the lost features from this version) are well known, we must face the fact that we will not get SQL Agent for free anymore.

Command Line Limitations

Running SQL Server tasks or scripts in the Windows Scheduler context might seems easy at first thought but not so once you come to think of it. The Windows scheduler has its limitations. It will run a command, executable or batch file. But it has some difficulties dealing with the apostrophes needed when paths contains spaces and with batch files. It also has a limited buffer to store the command and path. When the time comes to set parameters to control behavior of the scheduled configured call it can become a limitation pretty fast. I also prefer to use the old AT command instead of the new SCHTASKS command. This was to keep compatibility with older OS versions. Yes, there is still some Windows NT4 and Windows 2000 systems out there. I also found out that SCHTASKS had some sort of a bug with apostrophes, especially when you try to create the schedule task using the command line options.

Security issues

Another thing that has to be a concern is security rights. We are talking about administrative tasks, saving them using a SQL Server context external scheduler. To work the SQL Server service user running the service must be at least System/NTAuthority.

Object context

If you want to use this approach to manage your SQL Server instances I suggest you create a dedicated user database of your own to store all the necessary tables and scripts of the tools you will be using.

The CreateSchedule Procedure

The procedure CreateSchedule.prc

takes the following input parameters.

• @CommandLine = The path and the command to execute

• @TimeToRun = The time (hh:mm) the command will be executed

• @RunEvery = The frequency

• @RunNext = to run only once at a further date

• @ComputerName = To create the schedule on a remote computer (Rights issues)

• @JobType = A way to classify the different type of schedule tasks created.

Most user inputs are validated and they go like this:

Commands for the command line parameter must contain the path to the command if not in the PATH environment user variable. If it is a batch or command file it must start with “cmd /c” for the scheduler to open a DOS window to run it. When a space is present within the path it must be enclose in apostrophe. Time to run is expressed as I>hours:minutes in 24-hour notation (that is, 00:00 [midnight] through 23:59) or using “am pm” annotation. Run Every and Run Next are mutually exclusive. To run command on every specified day or days of the week or month (for example, every Thursday, or the third day of every month). Specifies the date when you want to run the command. You can specify one or more days of the week (that is, type M,T,W,Th,F,S,Su) or one or more days of the month (that is, type 1 through 31). Separate multiple date entries with commas. The computer name will be the computer on witch the schedule will be created. By default the machine on which SQL Server is installed on. The JobType is to have a basic grouping for the schedules. For more details see the procedure script and the AT command help.

You will see inside the procedure script that some table objects get created if they are missing. Those are to store information about the Schedule created by this tool. Another use of those tables exists in the context of a Unified Database Tool Kit. Basically it can be used to compare existing schedules with their original commands. Recreate schedules if they were manually deleted from the Schedule Tasks Window or simply to save tasks IDs per Job Type so they can easily be modified or deleted as a group. You will also see that many of the scripts concern data entry validation.

The DropSchedule Procedure

The procedure DropSchedule.prc takes the following input parameters.

• @JobID = The IDnumber of the schedule you want to drop. No default to make sure there will be no deletion of all schedules at once...

The JobID is the number used to form the Schedule name. Because we use the AT command the Schedule name starts with “At” then the Schedule ID number.

Now you have procedures you can use within any SQL Server context to manage Windows Scheduler tasks. In a future article I will explain to you how I generate script files on the fly as well as batch files to run them. Once done the only thing left to do is use the Create Schedule procedure to execute at off peak or the appropriate scheduled time. The application example I intend to use is a simple backup plan implementation.