Manage SQL Server on Windows Azure with local SQL Manage Sutudio

Manage SQL Server on Windows Azure with SQL Manage Sutudio

When it comes to SQL creation on Windows Azure, which we've covered in our previous article, what we're going to cover today, we found that there is no SQL Server Agent option feature under SQL Database created on Windows Azure. I believe that everyone has used the SQL Server Agent-related functions, the specific functions will not have to say. Take me, because we have a service data is written to SQL Server, we are locally through the planning of people to achieve data retention for 60 days, and then through the Web query, so it is more convenient, more efficient, if the database more data, the slower the search, so I insist on using SQL Scheduled maintenance tasks for the server. In addition, I wrote the log of multiple services to SQL, almost hundreds of thousands of per day, but interested in some of the content, want to remove some of the keywords from the database, so I can only end the SQL Agent under the scheduled task. I thought, that if there is no graphical interface on Windows Azure, there should be a command! Finally, by understanding that the functionality of SQL Agent job is not supported on Windows Azure, after listening to the heart of a cool more than half cut, what good way to go through with windows People on Azure understand that there's another way to do a scheduled task maintenance on SQL on Windows Azure, no matter what, as long as there is a way to do it, today, I will share this with you, through the local manage Studio to create a new schedule to remotely maintain SQL data on Windows Azure.

On the official website, the database on Windows Azure does not support proxies.

Http://msdn.microsoft.com/library/azure/ee336245.aspx#sqlagent

We operate today, we need SQL statements to remotely manage SQL data on Windows Azure, what is the statement, and what is the format? Let's just say it again.

What we're using is :sqlcmd statements.

sqlcmd-u xxxxx-p xxxxxx-s xxxxx-d holtestdb-q "SQL Statement "

-u:sql Database the user name

- P: Password

- D: Database name

-q:sql Statement

For example: I tried to delete my SQL database through SQLCMD, holtestdb the data in the table named people, with IDs greater than 13. Then this command is:

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

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

In fact, we mainly remember the following format is possible: We will be in the back of the two real columns

Sqlcmd-u User name-p password-s server name-d database name-Q "SQL statement"

Environment Introduction:

SQL Server: wrllkpz6kb.database.chinacloudapi.cn

Database: Domino

Table: Logs

Login Name: Wenlong

Password: Password8

Today mainly introduces two main names:

Deletes the specified data under the specified field in the database;

Note that the% (percent sign) application in the SQL statement, to delete the range of data, you need to add a% percent sign to apply, but there is no space between the data and%, in the statement, the space is also a space,

1. Delete from Domino where sevent like ' SMTP server:% '

2. sqlcmd-u wenlong-p password8-s wrllkpz6kb.database.chinacloudapi.cn-d domino-q "Delete from Domino where sevent Like ' SMTP server:% ' "

Delete data in the database except for 60 days:

1. Delete FROM [domino].[ Dbo].logs where DATEADD (month,6,log_date) < getdate ();

2. sqlcmd-u wenlong-p password8-s wrllkpz6kb.database.chinacloudapi.cn-d domino-q "Delete from [Domino]. [Dbo].logs where DATEADD (month,6,log_date) < getdate ();

"

1. We need to open SQL Manage Studio locally, and then expand SQL Server Agent, new job

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://img1.51cto.com/attachment/201407/30/451336_1406708559bHly.png "height=" 280 "/>

The job name can be written according to your environment. Confirm

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://img1.51cto.com/attachment/201407/30/451336_1406708560v5M1.png "height=" 331 "/>

2. Select step: Name at will, type--operating system (if you remotely manage Windows Azure SQL by using the local manage studio, you must select the type-operating system.) ), and then in the command input box, enter the command you want to execute.

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://img1.51cto.com/attachment/201407/30/451336_1406708560hj1m.png "height=" 356 "/>

For the convenience of testing, I temporarily changed the SQL statement to select * from logs and then to the console to see if the command took effect.

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://img1.51cto.com/attachment/201407/30/451336_14067085606py5.png "height=" 341 "/>

Before I do this, I'll take a look at the table data under my database through manage studio.

Select * from logs

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://img1.51cto.com/attachment/201407/30/451336_1406708560fsst.png "height=" 263 "/>

To determine if my command is valid, I copy the code first and then execute the test results at the command line.

sqlcmd-u [email protected]-P password8-s wrllkpz6kb.database.chinacloudapi.cn-d domino-q "SELECT * from Logs"

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://img1.51cto.com/attachment/201407/30/451336_1406708560HNkr.png "height=" 524 "/>

If the above interface is present, then the remote command is executed successfully. In our own environment, we can replace the statements that need to be executed.

We can then write the command to the SQL language.

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://img1.51cto.com/attachment/201407/30/451336_1406708560ttGX.png "height="/>

After saving, we right-click the new job----job start step and start testing

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://img1.51cto.com/attachment/201407/30/451336_1406708563cTBH.png "height=" 301 "/>

After testing, the job can run normally.

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://img1.51cto.com/attachment/201407/30/451336_1406708564qHCM.png "height=" 369 "/>

Since it is homework, we need to set up a timing plan, edit the job, and then-plan, according to your own time can be set. Save

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://img1.51cto.com/attachment/201407/30/451336_1406708564CbuU.png "height=" 361 "/>

If you need to create more than one program, we can make a new step under the steps, or in the first SQL statement, separate multiple SQL statements with semicolons . You can enter multiple statements under double quotation marks, separated by semicolons (;).

Sqlcmd-u wenlong-p password8-s wrllkpz6kb.database.chinacloudapi.cn-d domino-q "SELECT * from Logs;select * from othe R Tablse "

This article from "Gao Wenrong" blog, declined reprint!