Post scripts to
implement after successful SQl Server installation in a new server:
1. Listing the
advanced configuration options and RUN the reconfigure statement to install.
|
use master
go
sp_configure 'show
advanced options',1
go
reconfigure with override
go
|
2. Enable
the Database Mail XPs.
|
sp_configure 'Database
Mail XPs',1
--go
--sp_configure 'SQL Mail
XPs',0
go
reconfigure
go
|
3. Mail
account settings
|
a).Create an account for the
notifications(set the email account, mail server and user credentials as per
your environment)
EXECUTE
msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL',
@description = 'DBA Email
account',
@email_address = ' sqlalerts@gmail.com',
@display_name = ' SQL DBA
Alerts',
@username='',
@password='',
@mailserver_name = 'smtp.gmail.com'
|
|
b).Create
a new mail profile.
EXECUTE
msdb.dbo.sysmail_add_profile_sp
@profile_name = 'mssql',
@description = 'Profile used for emails in
database mail'
c).Setting the profile the default public
profile.
This
script sets the profile mssql as default public profile for users in the msdb
database.
EXECUTE
msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'mssql',
@principal_name = 'public',
@is_default = 1 ;
d).Add the mapping of profile ‘mssql’ and
account ‘SQL’.
EXECUTE
msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'mssql',
@account_name = 'SQL',
@sequence_number = '1'
--------------------------
|
4. This script sets the
operator information for DBA and operator is enabled; SQL server agent sends
notifications to the mentioned email address by pager from Monday 9AM to Friday
6PM, Saturday 9AM to 6PM and Sunday 9AM to 6PM.
|
USE [msdb]
GO
/****** Object:
Operator [DBA] Script Date: 12/21/2011 10:39:14 ******/
EXEC
msdb.dbo.sp_add_operator @name=N'DBA',
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=127,
@email_address=N'sqlalerts@gmail.com',
@category_name=N'[Uncategorized]'
GO
|
That’s all. Database mail
is enabled and ready to use by the new SQL server.
5. This script will put a
copy of the email in the sent folder for the sending email address you have
provided if it is set to true or 1
|
USE
[msdb]
GO
EXEC
msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
GO
|
6. This script will enable
the SQL Server Agent to use the Database Mail Profile by updating the settings
in the registry as shown below.
|
EXEC
master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail',
N'REG_DWORD', 1
GO
EXEC
master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile',
N'REG_SZ', N'mssql'
GO
|
7. This script will enable
the CLR integration; Sql Server stops executing all CLR routines and loads all
application domains.
|
sp_configure 'clr enabled',
1
go
reconfigure
go
|
8. Check the mail profile
and mail account is created successfully or not by the following script.
|
SELECT * FROM msdb.dbo.sysmail_profile
SELECT * FROM
msdb.dbo.sysmail_account
|
Have a Happy
post script implementation in a new SQL Server. Then consider Maintenance plan
setup in this new SQL server environment.
