There are a number of SQL Agent Jobs baked into the vCenter Server installation for your back end SQL database. These jobs help prune and maintain your vCenter database as it grows over time. Normally you’d never really notice these jobs because the vCenter Server installation process will automagically create them on your behalf. However, whenever you wish to migrate your database to a new server, it’s important to also create the jobs to maintain a healthy database.

A few reasons why you might migrate the database to a new server:

You’re using a local copy of SQL Express on the vCenter Server, and wish to put the database on a dedicated server
The database server currently hosts a number of other databases, and you have a use case to isolate the vCenter database onto a dedicated server
It’s time to upgrade the version of Windows that the database server is running upon, perhaps even to support 64 bit operations
It’s time to upgrade your database server to a new database instance version using a fresh installation on another server

Here’s a quick and handy way to use SQLCMD to script out your SQL Agent Job creation.

SQL Agent Jobs

First, take a look at your current database server’s list of jobs by way of SQL Server Management Studio (SSMS). They are listed under SQL Server Agent > Jobs. Depending on your version, you may have anywhere from 8 to 10 jobs for your vCenter database. Each job name ends with the name of your vCenter database, which is “VCDB” for my lab as shown below.

SQL Server Agent Jobs for vCenter Server

Your new database server will lack these jobs, and it’s important to create them. Fortunately for you, VMware has provided a full list of queries that you can use to re-create the jobs on another server. They are found in the directory where vCenter Server is installed, which is typically:

C:\Program Files\VMware\Infrastructure\VirtualCenter Server\sql

[symple_box color=”yellow” text_align=”left” width=”100%” float=”none”]
Note: On older installs of vCenter, such as 4.1, the jobs were found in the root of the VirtualCenter Server folder.
[/symple_box]

Once you’ve identified the folder, use the search box to filter out the jobs. Every job file begins with the word “job” and ends with the database type: mssql, db2, and oracle. I use the filter “job*mssql” to find job queries that can be used for Microsoft SQL Server, as shown here:

Once you’ve isolated out the correct job files, make sure to copy them somewhere that the SQL Database Server has access to, or just directly to the SQL server itself (this is what I typically do). In this example, I’ve created a folder on my SQL server called “SQL Server Agent Jobs” and copied all 10 of my job files to it.

The vCenter SQL jobs are copied to the SQL Server

It’s now time to create your SQLCMD script to build out the jobs using those query files.

SQLCMD Script

SQLCMD should have already been installed on your SQL server, with the correct path entry already existing for your command prompt. If not, check with your specific installation of SQL to find out what component is missing – I am not covering that portion here.

The syntax to use for your script is as follows:

sqlcmd -S <sql_server> -d <database> -i "<path_to_sql_script.sql>"

Where:

-S specifies the SQL Server name, which is “SQL” for my lab
-d specifies the database name, which is “VCDB” for my lab
-i specifies the path to the .sql query file, which is “E:\SQL Server Agent Jobs” in my lab

Create an entry for all of your SQL query files, which is around 8 to 10 files depending on what version of vCenter you are using. I’m on vCenter 5.1, which uses 10 files. The script should look roughly like this when completed:

[sourcecode language=”sql”]sqlcmd -S sql -d VCDB -i "E:\SQL Server Agent Jobs\job_cleanup_events_mssql.sql"
sqlcmd -S sql -d VCDB -i "E:\SQL Server Agent Jobs\job_dbm_performance_data_mssql.sql"
sqlcmd -S sql -d VCDB -i "E:\SQL Server Agent Jobs\job_property_bulletin_mssql.sql"
sqlcmd -S sql -d VCDB -i "E:\SQL Server Agent Jobs\job_schedule1_mssql.sql"
sqlcmd -S sql -d VCDB -i "E:\SQL Server Agent Jobs\job_schedule2_mssql.sql"
sqlcmd -S sql -d VCDB -i "E:\SQL Server Agent Jobs\job_schedule3_mssql.sql"
sqlcmd -S sql -d VCDB -i "E:\SQL Server Agent Jobs\job_topn_past_day_mssql.sql"
sqlcmd -S sql -d VCDB -i "E:\SQL Server Agent Jobs\job_topn_past_month_mssql.sql"
sqlcmd -S sql -d VCDB -i "E:\SQL Server Agent Jobs\job_topn_past_week_mssql.sql"
sqlcmd -S sql -d VCDB -i "E:\SQL Server Agent Jobs\job_topn_past_year_mssql.sql"[/sourcecode]

You can now either change the text file to a batch file (.BAT) or just open an elevated command prompt and paste in the script. I typically just paste it into a command prompt to prevent anyone from accidentally running the script later, but I’m also working in other people’s environments. Either way is fine.

Using an elevated command prompt to add the SQL Agent jobs

That’s it!

Testing a SQL Agent Job

I tend to like kicking the tires on one of the jobs to ensure it was imported successfully. First, open up SSMS and look at the list of jobs. Make sure they all have a name that ends with the name of your vCenter database (VCDB for me). Then, try running one, such as the Event Task Cleanup job. To do this, right click on the job and choose “Start Job at Step…” to ensure it is successful.

Thoughts

While you are certainly welcome to open up each of the SQL query files in SSMS and execute the queries individually (making sure to change the target database to your vCenter database), that’s a very manual process prone to human error. It’s much easier and cleaner to use a SQLCMD script, and can also be recycled if you ever need to do the process again.