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

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

TDE and Tempdb

If you use TDE in any of your SQL instances, then the Tempdb also automatically gets encrypted on that instance. This could have a performance effect for the other unencrypted databases. Is this true or false?

Answer this question here

Token-based server access validation failed with an infrastructure error - MS SQL Server 2008 on Windows 2008 Server

Issue

Recently I faced login failure issue while logging into a newly built MS SQL Server 2008 instance on Windows Server 2008 R2 server. I’ve ensured that I’m part of the local administrators group in the server even though I’m getting the login failure and SQL didn’t allow me to connect to the instance through SSMS (SQL Server Management Studio).

SQL ErrorLog
2010-05-24 11:27:55.480 Logon Login failed for user 'SQLDBOps\Krishjay'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]

Event Log

+ System
- Provider
[ Name] MSSQLSERVER
- EventID 18456
[ Qualifiers] 49152
Level 0
Task 4

Keywords 0x90000000000000

- TimeCreated
[ SystemTime] 2010-05-24T18:15:34.000000000Z
EventRecordID 2166
Channel Application
Computer SQLDBOPs
- Security
[ UserID] S-1-5-21-606947145-1563985344-839528115-120123
- EventData
SQLDBOps\Krishjay
Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.
[CLIENT: ]

Reason for the issue:

After going through couple of forums and knowledge base articles then I understood that Windows 2008 new Security feature UAC (User Access Control) is blocking me to login to the SQL Instance.

What is User Access Control?

UAC is a new Security feature introduced in Windows Server 2008 (also applies to Windows Server 2008 R2, Windows 7 and Windows Vista). When an administrator logs on to a computer running Windows 2008, the user’s full administrator access token is split into two access tokens: a full administrator access token and a standard user access token. During the logon process, authorization and access control components that identify an administrator are removed, resulting in a standard user access token. The standard user access token is then used to start the desktop, the Explorer.exe process. Because all applications inherit their access control data from the initial launch of the desktop, they all run as a standard user as well.

So the UAC is not passing all my group membership credentials when I log on to the server that’s why I do not have privilege to connect the SQL instance on SSMS. Here if I add my login account explicitly in SQL level then I can connect to SQL without any issues. I mean the Standard user credentials (as per UAC) are not sufficient to connect the SQL instance on Windows 2008 server or you can say that the local admin group membership credentials are not inherited with my account.

Workaround:

To resolve this issue, there are two options, one is open the SSMS with option “Run as Administrator” and the other is “Disable UAC” in the box. Again the answer is depends on the environment and management decision how it needs to be.

Note: Run as Administrator is user level settings (and can be configured for all the users in the box) and Disable UAC is for all users and disabling the server protection from the malwares and viruses.

Run As Administrator Settings

Start --> All Programs-->Microsoft SQL Server-->SQL Server Management Studio -->Right Click --> Properties

The SSMS properties window will pop up and change the settings on the compatibility tab according to your environment.
 

1. Check “Run this program as an administrator” on Compatibility page if you are the only user using SSMS on this box.

2. If this setting required for other admin users on the box then Click on the “Change Settings for all users” button

3. And Check “Run this program as an administrator” for all users.

This will allow opening the SQL Server management studio for all the administrators on the box without any login issues.

Disable UAC:

Go to Command prompt and run the below command to disable the UAC

Reg.exe ADD HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System /v EnableLUA /t REG_DWORD /d 0 /f

Other way of doing the same is Go to Registry Editor

Start --> Run -->Type RegEdit and hit Enter key


In the Registry Editor, Navigate to the below folder

HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System

Right click on System folder and place the mouse pointer on New --> click on "DWORD Value" (New Value will be created under System) -->Rename it as "EnableLUA" and default value will be 0 leave as it is.

Then restart your Computer
Now you will start to get nagging prompts to re-enable UAC, if needed you can disable these prompts.
Click the bubble
At the side, it should say “Change the way Security Center alerts me”, Click that.
Click “Don’t notify me and don’t display the icon”

Now you have disabled UAC but keep in mind that you have disabled some of your protection against spyware and viruses.

Reference:

http://technet.microsoft.com/en-us/library/cc709691(WS.10).aspx
http://technet.microsoft.com/en-us/library/cc731416(WS.10).aspx
http://windows.microsoft.com/en-ZA/windows7/what-is-user-account-control
http://windows.microsoft.com/en-ZA/windows7/turn-user-account-control-on-or-off

MS SQL Server 2008 Administration Exam - Free from BrainBench

BrainBench announced the release of their new MS SQL Server 2008 Administration test. This certification test is available for free on their site for a limited time as they gather data to monitor its performance.

 
Be among the first to take this test and measure your level of expertise with the new content. These tests are developed as pre-employment assessments where MS SQL Server 2008 Administration is used significantly in the candidate’s job role. Once again, when helping with this test validation, completion of the test offers free certification

To take the test, go to http://www.brainbench.com/xml/bb/common/testcenter/taketest.xml?testId=2831 You may have to register if it is your first time taking IT tests with Brainbench.

Take some time and explore our entire library of free tests. Forward this message and challenge your colleagues to top your score!


Good luck!

How to create Policies using PowerShell scripts

Introduction

As you know that SQL Server 2008 supports Windows PowerShell and is a powerful scripting shell that helps administrators to automate and built robust server administration scripts. In my previous articles I’ve written about Using SQL Server 2008 PBM, how to monitor Free Space of data file(s) and How to Evaluate Policies on Multiple Instances using PowerShell scripts. In this article I’m going to walk you through how to create policies using PowerShell script.
In SQL 2008, policies can be created in two ways. One is using SSMS (SQL Server Management Studio) wizard and second is using PowerShell script. In my scenario I’ve been requested to create this script to deploy/create policies as a POST installation step. Suppose if you need to use this script for unattended SQL installations then make sure that this script called only after a complete SQL installation done on a server, because you need a SQL policy store to create your policies.Read More

Microsoft SQL Server 2008 R2 top 10 features

 Hey folks be prepare to read more about new enhancements in SQL Server 2008 R2 edition, a lot of attention has been given to the new database engine and BI features to landing on  the ground with SQL Server 2008 R2 edition. I’ve listed the top 10 features/enhancement here for your reference… Sure you will feel it is amazing…

1.       Multi-server management

2.       Data-tier applications (DAC)

3.       Master Data Services

4.       StreamInsight

5.       SSMS enhancements for SQL Azure

6.       Parallel Data Warehouse

7.       Enhanced scale-up capabilities

8.       PowerPivot for Excel and SharePoint

9.       Reporting Services improvements

10.   Report Builder 3.0

1.       Multi-server management

Multi-server management utility is a new feature in MS SQL 2008 R2 edition; it is very useful for DBAs to monitor their SQL instances from one instance. This feature named as UCP (Utility Control Point) you can setup this Multi-Server Management feature using View Menu à Utility Explorer. This feature is available only in SQL 2008 R2 and this will not support earlier versions.  Read more

2.       Data-tier applications (DAC)

DAC is a new feature in SQL Server 2008 R2, it is focused for database deployment best practice, DAC is a self-contained unit of deployment that enables data-tier developers and DBAs to package the SQL Server objects, including database and instance objects, into a single entity called DAC package. Developers can build a DAC package using the Data-tier Application project system in Visual Studio 2010, whereas SQL Server Management Studio (SSMS) users can extract a DAC (reverse engineering) and generate a DAC package file for an existing database. In SSMS you can find Extract Data-tier Application  (right click on database àTasksà Extract Data-tier Application) open which can be used to do reverse engineer your database (typically generate the script for all objects in a database) and can be deployed on different instance (Eg. Dev environment to Prod) using Deploy Data-tier Application (right click on Instance name àDeploy Data-tier Application option) Read More

3.       Master Data Services

MDS (Master Data Services) is a feature in BI platform which helps an Enterprise to manage centralized master data entities and hierarchies. With MDS, people across the enterprise can contribute to and consume master data securely and as needed, without having to go through IT and MDS provides web-based access to the master data hub for browsing, editing, authoring, auditing, and approving master data. Read More

4.       StreamInsight

MS SQL Server 2008 R2 StreamInsight feature is focused for event-driven applications where you need low latency requirements. What StreamInsight does is allow organizations to make decisions on streaming data in near real-time. An event-driven application use CEP (Complex Event Processing) technology and is different from traditional databases, and the new component could make SQL Server a better fit for areas like financial services and web analytics. Developers can develop the CEP applications using C# and .NET environments. StreamInsight implements a lightweight streaming architecture that supports highly parallel execution of continuous queries over high-speed data. Read More

5.       SSMS enhancements for SQL Azure

The November CTP version of SQL Server Management Studio from SQL Server 2008 R2 and SQL Server 2008 R2 Express can be used to access, configure, manage and administer SQL Azure. Previous versions of SQL Server Management Studio are not supported. Read More

6.       Parallel Data Warehouse

MS SQL Server 2008 R2 PDW (Parallel Data Warehouse) (previously code named Project Madison) allows data warehouses to be scaled over several physical servers that deliver really high performance through a MPP (Massive Parallel Processing) architecture at a low cost on Windows 2008 R2 with industry-standard hardware. “In symmetric multi-processing (SMP) architecture, query processing occurs entirely within one physical instance of a database. CPU, memory and storage impose physical limits upon speed and scale.” A SQL Server 2008 R2 Parallel Data Warehouse MPP data warehouse appliance partitions large tables across multiple physical nodes, each node having dedicated CPU, memory and storage, and each running its own instance of SQL Server, in parallel shared nothing architecture “(oh man… my head is spinning …!)Read More

7.       Enhanced scale-up capabilities

MS SQL Server 2008 R2 with Windows 2008 R2 now supports up to 256 logical processors with the new data center editions up from 64. It is a lot more in SQL and really a big step forward by Microsoft with SQL Server.  Also it supports Memory in excess of 2 terabytes. Read More

8.       PowerPivot for Excel and SharePoint

Finally the Project Gemini has been integrated with MS SQL Server 2008 R2, it officially named as PowerPivot and is integrated with SharePoint 2010. “Use PowerPivot to expand the reach of BI in your organization through familiar technologies and platforms from Microsoft.” Read More

9.       Reporting Services improvements

In MS SQL Server 2008 R2 BI brings lots of new enhancements in the existing tools which you can feel more in SQL Server Reporting Services (SSRS). SQL Server 2008 R2 version of Business Intelligence Development Studio helps lot easier to open, modify, preview, save, and deploy both earlier and later versions of reports and Report Server projects by using the later version of Business Intelligence Development Studio. Read More

10.   Report Builder 3.0

The new version of Report Builder 3.0 brings plenty of data visualization capabilities including maps, sparklines and databars (this feature not available in SQL 2008). This also brings with it new capabilities for working with gauges and charts in SQL Server Reporting Services (SSRS), and while it is part of the R2 suite of tools, the tool can be downloaded separately to run with previous versions of SQL Server as well. Read More 1 Read More 2

You may feel like I covered here very little; of course yes it is, because we can write numerous pages here to talk about the new features and enhancements in MS SQL Server 2008 R2 edition, but I’m leaving that part with you…. Enjoy…