How to Implement Policy Based Management: A Primer in SQL Server 2008

Introduction

Policy Based Management was previously known as the Declarative Management Framework (DMF) and has made its name by ensuring compliance inside your SQL Server databases.Why Compliance? Today organizations are facing challenges for staying compliant. You either need tools, or the knowledge to use those tools, in order to ensure compliance in your company’s infrastructure. Globally, compliance is considered a serious business as it helps prevent unauthorized access to the resources and people that use the company’s infrastructure. Let’s take a look at a figure below: From here on I will be referring to Policy Based Management as PBM. For implementing PBM, you will need to understand the following three components: At the foundation of it all are facets. Facets are basically a sub-system of pre-defined database and server properties. Facets A facet can have multiple properties. There are more than 70 facets in SQL Server 2008 as you can see from the figure below: For example, the database facet contains multiple properties - one of them is the Owner property. Once chosen this property will return the owner of the database. Conditions A condition(s) validates a facet or a series of facets. PoliciesPolicies implement conditions based on the facets defined. ImplementationWe shall now create a new database called SQLServerClub and apply a policy that ensures that the Database Recovery model is set to FULL Recovery: Click on Start > All Programs > SQL Server 2008 > SQL Server Management Studio Now create a new database called SQLServerClub and click on Ok. You should now have a database SQLServerClub as shown in the figure below: Navigate to the Management folder and expand it. PBM is present at the first instance. You will need to identify if it’s enabled or disabled. The figure below indicates when PBM is disabled: The figure below indicates when PBM is enabled: Now first we need to understand which facet enables us to check the Database Recovery Model. As you can see below the Database Maintenance Facet contains the Recovery Model: Now right click on Conditions > Click New Condition Now in the General section, enter the name for the condition: Name: DB Recovery Model Compliance Choose a Facet: Database Maintenance Expression: Recovery = FULL Now we need to give this condition a description in the event the compliance is not met: You can locate the new condition under the Conditions Folder: Now that we have our condition in place, we will create a Policy that evaluates this condition. Right Click on the Policy folder and choose New Policy: Now give the Policy name "Database Recovery Model". Choose the condition: DB Recovery Model Compliance. Now under against targets > Choose Every Database The Evaluation Mode can be of two types: On Demand (Default) and On Schedule Leave the default as On Demand. Server Restriction: None Under the Description page leave the Category as default. Description: You can write any custom message you wish to convey to the user. Under the Text to Display: You can write a custom message saying to either contact the DBA or look at a specific URL of the organization that defines these policies: Now it’s time to evaluate our policy to ensure it is working :-)! For demonstration purposes, I intentionally made the SQLServerClub Database Recovery mode toSimple. To evaluate this, right click on your policy and choose Evaluate. You should get an error as shown below: Now click on the View hyperlink to investigate the reason for the compliance failure. As you can see, the expected value was Full and the actual value was Simple. Now let us get our database into a compliant state. To do this, click on the checkbox of the policy and click on Apply. You will now get a warning, asking if you wish to apply the policy: Click on the Yes button. Then you will get a check mark as shown below: Right click on the SQLServerClub database and choose Properties. You should then navigate to the Options Page and see that the Database Recovery Model is now set to Full: You have successfully implemented PBM :-)! There are many other things to explore. In the forth coming series, I shall explore Business Requirements implementation using PBM. Stay tuned! Please send me your valuable feedback to anil.mahadev2009@live.com

How to Install SQL Server 2005 - A Quick Guide

Purpose: To describe how to perform a basic installation of SQL Server 2005.Version Information: Although this document addresses SQL Server 2005 Developer Edition, the instructions are very similar or the same for other editions of SQL Server 2005. Important Notes: Before installing SQL Server 2005, SQL Server 2000 databases should be backed up (if they are required), and the SQL Server 2000 software should be completely un-installed through the Control Panel, UNLESS there is a technical requirement for it to exist on the same machine.

Although SQL Server 2000 and 2005 can exist side by side on the same machine, they have to be installed as separate named instances. This document does not attempt to address the details of SQL Server named instances – if in doubt, consult the DBA for the project for which the installation is being performed.Should a user require SQL Server 2000 AFTER SQL Server 2005 has been installed, it can be installed as a separate named instance, however a check should be made to ensure that any critical applications that require SQL Server 2000 are capable of supporting named instances – not all are.

1. Insert the DVD into the DVD drive and double-click on the icon:

2. The installation screen should appear:

For most installations, if installing on standard 32 bit Windows XP Professional, click on the x86-based operating systems option.

3. Click on Server components, tools, Books online and samples:

4. Read the terms and conditions of the license agreement and click the check box to accept them:

Then click Next.

5. The Installing Prerequisites screen appears:

Click on the Install button and wait whilst the system components are configured. This may take 10-20 seconds.

6. Once the prerequisites are installed, click on the Next:

7. Wait for a few seconds for the following screen to appear, and then click on Next:

8. The System Configuration Check screen appears:

If the configuration check completes successfully (Success appears in the top panel, all ticks in the Details panel), click on Next.

9. Fill in the Registration Information details:

Click on Next.

10. Select the components to install:

Note that the components selected in the screen shot above are the most common components that will be needed for most developers and development DBAs. However, if another component such as Analysis Services is needed, it can be added easily afterwards by re-inserting the installation DVD and re-running the setup program.Click on the Advanced button to open up further options.

11. The advanced Features Selection screen opens:

IMPORTANT NOTE As a general rule it is not necessary to change the default installation path or the Developer Edition installation, unless your organization has a standard which is different. If in doubt, check an existing PC or server at your site.Click on Next.

SQL Server Club Membership Benefits Articles written by experienced DBAs and developers Membership is free forever Get your content published - articles, news, blogs, events No spam - guraranteed! As a bonus and to thank you for supporting us, we'll give you a 20% discount off all Norb Technologies tools. Join today to take advantage of these benefits - join SQL Server Club now.

12. Select the Default instance:

IMPORTANT NOTE If the Default instance radio button is disabled, this probably means that there is another, default instance of SQL Server 2000 or SQL Server 2005 already installed on this machine. In this case, consult the DBA for the project as it may not be necessary or desirable to re-install the database components.Click on Next.

13. Select the radio button "Use the built-in System account":

The first step will change the Windows account under which the SQL Server service executes to the Local System account. In a production environment or certain development environments, it may be desirable to change this to a domain user account, but this is not usually necessary for local developer installations. Note that the service account settings can be changed through the Services applet in the Control Panel at any point after installation, however SQL Server will require re-starting in order for these changes to take effect.Click on Next.

14. Change Authentication Mode to Mixed Mode and set a password for the sa logon. The user’s NT logon name could be used as an initial password, but note that whatever is used, it should NOT be the same for each machine as the sa account gives sysadmin privileges to any user who uses it:

Click on Next.

15. Keep the default collation settings, UNLESS the DBA for the project specifies that they should be changed. Be aware that certain applications require non-default settings:

Click on Next.

16. Select Error Reporting and Usage options – it does not matter what these are set to, as they are completely at your discretion:

Click on Next.

17. The pre-installation screen will now appear:

Click on Install.

18. The Setup Progress screen will appear, informing you of installation progress:

This step usually takes 10-30 minutes, but requires no user intervention - so now's a good time to take a break and make some coffee.

19. The Setup steps have completed:

Click on Next.

20. The "Completing Microsoft SQL Server 2005 Setup" screen will now appear:

If you only want the database server to be accessible from the machine on which it is installed, click on Finish. SQL Server 2005 installation is now complete.However, if you wish the database server to be accessible from other machines, click on the Surface Area Configuration tool link.

21. The "Help Protect Your SQL Server" screen will appear:

Click on the Surface Area Configuration for Services and Connections link.

22. The SQL Server 2005 Surface Area Configuration screen will appear:

Click on the Remote Connections node in the left hand tree pane, then click on Local and remote connections. By default, TCP/IP only is selected and is usually sufficient in most development environments, but if you suspect that you may need SQL Server 2005 to host legacy applications which require support for the named pipes SQL Server communication protocol, click on “Using TCP/IP and Named Pipes”. If in doubt, consult the DBA for the project.In addition, if you wish this installation of SQL Server to automatically announce itself on the network to automatic database server directory queries, click on the SQL Server Browser Service, set the Startup type to ‘Automatic”, click on “Apply”, then click on the Start button. Click on OK, and then close the “Help Protect Your SQL Server” screen. Finally, click on the Finish button on the “Completing Microsoft SQL Server 2005 Setup” screen. SQL Server 2005 setup is now complete.