You are the database administrator for your company. You maintain a database named Sql_ny on a SQL Server2008 instance named Sql1.
This server is located at the head office in New York. You have created 30 differentalerts on this database server.
Your company has recently opened two new branch offices in London and Paris.
The databases, named Sql_lon and Sql_par, in the London and Paris branch offices reside on SQL Server 2008 instances named Sql2 and Sql3, respectively.
The data in these databases is similar to the data stored in the Sql_ny database.
You are now required to define alerts on the Sql_lon and Sql_par databases and on the Sql_ny database.
What should you do to define the alerts for the databases in this scenario?
A.
Create an SSIS package to copy the master database from Sql1 onto the two destination servers.
B.
Create an SSIS package to copy the msdb database from Sql1 onto the two destination servers.
C.
Create a Transact-SQL script for all the alerts on Sql1 by using the Alert.Script() method, and run the scripton the destination servers.
D.
Create a Transact-SQL script for each alert on Sql1 by using SQL Server Management Studio, and run each script on the destination servers.
Explanation:
You should create a Transact-SQL script for each alert on Sql1 by using SQL Server Management Studio and runeach script on the destination servers. To copy the alerts from one database server to another, you can use SQLServer Management Studio to create scripts for the required alerts and then run these scripts on the servers towhich the alerts should be copied. To script an alert by using SQL Server Management Studio, you should perform the following steps:
1. Expand the Alerts section in SQL Server Agent after connecting to an instance.
2. Right-click the alert, point to Script Alert As , and select either the CREATE TO or the DROP TO option tocreate a script and save it in a text file or the clipboard.You should not create a SQL Server Integration Services (SSIS) package to copy the master database to the twodestination servers. The master database only contains database startup settings, such as locations of thevarious files, in the database. The master database does not contain any information about the alerts. You should not create an SSIS package to copy the msdb database to the two destination servers. Copying the msdb database to the destination servers will involve copying all the SQL Server Agent data. In this scenario,only the alerts should be copied. Therefore, you should use a method that copies only the alerts to the destinationservers. You should not create a Transact-SQL script for all the alerts on Sql1 by using the Alert.Script() method andthen run the script on the destination servers. The AlertCollection.Script() method is the correct method used tocreate a Transact-SQL script for all the alerts in the database. You can use this method to create a script for allthe alerts in the database, and then run this script on the destination servers to re-create the alerts. The Alert.Script() method is used to create a Transact-SQL script for an alert in the database.Objective:
Maintaining SQL Server InstancesSub-Objective:
Manage SQL Server Agent alerts.References:
MSDN > Developer Centers > Microsoft Developer Network > Script Method MSDN > Developer Centers > Microsoft Developer Network > How to: Script Alerts Using Transact-SQL (SQLServer Management Studio) MSDN > Developer Centers > Microsoft Developer Network > AlertCollection.Script Method