My Favorite Blogs

Tuesday, July 27, 2010

Microsoft SQL Server 2008 R2 Best Practices Analyzer how to audit or analyze SQL Server Configurations.

What is Best Practices Analyzer (BPA)?
Microsoft Best Practices Analyzer is a diagnostic tool which uses to collect information about a physical Server and SQL Server instances. The tool will verify the settings and configurations that are according to the recommended best practices by Microsoft. This tool will lists all configurations and settings differ from the best practice recommendations and list potential problems in the installed SQL server instance and recommend the resolutions for the problems.

Microsoft SQL Server 2008 R2 BPA is pre-requisite installed Microsoft Baseline Configuration Manager (MBCA V2) framework and PowerShell V2.0 on either on Windows Server 2003/2008/2008 R2 or Windows Vista or Windows Vista operating systems, MBCA is a new framework comes with Windows Server 2008 R2 and Windows 7 versions and it is a new standard and utilizes a model architecture made up of xml and PowerShell scripts. It supports both local and remote execution of BPA against both local and remote servers. We can also use both GUI and command line scanning abilities.

This version of BPA supports the SQL Server Database Engine, SQL Setup, Replication, SSAS, SSRS and SSIS as well. This tool will give a proactive advice to the SQL Users about the settings of an installed SQL instance.

How to Install?
Make sure the Windows PowerShell installed on the server, the SQL 2008 BPA R2 is built on the MBCA V2 framework, so install the MBCA V2 first then install the SQL 2008 R2 V2 on the server. It has simple steps to install the tool and nothing specific to specify while install.

How to Analyze?

Microsoft Baseline Configuration Analyzer 2.0 (MBCA) is the interface for SQL 2008 R2 Best Practices Analyzer (BPA). Go to Start, All Programs , click on MBCA 2.0, you will get the GUI wizard to execute the SQL Server 2008 R2 BPA.

In the Select a Product Combo box select “SQL Server 2008 R2 BPA” (that would be the only one listed in) by default it will be connected to the local SQL instance to analyze and if needed you can connect to a remote computer by clicking “Connect to Another Computer” link on the right side of the combo box. Click on the “Start Scan” link just below the combo box (See Figure 1) to scan the SQL configurations as per Microsoft’s Best Practice Analyze standards.
 
Figure 1

When you click on the “Start Scan” the next screen will be popup to enter parameters to do the analysis. Enter the Physical Server name, SQL instance name (if differed from server name else same) in the text boxes and check the option boxes to scan the configurations to analyze (See Figure 2).
Figure 2

Click on the “Start Scan” hyper link and scanning will be started and in progress. (See Figure 3)

Figure 3

 Once the scanning process completed then you can see the Baseline Configuration Analyzer Report, on the report page you can find two tabs Non-complaint and all (see Figure 4), the Non-complaint tab will list all the errors and warnings. The below errors says that the backup of the databases in the instance are outdated mean that there is no recent backup for the databases. The Replication “Replication is not configured” error shown up because there is no replication configured in the instance but I selected the option to scan the replication settings.

Figure 4

On the All tab you can find the analyze results on each categories (see Figure 5)

Figure 5

I intentionally copied a backup file on the data drive before the scan process and it caught this as an issue and specified as “Database files and backup files exists on the same volume” and also given Impact and Resolution for the issue. (See Figure 6)

Figure 6 

Conclusion
The BPA tool analyze the recommended best practices by Microsoft to bring the SQL Server up to the standard to avoid any configuration or settings issues in an environment. The noted feature for SQL Server 2008 R2 BPA is able to scan both the local computer and remote computers.

Download
BPA: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591
MBCA: http://www.microsoft.com/downloads/details.aspx?familyid=1B6E9026-F505-403E-84C3-A5DEA704EC67&displaylang=en

Saturday, July 17, 2010

SQL Server 2005 - DBMail

Steps to Activate DBMail in SQL Server 2005/2008

1. Enable DBMail XPs

USE [master]
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'Database Mail XPs', N'1'
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO

2. Create DBMail Account

USE [master]
GO
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA' ,
@email_address = 'DBMail@
sqldbops.com' ,
@display_name = 'DBMail' ,
@description = 'Mail account for administrative e-mail' ,
@mailserver_name = 'mail.eweb.sqldbops.com' ,
@port = 25
, @use_default_credentials = 1


3. Create DBMail Profile

USE [master]
GO
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'DBAProf',
@description=N'Profile used for administrative mail'

4. Add Account to the profile created in step-3

USE [master]
GO
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'DBAProf', @account_name=N'DBA', @sequence_number=1

5. Verify if profile created successsfully

SELECT * FROM [msdb].[dbo].[sysmail_profile] as p JOIN [msdb].[dbo].[sysmail_profileaccount] as pa
ON p.profile_id = pa.profile_id

6. Add profile to Public. If you declare as Public, you don't have specify the profile when sending mails using sp_send_dbmail proc. If declared Private, then profile should be specified

EXEC msdb.dbo.sysmail_add_principalprofile_sp @principal_name=N'guest', @profile_name=N'DBAProf', @is_default=1

6. Update Registry for SQLServer Agent:(Useful for Cluster env)

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'DBAProf'

You may not find ‘SQLServerAgent’ key entry on the registry in SQL Server 2005 because registry entries keys totally changed.
But the above query will work successfully(0 rows affected) in SQL Server 2005 and finally enables DBMail for SQL ServerAgent by locating to the following registry(9.0)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft SQL Server\MSSQL.1\SQLServerAgent

7. Verify if Value shows 1 to ensure AgentXps are enabled

USE [master]
GO
select value_in_use from sys.configurations where configuration_id = 16384


8. Create an Operator

USE [msdb]
GO
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=0,
@email_address=N'surpal@sqldbops.com',
@category_name=N'[Uncategorized]'

9. Add Operator to all jobs using the script..

DECLARE @JobName SYSNAME, @job_id UNIQUEIDENTIFIER, @NotifyEmail INT, @SQL NVARCHAR(3000)


SELECT JobName = '%'
DECLARE Job_operator_cursor CURSOR FOR
SELECT name,job_id ,notify_level_email FROM msdb.dbo.sysjobs_view AS sv

OPEN Job_operator_cursor

FETCH NEXT FROM Job_operator_cursor INTO @JobName, @Job_Id, @NotifyEmail
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobs_view WHERE notify_level_email = 2 and name LIKE @JobName)
BEGIN
PRINT 1
SELECT @SQL = 'msdb.dbo.sp_update_job @job_name=N'''+@JobName+''',
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N''DBA'''

PRINT @SQL
EXEC sp_executesql @SQL
END

FETCH NEXT FROM Job_operator_cursor INTO @JobName, @Job_Id, @NotifyEmail
END

CLOSE Job_operator_cursor
DEALLOCATE Job_operator_cursor

10. Restart SQL Server Agent

Steps to De-Activate DBMail in SQL Server 2005/2008

1. Remove Operator from all jobs using the script


DECLARE @JobName SYSNAME, @job_id UNIQUEIDENTIFIER, @NotifyEmail INT, @SQL NVARCHAR(3000)

DECLARE Job_operator_cursor CURSOR FOR
SELECT name,job_id ,notify_level_email FROM msdb.dbo.sysjobs_view AS sv

OPEN Job_operator_cursor

FETCH NEXT FROM Job_operator_cursor INTO @JobName, @Job_Id, @NotifyEmail
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs_view WHERE notify_level_email = 2 and name = @JobName)
BEGIN
PRINT 1
SELECT @SQL = 'msdb.dbo.sp_update_job @job_name=N'''+@JobName+''',
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N'''''

PRINT @SQL
EXEC sp_executesql @SQL
END

FETCH NEXT FROM Job_operator_cursor INTO @JobName, @Job_Id, @NotifyEmail
END

CLOSE Job_operator_cursor
DEALLOCATE Job_operator_cursor


2. Drop DBMail Profile

EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name=N'DBAProf', @force_delete=True


3. Drop DBMail account

EXEC msdb.dbo.sysmail_delete_account_sp @account_name=N'DBA'


4. Remove the entry from DBMail Registry for SQLServer Agent

EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N''
GO


You may not find ‘SQLServerAgent’ key entry on the registry in SQL Server 2005 because registry entries keys totally changed.
But the above query will work successfully(0 rows affected) in SQL Server 2005 and finally removes DBMailprofile for SQL ServerAgent by locating to the following registry(9.0)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft SQL Server\MSSQL.1\SQLServerAgent

5. Remove Operator from SQL Server Agent

EXEC msdb.dbo.sp_delete_operator @name=N'DBA'

6. Disable DBMail XPs

USE [master]
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'Database Mail XPs', N'0'
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO