Use a local SQL Server maintenance plan to maintain SQL Database

On-premise SQL Server provides a maintenance plan to maintain SQL Server on a regular, scheduled basis. As a general rule, define SQL Server Agent jobs, and the maintenance plan helps us Execute SQL Server Agent jobs on a regular, scheduled basis. Unfortunately, SQL database does not provide maintenance plans and SQL Server agent functionality.

However, according to MSDN, we can still connect to SQL Database through a maintenance plan provided by SQL Server. (Microsoft Azure SQL Database does not a support SQL Server Agent or jobs.) You can, however, run SQL Server Agent on your on-premise SQL Server and connect to Microsoft Azure SQL Database. http://msdn.microsoft.com/library/azure/ee336245.aspx#sqlagent). The following are the specific practices:

The sqlcmd command can be called through SQL Server Agent, and then we can specify the server that performs the sqlcmd command. This allows us to configure a SQL Server Agent job to invoke sqlcmd to maintain SQL Database.

The SQL Server Agent job is configured in the following manner (SSMS 2012):

1. Create a new SQL Server Agent Job:

2. Configure the job Steps:

3. Create a new job Step,step type Select operating System (CMDEXEC):

4. Configure SQLCMD. In this example, I tried to remove data from my SQL database, named people, with an ID greater than 13, under the HOLTESTDB databases, in sqlcmd. Then this command is:

Sqlcmd-u xxxxx-p xxxxxx-s xxxxx-d holtestdb-q "Delete from people where ID > 13"

Note that the parameters in the above example have the following meanings:

User name for-u:sql database

-P: Password

-D: Database name

-q:sql statements

-S: Server DNS name. You can find it through the admin portal, such as:

5. Test if SQLCMD is valid (note: This step is not required). The purpose of the test is to verify that the commands we configured are actually valid. However, I did not want to delete the data during the test, so I changed the sqlcmd command (delete to select):

Sqlcmd-u xxxxx-p xxxxxx-s xxxxx-d holtestdb-q "SELECT * from people"

In the Command Line window, enter the command above and get the following result, stating that the command can work:

6. Set the SQLCMD command for the agent job and Save:

7. Run Agent job: Select the job you just created, right click, select Start job at Step ...

8. Successful operation:

9. The 5th step validates the following results and discovers that the success of the ID greater than 13 is deleted:

In addition, we can also put your SQL script in the. sql file, then sqlcmd adjusts accordingly as follows (the previous example, the SQL statement is specified by the-q parameter; The following example executes the SQL script file with the-i parameter):

Sqlcmd-u xxxx–p xxxx-s xxxxx-d holtestdb-i "D:\MaintainSqlAzure.sql"

For more information about SQLCMD users, please refer to:

sqlcmd Utility

How To:connect to Azure SQL Database Using sqlcmd