Policy Based Management in SQL Server 2008

Policy-base Management is a new feature in SQL Server 2008 that helps SQL Server administration. It allows us to define and enforce policies for configuring and managing SQL Server across the enterprise. The policy-based framework implements the policies behind the scene with a Policy Engine, SQL Server Agent jobs, SQLCLR, DDL triggers and Service Broker. Policies can be applied or evaluated against a single server or a group of servers.

To understand PBM we have to understand the below terms.
Target - an entity that is managed by PBM (i.e. database, table, index, etc.)
Facet - a predefined set of properties that can be managed
Condition - a property expression that evaluates to facets on target and returns True or False;
Policy - a condition to be checked and/or enforced

Expand the Management Node to use the PBM.



Expand the facets node to checkout the facets.


To Check out facets properties double click on facets. I have clicked on Login Facets. These facet properties are used to specify a condition

Undocumented Server Property ErrorLogFileName

You can use Server Property errorlogfilename to get the error log file path.

SELECT SERVERPROPERTY('ErrorLogFileName')

How to Hide System Objects in Object Explorer?

Open SSMS -> GO TO Tools -> Option and Check the below highlighted check box and restart the Management Studio. It will hide all the system objects in Object Explorer.

Step By Step Log-Shipping Configuration for SQL Server

Setting up Log Shipping for SQL Server is not that difficult, but having a step by step process is helpful if this is the first time you have setup Log Shipping. In this tip we walk through the steps to setup Log Shipping.

To read the step by step Log-Shipping steps CLICK ME to read it.

Or Open http://mssqltips.com/tip.asp?tip=2301 link

Surface Area Configuration in SQL Server 2008

I got an email from one of my reader and He is asking me that where is SAC in SQL Server 2008. Microsoft has removed the Surface Area Configuration tool that was shipped in SQL Server 2005. The Services and Connections which were previously managed using Surface Area Configuration tool is now managed using the SQL Server Configuration Manager.

To manage the database engine features you have to use Policy Based Management. For that you have to use Surface Area Configuration facet.

You can follow below steps to configure the Database Engine features.

1. Right Click on Server Name and Select Facets from pop up windows


2. Select “Surface Area Configuration” from facets windows and do the appropriate desired settings

SSMS Startup Options

You can configure SSMS to startup with the below options. Default one is Open Object Explorer.

Go Tools --> Options -->Environment(page)-->General.

Options are self explanatory.

SQLWB.exe (SQL2k5) and SSMS.exe(SQL2K8)

SQLWB (sqlwb.exe) /SSMS(SSMS.exe) is the executable file that launches SQL Server Management Studio. I am usually using it to launch the SQL Server management studio instead of navigating through Start -> All Programs -> SQL Server 2005 -> SQL Server Management Studio.

Above command will launch the SQL Server 2005 Management Studio. You can launch the SQL2k8 management studio using SSMS.exe

You can also pass below list of argument with it, I am executing below command to see the list of parameters.

c:\sqlwb.exe -?

Steps to Check SQL Server Services Status

Go to All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager -> SQL Server Services

DMVs for SQL Server Cluster

sys.dm_os_cluster_nodes
This view returns a row for each node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance has been defined.

sys.dm_io_cluster_shared_drives
This view returns the drive name of each of the shared drives if the current server instance is a clustered server. If the current server instance is not a clustered instance it returns an empty rowset.

Permission
You must have VIEW SERVER STATE permission for the SQL Server instance.

SELECT *
FROM   sys.dm_os_cluster_nodes
--OR
SELECT *
FROM   Fn_virtualservernodes()

--Shared Drives
SELECT *
FROM   sys.dm_io_cluster_shared_drives 

Subscribe to SQLDBPool by SMS

Dear Readers,

By registering your mobile number, you will get SMS on SQLDBPool.com post. Click SMS icon for more information.



Thanks,
Jugal Shah

Can we restore SQL Server 2008 database to SQL Server 2005?

No we can't restore it. SQL Server is not allowing the restore of higher version databases to a lower version. It is not possible to restore a database from a backup of a newer version to older version as database backups are not backward compatible.

You can do below workaround to transfer higher version database to lower version.

1. Generate database script. Right Click database -> Tasks -> Generate Scripts



2. Execute the script on the lower version server and it will create the database and its objects

3. Transfer data between these two databases using DTS/SSIS