Transcript Continued to next slide

Microsoft SQL Server 2008
Advance Features
Manu Kapoor
© 2010 Wipro Ltd - Confidential
Contents
1.
2.
3.
4.
5.
6.
7.
8.
9.
2
Working with Performance Counters and SQL Server Profiler (Merge Approach)
Securing data with Transparent Data Encryption
Managing Resource Pool with Resource Governor
Working with SQL Server Plan Guides
Working Change Data Capture
Working with Change Tracking
Multi Server Management using Central Management Server
Using Policy Based Management
Using PowerShell in MS-SQL Server 2008
© 2010 Wipro Ltd - Confidential
1. Working with Performance Counters and SQL Server Profiler
We all understand that SQL Profiler is a handy tool for performing workload analysis and perfmon offers
capturing OS/ SQL Server performance counters so they may be analyzed during real-time or at a later stage.
Combining Performance Counter metrics with MS-SQL Profiler workload trace provides us great
opportunity to pin-point the exact statement/ Stored Procedure or Batch that is causing high resource
utilization. Up until this point there was practically no-good-way to know which statement in user sessions is
causing the excessive resource consumption specially when there are numerous concurrent user sessions
hitting the instance.
Together with MS-SQL Server 2008 profiler and performance logs this can be easily achieved as we shall see
in this session.
Continued to next slide
3
© 2010 Wipro Ltd - Confidential
Working with Performance Counters and SQL Server Profiler Continued
Configuration Steps:
We all understand that SQL Profiler is a handy tool for performing workload analysis and perfmon offers
capturing OS/ SQL Server performance counters so they may be analyzed during real-time or at a later stage.
1. Setting-up the PerfMon
1.
2.
3.
4.
Open Perfmon, start  run  perfmon and press enter
Expand Performance Logs and Alerts
Right click on Counter logs and select New Log Settings
Add counters in General Tab, for our session we will capture
1.
2.
3.
4.
5.
4
Processor: %Processor
SQL Server Buffer Manager: Page Life Expectancy
SQL Server Buffer Manager: Buffer Cache Hit Ratio
Physical Disk: Average Disk Sec/ Read
Physical Disk: Average Disk Sec/ Write
Continued to next slide
© 2010 Wipro Ltd - Confidential
Working with Performance Counters and SQL Server Profiler Continued
2. Setting-up the MS-SQL Profiler
1.
Create a new SQL trace and add the following Events
1. T-SQL: Batch Completed
2. T-SQL: Batch Started
3. Optionally you can also select other events as well as columns to display
In the next slide, we will how to set-up the transaction workload.
Continued to next slide
5
© 2010 Wipro Ltd - Confidential
Working with Performance Counters and SQL Server Profiler Continued
3. Setting-up the Transaction Workload
We will executing some scripts to simulate the load on the server. The script will create a table, push some
data into it and while this insertion is going on we will also fire selection using NOLOCK keyword. This will
cause CPU to gain spikes, that will be captured by perfmon.
-- First Query Window
CREATE TABLE T1
(
EmpID INT,
EmpName VARCHAR(50)
)
GO
DECLARE @i INT
SET @i = 1
WHILE @i <= 100000
BEGIN
INSERT INTO T1 SELECT @i, 'Employee ' + CAST ( @i AS VARCHAR )
SET @i = @i + 1
END
6
-- Second Query Window ( Load Simulation )
SELECT COUNT(*) FROM T1 WITH ( NOLOCK )
© 2010 Wipro Ltd - Confidential
Working with Performance Counters and SQL Server Profiler Continued
4. Merging Perfmon and MS-SQL Profiler Trace
At this point both Perfmon and MS-SQL Profiler must have captured enough data for us to view and analyze.
To see the them action, perform the following steps:
1.
2.
3.
4.
5.
6.
7
Stop SQL Profiler Trace and Save the trace to some location.
Stop Perfmon Logs in the Perfmon tool
In MS-SQL Profiler window, click on File menu and open the recently saved trace file
Click on the File menu again and click on Import Performance Data
1. Select the file which you have configured for capturing the perfmon logs
2. Select counters to view (For our example, we will just pick Processor: %Processor )
You will now see both T-SQL statements and perfmon graph appearing in the cascaded window mode
As you navigate and traverse between the T-SQL statements, you will also see the changes in the Graphs
indicating the CPU spikes.
© 2010 Wipro Ltd - Confidential
2. Securing data with Transparent Data Encryption
MS-SQL 2008 offers out-of-the box feature for securing your data files and database backups and we call this
as Transparent Data Encryption (TDE).
TDE works at the storage level and has the following key properties:
1.
2.
3.
This feature is only available with Enterprise Edition, Enterprise Evaluation Edition and Developer Edition
TDE is transparent for applications because it does not require any code changes at the application level.
The data is encrypted/decrypted in the memory before going/coming from the I/O device
TDE has some additional usage over-head generally between 3%-5%. As per Microsoft, under TPC-C
workload testing, the overhead never went beyond 28%
Why to use TDE
1. The data stored in the physical database files are in clear-text, can be viewed using a common text editor.
I can’t believe this ?
2. The data stored in the database BACKUP files are also in clear-text, can be viewed using a common text
editor. I can’t believe this too ?
Continued to next slide
8
© 2010 Wipro Ltd - Confidential
Securing data with Transparent Data Encryption Continued
Setting-up Transparent Data Encryption:
We will be creating a database called “ResearchDB” that will have one table. We wish to encrypt this
database and test TDE.
Configuring TDE requires the following steps:
1. CREATE MASTER KEY (This will always be the one in your instance)
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@$$w0rd‘
2. CREATE SERVER CERTIFICATE
USE MASTER
GO
CREATE CERTIFICATE SECURE_CERT WITH SUBJECT = 'USED FOR TRAINING SESSION
DEMO'
Continued to next slide
9
© 2010 Wipro Ltd - Confidential
Securing data with Transparent Data Encryption Continued
3. CREATE DATABASE ENCRYPTION KEY
USE ResearchDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE SECURE_CERT
( Pay attention to the warning )
3. ENABLE ENCRYPTION ON THE DATABASE
ALTER DATABASE ResearchDB SET ENCRYPTION ON
Before we get onto the real actions let us check few more things:
SELECT * FROM sys.DATABASES
SELECT
DB_NAME(database_id) AS DatabaseName, Encryption_State AS
EncryptionState,key_algorithm AS Algorithm,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO
10
SELECT * FROM SYS.SYMMETRIC_KEYS
© 2010 Wipro Ltd - Confidential
Securing data with Transparent Data Encryption Continued
Before we get onto the real actions let us check few more things:
SELECT * FROM SYS.CERTIFICATES
SELECT * FROM SYS.DATABASES
SELECT
DB_NAME(database_id) AS DatabaseName,
Encryption_State AS
EncryptionState,key_algorithm AS Algorithm,
key_length AS KeyLength
FROM SYS.DM_DATABASE_ENCRYPTION_KEYS
GO
--1 = Unencrypted
--2 = Encryption in progress
--3 = Encrypted
--4 = Key change in progress
--5 = Decryption in progress (after ALTER DATABASE…SET ENCRYPTION OFF)
Continued to next slide
11
© 2010 Wipro Ltd - Confidential
Securing data with Transparent Data Encryption Continued
At this point our database (ResearchDB) is encrypted. We can perform the following tasks (Real action):
1. Take the database off-line and read the contents in NOTEPAD
2. Take the database back-up and read its content in NOTEPAD
12
Few Key Questions:
1. Can you restore the encrypted database backup on any other Enterprise edition instance ?
1. Yes you can, provided you have the valid certificate
2. Can you attach the encrypted database file to other Enterprise edition instance ?
1. Yes you can, provided you have the valid certificate
3. Can you do an IMPORT/EXPORT of the database tables which is encrypted to other instance
1. Yes, this DOES NOT need certificate. Import/Export works outside of TDE …. 
4. Can you restore the encrypted database backup on any other Non-Enterprise edition instance ?
1. NO YOU CANNOT
5. Why is it important to BACKUP the CERTIFICATE ?
1. To allow restoration on same or other instance (Of-course Ent/Dev edition only)
2. WARNING:Your database will be useless if you don’t have the back-up of the certificate and you
want to restore the database or even worse if your certificate is deleted. Look at the example in
the next slide
6. What other algorithm keys available ?
1. AES_128 | AES_192| AES_256| TRIPLE_DES_3KEY
© 2010 Wipro Ltd - Confidential
Securing data with Transparent Data Encryption Continued
(Handling a worst-case scenario)
What will happen if the certificate is removed ?
As simple as that, your database will become in-accessible. You will not be able to recover it
Issue Simulation:
1.
Ensure that you have taken the back-up of the certificate that you have used.
BACKUP CERTIFICATE SECURE_CERT
TO FILE = 'C:\Secure_Cert.cer'
WITH PRIVATE KEY ( FILE = 'C:\Secure_Cert.pvk', ENCRYPTION BY PASSWORD = 'test@123' )
2.
Issue the command DROP CERTIFICATE <CERTIFICATE_NAME>
3.
Restart MS-SQL database engine, check error log file and database state
4.
This must be looking ugly to you, the database is crashed
Solution:
1.
Restore this certificate again
CREATE CERTIFICATE SECURE_CERT
FROM FILE = 'C:\Secure_Cert.cer'
WITH PRIVATE KEY ( FILE = 'C:\Secure_Cert.pvk', DECRYPTION BY PASSWORD = 'test@123' )
2.
3.
4.
13
Restart the MS-SQL database engine service
Check your database state
It should be fine now !!!!
© 2010 Wipro Ltd - Confidential
3. Managing Resources with Resource Governor
Resource Governor as the name implies is used to manage the resources available on the server machine. We would normally
associate resources with CPU and Memory. In MS-SQL 2008, the new feature Resource Governor went beyond what is had
traditionally offered, that is it allows database administrators to manage the workloads according to the situation. The Resource
Governor has the following components:
Resource Pool: - A resource pool represents the physical resources of the server. There are two resource pool
namely internal and default which are created when SQL Server 2008 is installed. However, SQL Server also
supports the creation of user defined resource pools. In a resource pool a DBA can specify MIN or MAX value in
percentages for CPU and Memory utilization. The Internal pool basically represents the resources which are
consumed by SQL Server itself for its running. This pool cannot be altered by a user in any way. The default pool is a
predefined user pool which contains the default group. The important thing to note is that the default pool cannot be
dropped or created, however it can be altered as required.
Workload Group: - A workload group acts as a container which accepts the sessions from SQL server users, which
are similar in nature based on the classification criteria that are applied to each requests. As in Resource Pool's there
are two predefined workload groups namely internal and default defined by SQL Server. The incoming requests to the
server are classified into default workload when there is no criteria defined to classify the incoming request, or there
was an attempt made to classify the requests into a nonexistent workload group or there is a failure with the
classification
Classification: - Classifications are internal rules that classify the incoming requests and route then to a workload
group. This classification is based on a set of user written criteria contained in a scalar function which will be created
in the Master database. Once a DBA enables the Resource Governor on SQL Server then each and every single
session to the server will be evaluated by the user defined classifier function.
14
Continued to next slide
© 2010 Wipro Ltd - Confidential
Managing Resources with Resource Governor Continued
Problem:
1. We want to limit CPU % for the sessions initiated by the user ”dbadmin” to 40%
2. We want to limit CPU % for the sessions initiated by the user ”dbuser” to 10%
3. For all other users 50% CPU is allowed
Setting-up Resource Governor:
For our session we will adopt this approach:
1. Create a user with the name ”dbadmin”
2. Create a user with the name ”dbauser”
3. Resource Pool:
1. ADMINS: Min CPU 0%, MAX CPU 40%
1. Workload Group Name: GROUPADMINS
2. USERS: Min CPU 0%, MAX CPU 10%
1. Workload Group Name: GROUPUSERS
Continued to next slide
15
© 2010 Wipro Ltd - Confidential
Managing Resources with Resource Governor Continued
4.
Create a classifier function using the following T-SQL:
CREATE FUNCTION dbo.ClassifierResources()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
DECLARE @VAL VARCHAR(32)
SET @VAL = 'default';
IF 'dbadmin' = SUSER_SNAME()
SET @VAL = 'GROUPADMINS';
IF 'dbuser' = SUSER_SNAME()
SET @VAL = 'GROUPUSERS';
RETURN @VAL;
END
5. Attach this classifier function to the Resource Governor:
ALTER RESOURCE GOVERNOR
WITH ( CLASSIFIER_FUNCTION = dbo.ClassifierResource )
Continued to next slide
16
© 2010 Wipro Ltd - Confidential
Managing Resources with Resource Governor Continued
4.
Create a classifier function using the following T-SQL:
CREATE FUNCTION dbo.ClassifierResources()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
DECLARE @VAL VARCHAR(32)
SET @VAL = 'default';
IF 'dbadmin' = SUSER_SNAME()
SET @VAL = 'GROUPADMINS';
IF 'dbuser' = SUSER_SNAME()
SET @VAL = 'GROUPUSERS';
RETURN @VAL;
END
5. Attach this classifier function to the Resource Governor:
ALTER RESOURCE GOVERNOR
WITH ( CLASSIFIER_FUNCTION = dbo.ClassifierResource )
Continued to next slide
17
© 2010 Wipro Ltd - Confidential
Managing Resources with Resource Governor Continued
4. Activate the changes done to the Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE
Resource Governor in Action
At this point we have created the resource pool and is ready to be used. To se Resource Governor in action
follow these steps:
1. Open Perfmon start  run  type perfmon and press enter
2. Select SQL Server: Resource Pool Counter Class and add the counter CPU usage % and ensure
you have selected default/ internal/ ADMINS/ USERS resource pools
3. Make three NEW connections to the instance:
1. One using Administrator
2. One using dbadmin
3. One using dbuser
4. Open three New Query window for each session
5. Execute the following T-SQL for LOAD SIMULATION
6. Observe the CPU usage % for each Resource Pool
18
© 2010 Wipro Ltd - Confidential
4. Working SQL Server Plan Guides
Plan guides are used to optimize the performance of queries when you cannot or do not want to
change the text of the query directly. This can be useful when a small subset of queries in a
database application deployed from a third-party vendor are not performing as expected. Plan
guides influence optimization of queries by attaching query hints or a fixed query plan to them. In the
plan guide, we specify the Transact-SQL statement that we want optimized and either an OPTION
clause that contains the query hints you want to use or a specific query plan you want to use to
optimize the query. When the query executes, SQL Server matches the Transact-SQL statement to
the plan guide and attaches the OPTION clause to the query at run time or uses the specified query
plan.
As an example, take this query which is coming from vendor-deployed application:
SELECT * FROM <SOME_LARGE_TABLE_NAME>
This query is not making use any indexes even if they are defined and is not limiting the rows at all. In such a
situation MS-SQL Server or for that matter any other RDBMS will probably use parallel execution to derive
the results. This can hinder the performance specially when con-current user sessions are also sending their
own work-loads..
Plan guides can help-us take care of this situation by attaching OPTION ( MAXDOP 1 )
19
Continued to next slide
© 2010 Wipro Ltd - Confidential
Working SQL Server Plan Guides Continued
For our session, we will use the following method:
CREATE DATABASE ResearchDB
USE ResearchDB
-- Create the table
CREATE TABLE T1
(
EmpID INT,
EmpName VARCHAR(50),
Salary INT
)
-- Push some data into this table
DECLARE @i INT
SET @i = 1
WHILE @i <= 50000
BEGIN
INSERT INTO T1 SELECT @i, 'Employee '+ CAST ( @i AS VARCHAR ), @i * 2
SET @i = @i + 1
END
Continued to next slide
20
© 2010 Wipro Ltd - Confidential
Working SQL Server Plan Guides Continued
-- Create some indexes
CREATE CLUSTERED INDEX IDX_EmpID ON T1 ( EmpID )
CREATE NONCLUSTERED INDEX IDX_EmpName ON T1 ( EmpName )
-- Check what is the execution plan of this query, this is taken as SCAN
instead of SEEK
SELECT * FROM T1 WHERE EmpName LIKE 'Employee 1%';
-- Try using FORCESEEK (Just a test)
SELECT * FROM T1 WITH ( FORCESEEK) WHERE EmpName LIKE 'Employee 1%';
-- Creating Plan Guide
EXEC sp_create_plan_guide
@name = N'TEST PLAN FORCESEEK',
@stmt = N'SELECT * FROM T1 WHERE EmpName LIKE ''Employee 1%'';',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION ( TABLE HINT ( T1, FORCESEEK) )';
GO
21
Continued to next slide
© 2010 Wipro Ltd - Confidential
Working SQL Server Plan Guides Continued
-- Check what is the execution plan of this query AGAIN
SELECT * FROM T1 WHERE EmpName LIKE 'Employee 1%';
We can clearly see the change in the execution plan now.
Can we check it in profiler as well ?
Yes you can, you have to include Plan Guide Successful event under performance Event
Class
Controlling plan guides:
1. Viewing which plans are created in my database
1. SELECT * FROM sys.plan_guides
2. Enabling the SQL Plan Guide
1. EXEC sp_control_plan_guide 'ENABLE', @name = N'TEST PLAN
FORCESEEK’
3. Disabling the SQL Plan Guide
1. EXEC sp_control_plan_guide 'DISABLE', @name = N'TEST PLAN
FORCESEEK’
4. Dropping the SQL Plan Guide
1. EXEC sp_control_plan_guide 'DROP', @name = N'TEST PLAN FORCESEEK'
22
© 2010 Wipro Ltd - Confidential
5. Change Data Capture
Change Data Capture is yet another Enterprise Edition Feature that allows Database administrators to capture the changes
happening in the database tables (Including DDL) changes.
Change data Capture has the following key properties:
1. It uses Transaction Log File to capture the changes
2. It uses SQL Server Agent Job to traverse though the Log file and push the data into relational tables
3. It is ASYNCHRONOUS in nature (Unlike Change Tracking which works in a Transaction Mode hence SYNCHRONOUS)
4. It can only be configured using T-SQL
5. Unlike Change Tracking it also keeps values that was changed
Internally CDC uses sp_replcmds system stored procedure (This is the same procedure which built-in to MS-SQL database
engine for Transactional Replication) to traverse through the Transaction Log file and pushes the data into the Capture
Instance
Continued to next slide
23
© 2010 Wipro Ltd - Confidential
Change Data Capture Continued
Configuring Change Data Capture
As stated earlier change data capture is enabled by using system stored procedure. Before enabling the database for CDC
please ensure that SQL Server Agent services is started. To enable the Change Data Capture execute the following stored
procedure:
CREATE DATABASE ResearchDB
USE
USE ResearchDB
-- Enable Change Data Capture on the database first
EXEC sp_cdc_enable_db
-- Check which all databases are enabled for CDC
SELECT * FROM SYS.DATABASES
-- Create the table which on which we want to enable CDC
CREATE TABLE T1
(
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary INT
)
24
Continued to next slide
© 2010 Wipro Ltd - Confidential
Change Data Capture Continued
-- Enable Change Data Capture for the table now
EXEC sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'T1',
@role_name = 'CDCRole',
@supports_net_changes = 1
-- Performing some DMLs now
INSERT INTO T1 SELECT 1, 'Employee 1', 5000, NULL, NULL
INSERT INTO T1 SELECT 2, 'Employee 2', 6000, NULL, NULL
INSERT INTO T1 SELECT 3, 'Employee 3', 7000, NULL, NULL
DELETE FROM T1 WHERE EmpID = 2
UPDATE T1 SET Salary = Salary + 2 WHERE EmpID = 3
Since the database and the table has been enabled for CDC, we can now query the built-in functions to see the
affected changes. Look at the sample in the next slide.
Continued to next slide
25
© 2010 Wipro Ltd - Confidential
Change Data Capture Continued
-- Viewing changed data by passing Specific LSNs
DECLARE @begin_lsn BINARY(10)
DECLARE @end_lsn BINARY(10)
SET @begin_lsn = 0x0000001C0000010F0018
SET @end_lsn = 0x0000001C000001140003
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_T1 ( @begin_lsn, @end_lsn, 'all' )
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_T1 ( @begin_lsn, @end_lsn, 'all' )
-- Tacking DDL Changes
ALTER TABLE T1 ADD NewCol VARCHAR(50)
GO
EXEC sys.sp_cdc_get_ddl_history 'dbo_T1'
As can be seen above the CDC also captures the DDL changes.
Continued to next slide
26
© 2010 Wipro Ltd - Confidential
Change Data Capture Continued
-- Useful System Tables:
SELECT
SELECT
SELECT
SELECT
SELECT
*
*
*
*
*
FROM
FROM
FROM
FROM
FROM
cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.dbo_T1_CT
-- Useful Stored Procedures
EXEC sys.sp_cdc_get_ddl_history 'dbo_T1'
EXEC sys.sp_cdc_get_captured_columns 'dbo_T1'
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'T1'
Continued to next slide
27
© 2010 Wipro Ltd - Confidential
6. Change Tracking
Change tracking in SQL Server 2008 enables applications to obtain only changes that have been made to the user
tables, along with the information about those changes. With change tracking integrated into SQL Server,
complicated custom change tracking solutions no longer have to be developed.
Change Tracking has the following properties:
1. Unlike CDC the Change Tracking is configured using ALTER command. Not limited to Enterprise Edition.
2. Change Tracking is SYNCHRONOUS and happens within the transaction
3. Uses VERSION to track the changes. This version has a DATABASE scope.
4. Change Tracking cannot capture DDL statements (Unlike CDC which does)
5. Unlike Change Data Capture does not retain OLD values
6. Just tracks the changes using primary key, does not show other columns. Does not use any SQL Server Agent jobs to track
changes.
To configure Change Tracking, follow these steps:
-- Enable Change Tracking on the Database
ALTER DATABASE ResearchDB SET CHANGE_TRACKING = ON
-- Check which all database are enabled for Change Tracking
SELECT * FROM SYS.CHANGE_TRACKING_DATABASES
-- Enable Change Tracking on the Table
ALTER TABLE T1 ENABLE CHANGE_TRACKING WITH ( TRACK_COLUMNS_UPDATED = ON )
-- Check which all tables are enabled for change tracking
SELECT * FROM SYS.CHANGE_TRACKING_TABLES
-- Check Internal Tables, observe the name used for the internal tracking table
SELECT * FROM SYS.INTERNAL_TABLES
Continued to next slide
28
© 2010 Wipro Ltd - Confidential
Change Tracking Continued
Making use of Change Tracking:
-- Perform some DML
INSERT INTO T1
SELECT 1, 'Employee
INSERT INTO T1
SELECT 2, 'Employee
INSERT INTO T1
SELECT 3, 'Employee
INSERT INTO T1
SELECT 4, 'Employee
operations
1', 8000
2', 8000
3', 8000
4', 8000
-- SEE THE TRACKED CHANGES
SELECT * FROM CHANGETABLE ( CHANGES T1, 0 ) CT
-- GETTING RECORDS AS WELL AS CHANGES
SELECT CT.*, T1.* FROM
CHANGETABLE ( CHANGES T1, 0 ) AS CT
FULL OUTER JOIN
T1 ON CT.EmpID = T1.EmpID
Continued to next slide
29
© 2010 Wipro Ltd - Confidential
Change Tracking Continued
-- PERFORM THE DELETE
DELETE FROM T1 WHERE EmpID = 3
-- SEE THE TRACKED CHANGES, NOTICE WE WILL ONLY SEE THE NET CHANGES
SELECT * FROM CHANGETABLE ( CHANGES T1, 0 ) CT
SELECT * FROM CHANGETABLE ( CHANGES T1, 2 ) CT
-- WHAT IS THE CURRENT CHANGE TRACKING VERSION
SELECT CHANGE_TRACKING_CURRENT_VERSION()
-- UNDERSTANDING UPDATES
UPDATE T1
SET SALARY = 7000
WHERE EmpID = 1
-- Now see the tracked changes
SELECT * FROM CHANGETABLE ( CHANGES T1, 1 ) CT ORDER BY SYS_CHANGE_VERSION
SELECT * FROM CHANGETABLE ( CHANGES T1, 0 ) CT ORDER BY SYS_CHANGE_VERSION
Continued to next slide
30
© 2010 Wipro Ltd - Confidential
Change Tracking Continued
-- Making use of change context
-- Change Tracking Context helps us to determine who has done the changes
DECLARE @ApplicationName VARBINARY(128) = CAST ( 'SSMS ADHOC Batch' AS VARBINARY(128)
);
WITH CHANGE_TRACKING_CONTEXT ( @ApplicationName )
UPDATE T1
SET Salary = Salary+10
WHERE EmpID = 2
SELECT *, CAST ( SYS_CHANGE_CONTEXT AS VARCHAR(128) ) FROM CHANGETABLE ( CHANGES T1,
2 ) CT
Continued to next slide
31
© 2010 Wipro Ltd - Confidential
7. Multi-Server Management using Central Management Server
Central management servers store a list of instances of SQL Server that is organized into one or more central
management server groups. Actions that are taken by using a central management server group act on all servers in
the server group. This includes connecting to servers by using Object Explorer and executing Transact-SQL
statements and Policy-Based Management policies on multiple servers at the same time. Versions of SQL Server
that are earlier than SQL Server 2008 cannot be designated as a central management server.
To configure Central Management Server, follow these steps:
1. Click on the Registered Servers in the View Menu
2. Right click on Central Management Servers node and click on New Server Registration
3. In the New Server Registration dialog box, provide the value for the Server Name.
4. Click on Test and Save
5. Right click on the newly created Central Management Server, click on “New Server Group”
6. Provide some name to the Group and Click OK
7. Right click on the newly created server group and select “New Server Registration”
8. Add servers (Instances) of your choice that you want to centrally managed. Click OK
Testing:
1. Select newly created Central Management Server
2. Click on “New Query” Button and execute the following query:
3. SELECT * FROM SYS.DATABASES
4. You will notice that:
1. This query is fired against all the databases contained in the group
2. The result-set contains Server Name under the context as additional column
32
© 2010 Wipro Ltd - Confidential
8. Policy Based Management
Policy Based Management is yet another great new feature available in MS-SQL 2008. Policy based management (PBM) is used
to evaluate policies across instance or instances and with the help of PBM we can evaluate existing policies or create one to
enforce rules.
Primarily, policies can be used for:
1. On demand or scheduled Evaluation
2. Enforcement
3. Auto correction
Note: Originally this feature was called the Declarative Management Framework but has since been renamed. There
are a number of terms that we need to define in order to begin to understand Policy-Based Management:
Target - an entity that is managed by Policy-Based management; e.g. a database, a table, an index, etc.
Facet - a predefined set of properties that can be managed
Condition - a property expression that evaluates to True or False; i.e. the state of a Facet
Policy - a condition to be checked and/or enforced
Making use existing policies:
MS-SQL 2008 offers several pre-configured/ pre-defined policies that we can use to evaluate the health of the databases. For
example we can make of “Database Auto Close” policy to check which all databases within the instance are non-compliant.
Continued to next slide
33
© 2010 Wipro Ltd - Confidential
Policy Based Management Continued
Example of On-demand evaluation of existing policy on the current instance
To perform on-demand evaluation of the policy , expand Policy Management in SSMS, Right click on Policy
and then click on Evaluate. In the Evaluate Policy dialog box, click on browse button and select File as
source. Select the available policy from the list. Click on Evaluate button to see the evaluation results.You
can also click on Apply button to make the changes
Example of On-demand evaluation of existing policy on the multiple instances
To perform on-demand evaluation of the policy on multiple instances,:
1. Create a Central Management Server (Using registered server tool window)
2. Create Server Group
3. Register the instances
4. Right click on the central management server,:
1. Click evaluate policies
2. Select source policy
3. Click on evaluate
4. View the evaluation results
5. You can also Apply the changes if you wish
Continued to next slide
34
© 2010 Wipro Ltd - Confidential
Policy Based Management Continued
Example of On-demand evaluation of existing policy on the multiple instances
To perform on-demand evaluation of the policy on multiple instances,:
1. Create a Central Management Server (Using registered server tool window)
2. Create Server Group
3. Register the instances
4. Right click on the central management server,:
1. Click Evaluate Policies
2. Select Source Policy
3. Click on Evaluate
4. View the Evaluation results
5. You can also Apply the changes if you wish
35
© 2010 Wipro Ltd - Confidential
Policy Based Management Continued
Example of scheduled evaluation of existing policy
To perform scheduled evaluation of the policy:
1. Expand Policy Management in SSMS
2. Right click on Policies, click on Import Policy
3. Select appropriate policy and click OK
4. Right click on the newly imported policy and click properties
5. In the evaluation mode, select “On Schedule” and create the scheduler
6. Click OK
7. Notice that a job is created that will run as per schedule and evaluate the policy
8. The logs can be viewed by right clicking on the policy and click “View History”
Note: the View History will always show “Failed” icon as long as the database fails
the evaluation test.
Continued to next slide
36
© 2010 Wipro Ltd - Confidential
Policy Based Management Continued
As with existing policies we can also create our policy, evaluate and deploy it on the instance. This is
particularly useful when we want enforce standards, for example, all tables in the database should be
prefixed with “tbl”
To create custom policy, follow these steps:
1. Expand Policy Management in SSMS
2. Expand Conditions, Right click on it and click New Condition
3. In the New condition dialog box, provide value for names and select “Multipart Name” for the Facet.
4. Select
1. @Name for the Field
2. LIKE for the operator
3. ‘tbl%’ for the operator, click OK
5. Now right click on the Policies, click New Policy. Provide Name for the Policy.
6. In the Check Condition section, select the condition that you created in the previous step
7. Select Every Table in Every Database for “Against Target”. Click OK
8. You can now evaluate this newly created policy against your database as shown in the previous step
NOTE:You can also attach this policy to the database by Right licking on the database  Policies  Categories
 Select your policy  Evaluation  On change: Prevent  Click OK. After this try to create a table which
does not start with ‘tbl’ and seetthe output. The transaction will be aborted.
37
© 2010 Wipro Ltd - Confidential
9. Using Powershell in MS-SQL Server 2008
PowerShell is the .NET based automation engine that Microsoft shipped in November 2006. Tt
can be embedded into .NET applications but is usually used as a command line shell and
scripting language. PowerShell install packages are available in 32 bit and 64 bit versions for
Windows 2003, Windows XP and Windows Vista. It is an installable feature in Windows Server
2008 i.e. it is part of the operating system install. It is incorporated into a number of Microsoft
products including Exchange 2007, a number of System Center products including Operations
Manager 2007, Data Protection Manager 2007 and Virtual Machine Manager. A number of third
party vendors including Quest, Special Operations Software, SDM software, IBM, Citrix and
VMware have produced PowerShell adds for their products or incorporated PowerShell directly
into their products. PowerShell is now part of Microsoft’s Common Engineering Criteria and will
be incorporated into all major products.
To use Powershell, open it from SQL Serve Management Studio
Samples:
1. You can navigate through the instances like a file system
2. You can invoke SQL Command using Invoke-sqlcmd cmdlet, as in:
1. Invoke-sqlcmd –query “SELECT * FROM T1’
3. You can perform the formatting of the output using FORMAT-TABLE | auto
38
© 2010 Wipro Ltd - Confidential