Browse DevX
Sign up for e-mail newsletters from DevX


ADS Enables "Hands Off" SQL Server Installs : Page 4

Does setting up SQL Server on multiple servers mean running setup.exe over and over? No, it doesn't. With the ADS Controller, you can deploy SQL Server to many servers at once, without having to visit each machine individually.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Wrapping It All Together
My previous DevX article provided the framework for performing some standard SQL style tasks, such as configuring the advanced options in SQL Server. Because I believe in code reuse, I am using the same scripts again! Except this time I wrap them up in an ADS job sequence.

You may be wondering why you would do this when you can simply run the script on the server. Well, my observant reader, you would have to run these scripts one by one against every server you are building. By using the ADS Agent, you can execute the scripts against as many servers as you wish—all at once if you want! Now that sounds like my type of administration.

In this job sequence, you execute two of the scripts: SQLReconfigure.cmd and DropDatabases.cmd. These scripts demonstrate the framework for the job sequence, which in turn gives you the ability to expand and modify them to your needs. In fact, you can extend the sequencing file to include as many additional scripts as you like.

I've written this job sequence to be a standalone sequence. However, you could easily incorporate it into the other job sequences so that you get a complete custom installation of SQL Server, right from start to finish!

This job sequence will copy the script files to the local machine, set up the environment variables for the script, and then execute the script. The environment variable and script execution are done in a single task, as the variables are local only to the command being executed (i.e., not system-wide).

Job 3: SQLReconfigure.xml

<!-- Code provided by DevX (http://www.devx.com) as part of an article ************************************************************************************* Job Name: SQLReconfigure.xml Job Description: Executes scripts to reconfigure SQL Server Job Purpose: Uses already written scripts to reconfigure SQL Server Version: 1.1 Last Updated By: Rob Hawthorne on 20th August 2004 Version Info: Added the ability to use the ADS Agent tool
/bmonitor/bmfilexfer.exe Notes: The job first downloads the script files to the target
machine and then runs them against SQL Server. Usage: Register this job in ADS, and then run against a device
to reconfigure SQL Server Input Parameters: Return Values: ************************************************************************************* --> <sequence version="1" description="Reconfigure SQL Server" command=""
xmlns="http://schemas.microsoft.com/ads/2003/sequence"> <task description="Download File (SQL Reconfigure Cmd)" doesReboot="false"> <command>/BMONITOR/BmFileXfer.exe</command> <parameters> <parameter>-d</parameter> <parameter>"\\IDSTEST01\SrcTree\Scripts\SQLReconfigure.cmd"</parameter> <parameter>"\device\harddisk0\partition1\Windows\Temp\SQLReconfigure.cmd"</parameter> </parameters> </task> <task description="Download File (Drop Databases Cmd)" doesReboot="false"> <command>/BMONITOR/BmFileXfer.exe</command> <parameters> <parameter>-d</parameter> <parameter>"\\IDSTEST01\SrcTree\Scripts\DropDatabases.cmd"</parameter> <parameter>"\device\harddisk0\partition1\Windows\Temp\DropDatabases.cmd"</parameter> </parameters> </task> <task description="Execute SQL Reconfigure Command" doesReboot="false"> <command delivery="none" target="device">.\cmd.exe</command> <parameters> <parameter>/v:on /c set UseTrustedConn=Yes && OptionsToSet='recovery interval',
'3';'max server memory', '2900000' && start /wait /B
%SYSTEMDRIVE%\Windows\Temp\SQLReconfigure.cmd</parameter> </parameters> </task> <task description="Execute Drop Databases Command" doesReboot="false"> <command delivery="none" target="device">.\cmd.exe</command> <parameters> <parameter>/v:on /c set UseTrustedConn=Yes && DBsToDrop=Pubs;Northwind && start
/wait /B %SYSTEMDRIVE%\Windows\Temp\DropDatabases.cmd</parameter> </parameters> </task> </sequence>

Note: Be careful when using the ADS Sequence Editor against the XML job sequences. Whenever the ADS editor finds an "&&" it turns it into "&&". This is due to the ampersand being a character that needs escaping in valid XML. Consider using a simpler editor like Notepad to ensure that your XML is as you expect it.

And there you have it! You have now incorporated some core tasks you should achieve before completing your SQL Server install—and all without having to touch the server. Consider which other tasks you wish to achieve and then think about extending the job sequence to include them.

As an additional tip, if you run the ADS Agent as "localsystem" and would like to see the code executing on your server (by default, it executes as a background task), you can modify the ADS Agent service by selecting the action "Allow service to interact with the desktop" on the Log On tab (see Figure 2).

Figure 2: Allow Service to Interact with the Desktop

This allows the LocalSystem account to interact with the desktop, enabling you to see the command window pop-up with the commands you issued from the ADS Controller to the agent. Be cautious though: this can be a security issue (or so I've heard!).

The Possibilities Are Practically Endless
What you can do with ADS and a scripted "hands-off" configuration of SQL Server is almost unlimited. By using unattended installations and configuration scripts, you can quickly recover your server in the event of a failure, or just simply reduce the overhead in building multiple servers—especially in development environments where changes happen often!

Using ADS as a free management tool affects a significant change in the way you handle the Windows environment, and it allows administrators a lot more flexibility and control of their servers. So use it!

Rob Hawthorne is a senior consultant for Microsoft Consulting Services (MSFT) in Wellington, New Zealand. Rob works extensively with Microsoft's tools and technologies and has implemented a wide range of solutions for his customers.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date