Miscellaneous Administration Topics

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

1

p. 1

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 2

Chapter Introduction

• Chapter 8 covers several unrelated topics – All essential to being a DBA – None large enough to warrant a full chapter

p. 3

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 4

Moving and Copying Databases

• There are several techniques for moving and copying databases – BACKUP and RESTORE – DETACH and ATTACH – SQL Server Integration Services – Copy Database Wizard

p. 5

Moving and Copying Databases

• Depending on which technique you us, you can copy and move: – On the same server – Onto multiple servers – Multiple databases at the same time

p. 6

Moving and Copying Databases

• In the next few videos, we’ll see examples of how to move and copy databases using the following techniques: – BACKUP and RESTORE – DETACH and ATTACH – SQL Server Integration Services – Copy Database Wizard

p. 7

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

8

p. 8

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 9

Orphaned Users

• Way back in Chapter 5 (security) we talked about how user accounts are related to logins via the security identifier (SID) – Not by name

p. 10

Orphaned Users

• User accounts that previously had a login but now have no associated login are called orphaned users • Orphans can be created: – By deleting logins – By copying/moving database to new instance

p. 11

Orphaned Users

1. When you delete a login, the associated users are not deleted

p. 12

Orphaned Users

2. When you copy/move a database to a different instance, the user accounts may be orphaned –

–

If the destination instance does not have logins for the user account SIDs, the user accounts will be orphaned If the destination has logins with the expected SIDs, the user accounts will be mapped

p. 13

Orphaned Users

• Windows authentication logins: – If your user accounts are built on Windows user/group authentication logins, just create the Windows authentication login in the SQL Server • Remember from Chapter 5 that the SID is the actual Windows SID therefore the login/user will be mapped automatically

p. 14

Orphaned Users

• SQL Server authentication logins: – For SQL Server authentication login-based user accounts, simply creating the login with the expected name won’t work • CREATE LOGIN … generates a random SID

– Later on, we’ll cover how to fix!

p. 15

Orphaned Users

• You will encounter this problem often – The user accounts are stored in the database but logins are not – Any time you copy/move a database, you have to deal with this issue

p. 16

In the next video…

• We’ll talk about how to identify orphaned users and strategies for resolving

p. 17

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

18

p. 18

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 19

How to Identify Orphaned Users

• There are several ways to identify orphaned users 1. sp_change_users_login stored proc

2. Query the sys.database_principals view to locate users with no matching sid in sys.server_principals 3. Use SSMS’s graphical tools

p. 20

Orphaned Users

• There are two solutions to this problem: 1. Use sp_change_users_login stored procedure 2. Script out the login complete with SID from the source server and run script on destination server

p. 21

Let’s demo!

• Let’s take a look at using sp_change_users_login and scripting

techniques!

p. 22

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

23

p. 23

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 24

CLR Assemblies

• SQL Server 2008 allows you to load .NET DLLs (called assemblies) into your databases – Assemblies can be written in any language that implements that .NET Common Language Runtime (CLR) • C#, Visual Basic, etc • Often called “managed code” or “managed assemblies”

p. 25

CLR Assemblies

• An assembly loaded into SQL Server can contain one or more: – Stored procedures – User-defined functions – Triggers – and more

p. 26

CLR Assemblies

• Why would we need to use CLR assemblies? – Mostly because certain operations are more efficient in .NET than Transact-SQL • Examples: string manipulation, regular expressions

– Secondly because .NET can do things Transact-SQL cannot do • Example: create table results containing file and folder info

p. 27

CLR Assemblies

• What does this have to do with the DBA? – As the “gate keeper”, you must decide whether to allow and, if you do allow, to what extent you allow CLR integration – Remember the Principal of Least Privilege! • http://learnitfirst.com/go.aspx?id=LPSX

p. 28

CLR Assemblies

• CLR integration is disabled by default – Enabled using the sp_configure stored proc

p. 29

CLR Assemblies

-- To view setting: EXEC sp_configure 'clr enabled' -- run_value = 1: enabled

-- To enable: EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE WITH OVERRIDE

p. 30

In the next video…

• We’ll talk about permission sets and how to configure security for CLR objects

p. 31

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

32

p. 32

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 33

CLR Assemblies

• Once you enable SQL CLR, you must decide which permissions to allow • When you install an assembly, you must determine it’s Host Policy Level – SAFE – EXTERNAL_ACCESS – UNSAFE

p. 34

SAFE

• SAFE is the default and most secure – Cannot go outside the SQL Server process – Can connect to SQL Server in-process

p. 35

EXTERNAL_ACCESS

• EXTERNAL_ACCESS is “SAFE plus go out of process” – Can do all that SAFE can do – Can also go outside the SQL Server process to access external resources • • • •

Read files and folders Read environmental variables Read registry more…

p. 36

EXTERNAL_ACCESS

• UNSAFE can do anything – Can take over the world! – Only sysadmins can assign – Has same permissions as EXTERNAL_ACCESS plus: • Can call unmanaged code (i.e. C++ DLLs) • Allows assemblies to perform “illegal operations against the SQL Server process space…” – Source: Books Online

p. 37

How do I know which to use?

• As a DBA, you cannot know every nuance of every language that can write managed code assemblies • Must coordinate with developer to understand security needs

p. 38

How do I know which to use?

• As a general guideline: – Use SAFE for assemblies that need only to do computations, manipulations, and table access – Use EXTERNAL_ACCESS for when assemblies need to go outside SQL Server space – Use UNSAFE when working with unmanaged code

p. 39

How do I know which to use?

• Each permission set has different requirements for creation: – SAFE: requires CREATE ASSEMBLY permission in the database – EXTERNAL_ACCESS: SAFE plus EXTERNAL ACCESS ASSEMBLY server permission – UNSAFE: sysadmins only!

p. 40

How do I know which to use?

• Create assembly in the [LearnItFirst.com] database USE [LearnItFirst.com] GO CREATE ASSEMBLY LIFRegEx FROM „C:\MyAssembly.DLL‟ WITH PERMISSION_SET = SAFE p. 41

How do I know which to use?

• Can change permission set with ALTER ASSEMBLY ALTER ASSEMBLY LIFRegEx WITH PERMISSION_SET = EXTERNAL_ACCESS

p. 42

The DBA’s Role

• With respect to the CLR, the DBA is expected, at a minimum, to be able to: – To be able to explain what the CLR is – To be able to make suggestions related to when to use the CLR for certain operations – To be able to stop developers from using CLR unnecessarily – To understand the various permission sets

p. 43

The DBA’s Role

• To reiterate: you are not expected to know every single language – Don’t be intimidated by the CLR – If a manager tells you that, “You have to know C# to be a good DBA” during a job interview, you do not want to work for that person! • ….. Well, how much does it pay again?

p. 44

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

45

p. 45

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 46

The Resource Governor

• The Resource Governor: – New in SQL 2008 – Allows you to limit “resources” based on certain criteria • CPU, memory, number of requests, more

– Only available in Enterprise and Developer editions

img by eliburd @ flickr.com

p. 47

Example Usage

• Examples uses of the Resource Governor – Can prevent expensive queries from consuming more than an allowed amount of memory or CPU – Limit the amount of CPU that backup compression can consume – Ensure that specific applications cannot issue queries or workloads that are unhealthy to the rest of the server – Web host wants to provide guaranteed levels of service to multiple clients

p. 48

Example Usage

• The basics of how it works: 1. DBA creates a resource pool 2. DBA assigns the resource pool one or more workload groups 3. DBA writes a classifier function that binds incoming sessions to the appropriate workload group

p. 49

The Resource Governor

• Source: Using the Resource Governor, white paper, Microsoft 2009

p. 50

Resource Pools

• Resource Pools are the highest layer of abstraction – Can define a MIN and MAX CPU – Can define a MIN and MAX memory – Contain n workload groups

p. 51

Workload Groups

• Workload groups allow more granular settings

p. 52

Workload Groups

• Settings available for workload groups: – 0 means unlimited for all – Maximum Requests • Max number of simultaneous requests

– CPU Time (sec) • Max amount of CPU time that a request can use

– Memory Grant % • Max amount of memory a single request can take from the pool

– Grant Time-out (sec) • Max time that a query can wait for a resource before the query fails

– Degree of Parallelism • Max degree of parallelism for parallel requests. Range is 0 to 64

p. 53

Classifications

• Classification is the process of determining the workload group for a given session – A user-defined scalar function that is registered using the ALTER RESOURCE GOVERNOR statement • Called the classifier function

– Evaluated for every new session except Dedicated Administrator Connections

p. 54

The Classifier Function

• The classifier function: – There can be only one – If it returns NULL or a non-existent group, the session is bound to the default workload group – Sessions are bound to the workload group for life

p. 55

Putting It All Together

• After enabling the Resource Governor and putting a classification function in place: 1. User logs in 2. The login authentication is verified 3. Upon successful authentication, any LOGON triggers are executed 4. Finally, classification occurs

p. 56

Words of Wisdom

• A final word of caution – Be ready… • If you mess up your classifier function, you’ve messed up the entire server • Only Dedicated Administrator Connection (DAC) sessions can bypass classification • If a problem arises, log in as DAC and remove classifier

p. 57

Let’s play!

• In the next video, let’s play with the Resource Governor!

p. 58

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

59

p. 59

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 60

Policy-Based Management

• Windows administrators have long been familiar with policy-based management – “All user accounts must change their password every 42 days and it must contain letters, numbers, and special characters”

p. 61

Policy-Based Management

• DBAs have always had standards and business rules – “All stored procedures must begin with usp_” – “No database may have AutoShrink turned on”

• However, what we haven’t had is a way to enforce them automatically

p. 62

Policy-Based Management

• SQL Server 2008 introduces Policy-Based Management (“PBM”) – Allows DBA to define business rules (i.e. policies) that can either be: • Automatically enforced by SQL Server (i.e. prevent noncompliant modifications) • Reported to the DBA as non-compliant

p. 63

Policy-Based Management

• Nearly all DBAs have a daily task list: – Verify backups – Look for anomalies (low disk space, unrecognized files or folders, etc) – Check the error logs – Check the job logs – and many more tasks…

p. 65

Policy-Based Management

• One such task is to verify that new servers are compliant – Example: “Have any new servers been installed?” • If “Yes”, are they up to our standards? – Evaluation or Developer Edition only unless installed by DBAs (to ensure licensing compliance) – Minimum build number – MYDOMAIN\SQLDBAs Windows group is sysadmin – Authentication mode is Windows-only (a.k.a. Integrated)

p. 66

Policy-Based Management

• Another task is ensuring no changes have occurred that result in databases/objects being non-compliant – Are all production databases • Online • Using correct recovery model

– Are there any new [tables/views/functions/procs]? • Do they meet naming conventions?

p. 67

Policy-Based Management

• Manual enforcement of these types of policies is error prone and a time sink – Spending just 10 minutes a day on manually enforcing policies adds up to more than 40 hours at the end of a year • An entire work week!

– How much time did you have to spend during the year fixing a problem that occurred because of non-compliance?

p. 68

In the next video…

• We’ll take a look at the terms and concepts behind SQL Server 2008’s PBM

p. 69

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

70

p. 70

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 71

Policy-Based Management

• Understanding SQL Server’s policy-based administration is – Easy because we are all familiar with the basic ideas behind policy-based management – Difficult due to having to learn new abstractionlayer-type terms

p. 72

Policy-Based Management

• First, let’s talk about what you can police: – Nearly anything that has properties

• “Okay, so what items in SQL Server have properties?” – Everything

p. 73

Policy-Based Management

• “Are you saying that I can create a policy on anything I want?” – Pretty much!

• “What can’t I police?” – Anything you would normally do in a constraint or trigger – Anything you would normally do with Resource Governor

p. 74

Policy-Based Management

• Let’s now talk about the terms and concepts of Policy-Based Management (PBM) in SQL Server 2008

p. 75

Policy-Based Management

• It only takes two steps to define your policies: – Step 1: Create a condition – Step 2: Create the policy built on the condition

p. 76

Policy-Based Management

• Conditions define what you want to police – “I want to ensure that all stored procedures in our WebMarketing database start with usp_” – “No database may have AutoShrink turned on”

• This may seem like the policy but…

p. 77

Policy-Based Management

• Policies define the action you want to occur when a set of conditions is met – “A developer should receive an error if they try to create a stored procedure in our WebMarketing database that does not start with usp_”

p. 78

Policy-Based Management

• A (weak?) analogy: – Conditions are like SELECT statements •

Rows that are returned from running the query are compliant

SELECT * FROM sys.procedures WHERE name LIKE 'usp_%'

p. 79

Policy-Based Management

• So far, so good, right? – Nothing too difficult yet… – Let’s make things a bit more complicated in the next video!

p. 80

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

81

p. 81

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 82

Policy-Based Management

• In truth, PBM is quite easy • The tricky parts are: – Understanding terms like facet, property, and target – Understanding which facets allow which evaluation modes – Writing conditions and always “being positive”

p. 83

Policy-Based Management

• Facets are confusing at first – Simple mode: facets are just another word for things you already know by other names • Table, database, server, login, etc

– Advanced mode: facets are an abstraction layer that models behavior or characteristics for targets. • A target is a table, a database, a server, a login, etc

p. 84

Policy-Based Management

• Each facet contains at least one property • Properties have values • To retrieve or query a property’s value, you write an expression

p. 85

Policy-Based Management

• Example facet: The Stored Procedure facet has several properties: – Name – CreateDate – Schema

p. 86

Policy-Based Management

• To continue our previous analogy in which a condition was similar to a SELECT statement – Think of the facet as what occurs after the FROM – Think of the property as the column

p. 87

Policy-Based Management

• Consider this query: SELECT * FROM sys.procedures WHERE name LIKE 'usp_%„

• In this example, ‘sys.procedures’ would represent the Stored Procedure facet and ‘name’ would represent the facet’s Name property p. 88

Policy-Based Management

• We write our conditions against facets – Our desired condition: “All stored procedures in the WebMarketing database must begin with usp_”

• Which two facets will we use? 1. _________________________ 2. _________________________

p. 89

Policy-Based Management

• We write our conditions against facets – Our desired policy: “All stored procedures in the WebMarketing database must begin with usp_”

• Which two facets will we use? 1. Stored Procedure 2. Database

p. 90

Policy-Based Management

• Explanation: – “Stored procedure” is the facet and we must find all stored procedures whose Name property starts with usp_ – “Database” is the facet and we must find only that database whose name is WebMarketing

p. 91

In the next video…

• We’ll learn more about expressions and the mindset of writing conditions and policies

p. 92

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

93

p. 93

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 94

Policy-Based Management

• We write conditions using expressions – Not the same as a Transact-SQL expression – Same syntax as SSIS and SSRS expressions – You need to know expression syntax to be a good DBA

p. 95

Policy-Based Management

• Expression syntax is not difficult – Operators are same as C#: ==, !=, etc – Functions are similar to C# and T-SQL: • GETDATE() returns current system date/time • ISNULL() returns true/false to indicate if an expression is null

– Conditions are similar to Transact-SQL • LIKE and NOT LIKE, IN and NOT IN

– Double-quotes around strings

p. 96

Policy-Based Management

• Expression syntax is used in the graphical tools only – If you want to script out your conditions, you must write XML!

p. 97

Policy-Based Management

• Misc info about conditions: 1. Cannot change the facet of a condition after creation • Can change the expression but not the facet

2. A single condition may only operate on one facet • Can combine conditions in a policy to create complex scenarios

3. Conditions can be used by multiple policies • Design your conditions so that they can be combined with other conditions (i.e. create reusable conditions)

p. 98

Policy-Based Management

• On a production server using PBM, you will likely have hundreds of conditions and policies – It is important to have a naming convention – Opt for longer, descriptive names • Conditions have an optional Description for more information • Policies have Descriptions and “Additional help links” allowing you to create web page links in your errors

p. 99

Policy-Based Management

• Policies also have Categories (a.k.a. Policy Groups) – Can run the entire category against a database/server instead of one-policy-at-a-time – Can mandate that new databases participate

p. 100

In the next video…

• We’ll cover Execution Modes

p. 101

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

102

p. 102

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 103

Evaluations

• Anytime a policy is evaluated and finds non-compliance, it logs the offender – In the SQL Server error Log – In the Windows Application log – In the msdb database

• Be prepared for msdb growth!

p. 104

Execution Modes

• Policies also have Execution Modes which determine – When the policy will be evaluated – The response to non-compliance

p. 105

Execution Modes

• There are four execution modes: – On demand – On schedule – On change: prevent – On change: log only

• Not all facets support all execution modes

p. 106

Execution Modes

• On demand: policies are not evaluated unless manually done so by admin – Useful for audits and non-critical policies – Supported by all facets – Actions taken: • Raises error #34052 • Logs infraction

p. 107

Execution Modes

• On schedule: a SQL Server job is created that periodically evaluates the policy – Useful when evaluation performance impacts system – Supported by all database-engine facets – Job does not error out if policy violations found • Writes errors to the logs and evaluation history

– Actions taken: • Raises error #34052 • Logs infraction

p. 108

Execution Modes

• On change: log: logs non-compliance only – Useful when you want a history of policy – Supported by few facets – Actions taken: • Logs infraction

p. 109

Execution Modes

• On change: prevent: non-compliance is not allowed – Useful to prevent critical changes – Expensive – Supported by fewest facets – Actions taken: • Rolls back user transaction • Raises error #34050 or #34051 • Logs infraction

p. 110

Summary

Facet

On change: log

On change: prevent

Application Role

a

a

Asymmetric Key

a

a

Database Option

a

Database Role

a

a

Login Options

a

a

Resource Pool

a

a

Schema

a

a

Server Configuration

a

Stored Procedure, Functions, Views

a

a

Table Options

a

a

Workload Group

a

a p. 111

In the next video…

• We’ll cover whether or not policies replace permissions!

p. 112

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

113

p. 113

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 114

Writing Policies and Conditions

• Consider this security requirement: “All databases must be owned by a sysadmin” – Should this be done via policy or other means? • Answer: It depends on what action you want!

p. 115

Writing Policies and Conditions

• If you want to prevent someone who is not a sysadmin from adding a database, then this is a permission • If you want to report on the databases that are currently not owned by sysadmins, then you want a policy

p. 116

Writing Policies and Conditions

• “Do policies take the place of permissions in SQL Server 2008?” – No! – Policies and permissions actually have nothing to do with one another

p. 117

Writing Policies and Conditions

• Generally speaking, actions that can be prevented by permission are not preventable using On change: prevent – Example: preventing someone from creating databases, tables, procs, etc

p. 118

Writing Policies and Conditions

• Prevention policies are “things you have permission to do but, for some reason, management doesn’t want you doing them” – Such as creating stored procs in the WebMarketing database that do not start with usp_

p. 119

Writing Policies and Conditions

• Remember our policy: “We want all stored procedures in WebMarketing to start with usp_” – Is this a permission or a policy? • A policy however the user still needs the CREATE PROC permission in the database!

p. 120

In the next video…

• We’ll discuss how to write effective conditions and policies

p. 121

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

122

p. 122

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 123

Writing Policies and Conditions

• Lastly, writing effective policies requires practice and patience – Writing conditions can be a confusing mess • It is easy to create two policies that contradict each other

– Expect to spend a few hours “getting it right” for your first several policies and conditions • You will mess up!

p. 124

Writing Policies and Conditions

• Example policy requirement: “All databases must be owned by the sa” – This requires one condition since there is only one facet (Database facet)

p. 125

Writing Policies and Conditions

• How would you write the condition? 1. To return databases owned by the sa 2. To return databases not owned by the sa

p. 126

Writing Policies and Conditions

• How would you write the condition? – To return databases owned by the sa – To return databases not owned by the sa

• Your conditions define the ideal, not the exception • Since we want only sa-owned databases, our condition is where @Name=“sa” p. 127

Writing Policies and Conditions

• Example policy req’t: “We want all stored procedures in WebMarketing to start with usp_” – This requires two conditions since there are two facets: • •

Stored Procedure facet Database facet

p. 128

Writing Policies and Conditions

• We have the choice of writing our Stored Procedure condition so that it returns: – Stored procs that start with usp_ – Stored procs that do not start with usp_

• Which do we choose?

p. 129

Writing Policies and Conditions

• We have the choice of writing our Stored Procedure condition so that it returns: – Stored procs that start with usp_ – Stored procs that do not start with usp_

• Remember – we want our conditions to define what we do want, not what we don’t want

p. 130

Writing Policies and Conditions

• To understand why it is this way, consider our options for the second facet (Database facet): – Database named WebMarketing – Databases not named WebMarketing

• Clearly we choose the first option

p. 131

Writing Policies and Conditions

• The final step is to create a policy on these two conditions

p. 132

In the next video…

• Enough talk - let’s demo!

p. 133

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

134

p. 134

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 135

PowerShell!

• PowerShell support for SQL Server 2008 is included in SSMS • PowerShell is a very extensible and powerful scripting shell that allows admins to script (“automate”) administration functions

p. 136

PowerShell!

• If you know DOS, you can easily get started with PowerShell (“PS”) – Commands like dir, del, cd perform the same function in PS

• PowerShell can do everything you can do at the command line and more

p. 137

PowerShell!

• So why talk about PowerShell in a SQL admin course? – SQL DBAs can automate SQL Server via PowerShell scripts! – This allows for uniformity across the enterprise • Anyone who knows PowerShell can write scripts • Allows integration between systems - can load Windows Event Logs into SQL Server tables for analysis via PS

p. 138

PowerShell!

• You can manage your SQL Server by – Issuing PS commands directly into the PS shell (powershell.exe) – Executing PS files that contain PS scripts • C:\Scripts\DeployNewServer.ps1

– Creating SQL Server jobs that execute PowerShell steps

p. 139

PowerShell!

• Full coverage of PS is beyond the scope of this course however there are several concepts you will need to know • Let’s start with the basics…

p. 140

PowerShell!

• PowerShell and the OS: – Included in Windows 2008 and Windows 7 – Download for Windows 2003, Vista, XP, etc

• Versions of PowerShell – 2.0 - Windows 2008 R2 and Windows 7 – 1.0 - Windows 2008, Windows 2003, Vista, XP

• http://www.microsoft/powershell

p. 141

PowerShell!

• SQL Server 2008 ships with the sqlps.exe, a minishell – Can drop to command line and just run sqlps to manage servers – Integrated into SSMS

p. 142

PowerShell!

• The sqlps minishell incorporates several cmdlets for managing SQL Server – Pronounced “commandlets”

• cmdlets are .NET classes invoked by PowerShell at runtime • Common cmdlets included in sqlps: – Invoke-Sqlcmd – Invoke-PolicyEvaluation

p. 143

In the next video…

• PowerShell is easier to demo than talk about!

p. 144

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

145

p. 145

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 146

Unstructured Data

• Let’s compare data storage/access of 1990 to 2010 for a health insurance company • 1990: – Storage: DB2 – Tables to store an insured family: 25 – Type of data stored: row data only – Export formats: CSV, text

p. 147

Unstructured Data

• 2010: – Storage: SQL Server 2008 – Tables to store an insured family: 50 – Type of data stored: row data, xml, images, audio – Export formats: CSV, text, xml, images, audio

p. 148

Unstructured Data

• Today’s companies have to deal not just with row data but “unstructured data” as well – Legal firms need transactional support for MS Word, Excel documents – Health industry needs transactional support for imaging – Music industry needs transactional support for audio

p. 149

Unstructured Data

• Unstructured data and relational databases have a history – Most common technique: store unstructured data (“files”) in file system; store pointers to file locations in the database • Pros: fast, easy to understand, easy to implement, easy to separate storage • Cons: No transactional support, backup/restore complicated, requires separate permissions, links can become stale

p. 150

Unstructured Data

• Files are stored in a folder in the file system that requires additional permissions

p. 151

Unstructured Data

• Beginning in SQL Server 2005, we could store unstructured data in the form of files in a new data type, VARBINARY(MAX) – Replaced the older image data type – Pros: transactional support, simplify backup/restore, native SQL Server security, never stale, can separate storage using filegroups – Cons: speed, data must be converted in/out to binary format, limited to 2GB files, explosion of database growth, buffer pool memory used to access data

p. 152

Unstructured Data

• Files are stored in a VARBINARY(MAX) column as a Binary Large Object (BLOB)

p. 153

Unstructured Data

• SQL Server 2008 introduces FILESTREAM access – Sort of a blend of file system pointers and VARBINARY(MAX) techniques – Data is stored in the NTFS filesystem • Improved speed, separation of storage

– All data access is through SQL Server • Single point of security, full transactional support, fulltext indexing, no stale links

p. 154

Unstructured Data

• FILESTREAM is not for everything BLOB… – For small BLOBs, a standard VARBINARY(MAX) will offer better performance • Consider “small” to mean around 1MB in file size

– No backup compression – No Transparent Data Encryption (use Encrypted Files System instead) – Not supported in database mirroring

p. 155

Unstructured Data

• More about FILESTREAM: – Works in all editions – Allows full ROLLBACK support – Supported on clusters and in log shipping – No limit on file size • VARBINARY(MAX) is limited to 2GB • VARBINARY(MAX) FILESTREAM has no such limit

p. 156

In the next video…

• We’ll cover more DBA-related details about FILESTREAM

p. 157

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

158

p. 158

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 159

FILESTREAM

• To use FILESTREAM for a database, follow these five steps: 1. Enable FILESTREAM for the instance 2. Add a FILESTREAM-enabled filegroup to the database 3. Add one or more files to the new filegroup 4. Create your column and assign it the FILESTREAM attribute 5. Load your data! p. 160

FILESTREAM

• To use FILESTREAM for a database, follow these five steps: 1. Enable FILESTREAM for the instance 2. Add a FILESTREAM-enabled filegroup to the database 3. Add one or more files to the new filegroup 4. Create your column and assign it the FILESTREAM attribute 5. Load your data! p. 161

FILESTREAM

• FILESTREAM requires both SQL Server DBA and the Windows admin – The SQL Server Service must be configured for FILESTREAM access first using the SQL Server Configuration Manager

p. 162

FILESTREAM

• Inside SQL Server, FILESTREAM is an instance-level option – Disabled by default; sysadmins only can change

p. 163

FILESTREAM

• To enable FILESTREAM using TransactSQL: EXEC sp_configure ‘filestream access level’ -- 0: disabled (default) -- 1: Transact-SQL access only -- 2: Full access enabled (a.k.a. -T-SQL and streaming access -via Windows APIs) -NOTE: Local disks only for now…

p. 164

FILESTREAM

• To use FILESTREAM for a database, follow these five steps: 1. Enable FILESTREAM for the instance 2. Add a FILESTREAM-enabled filegroup to the database 3. Add one or more files to the new filegroup 4. Create your column and assign it the FILESTREAM attribute 5. Load your data! p. 165

FILESTREAM

• Once you enable FILESTREAM at the instance level, you must now: 1. Create a new filegroup specifically for FILESTREAM 2. Add files to the filegroup

• You can do this with CREATE DATABASE or ALTER DATABASE

p. 166

Enable FILESTREAM on a Database

p. 167

Enable FILESTREAM on a Database

p. 168

Enable FILESTREAM on a Database

p. 169

FILESTREAM

• To create the filegroup at creation: CREATE DATABASE BigInsurance ON PRIMARY (NAME = BigInsurance, FILENAME = „D:\BigInsurance.mdf'), FILEGROUP BIDocs CONTAINS FILESTREAM( NAME = BigInsuranceDocs1, FILENAME = ‘F:\BigInsuranceFiles') LOG ON ( NAME = BigInsurance_log, FILENAME = „E:\BigInsurance.ldf')

p. 170

FILESTREAM

• To add FILESTREAM to an existing database: ALTER DATABASE [LearnItFirst.com] ADD FILEGROUP FilestreamFG CONTAINS FILESTREAM GO ALTER DATABASE [LearnItFirst.com] ADD FILE(Name=„File1‟, FileName=„F:\File1‟) TO FILEGROUP = FilestreamFG

p. 171

FILESTREAM

• To add FILESTREAM to an existing database: ALTER DATABASE [LearnItFirst.com] ADD FILEGROUP FilestreamFG CONTAINS FILESTREAM GO ALTER DATABASE [LearnItFirst.com] ADD FILE(Name=„File1‟, FileName=„F:\File1‟) TO FILEGROUP = FilestreamFG

p. 172

In the next video…

• We’ll take a look at the other requirements to use FILESTREAM

p. 173

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

174

p. 174

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 175

FILESTREAM

• To use FILESTREAM for a database, follow these five steps: 1. Enable FILESTREAM for the instance 2. Add a FILESTREAM-enabled filegroup to the database 3. Add one or more files to the new filegroup 4. Create your column and assign it the FILESTREAM attribute 5. Load your data! p. 176

FILESTREAM

• You can add FILESTREAM support to new tables or existing tables – Use ALTER TABLE to add to an existing table

USE [LearnItFirst.com] GO CREATE TABLE dbo.ClaimImage ( ClaimId INT NOT NULL PRIMARY KEY , TheImage VARBINARY(MAX) FILESTREAM NOT NULL , RowId UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEW_ID() ) -- Tables with FILESTREAM data must have a -- non-null UNIQUEIDENTIFIER column

p. 177

FILESTREAM

• To use FILESTREAM for a database, follow these five steps: 1. Enable FILESTREAM for the instance 2. Add a FILESTREAM-enabled filegroup to the database 3. Add one or more files to the new filegroup 4. Create your column and assign it the FILESTREAM attribute 5. Load your data! p. 178

FILESTREAM

• Writing/reading binary data is for developers – Requires accessing Win32 APIs – .NET is popular but can write Transact-SQL directly

INSERT dbo.ClaimImage (ClaimId, TheImage) VALUES (1, 0x01)

p. 179

FILESTREAM

• DBAs needs to know about security and backup/recovery – Security permissions are done inside SQL Server, not on the file system – The physical files/folders are secured by SQL Server service account – Backup/restore will include all files

p. 180

FILESTREAM Best Practices

• Best Practices for FILESTREAM – Do not make your ROWGUIDCOL a clustered index – For best performance, FILESTREAM files should be on separate physical volumes when possible – NEWSEQUENTIALID() will perform better than NEWID() when replicating databases – Read Paul Randall’s column: • http://learnitfirst.com/go.aspx?id=WBLX

– Have your network admins (or whoever configures the physical Windows Server 2008/2003 machine) read the top part of this article: • http://msdn.microsoft.com/library/dd206979.aspx

p. 181

FILESTREAM

• Final thoughts… – Transact-SQL’s DELETE statement causes the physical file to be deleted • Physical file deletion is not performed until a CHECKPOINT operation

– Do not attempt to manage files via the file system • Cannot change names, attributes or delete files

– There are other options… • Search for “SQL Server 2008 remote BLOB storage”

p. 182

Chapter 8: Miscellaneous Administration Presented by Scott Whigham

183

p. 183

• Chapter Introduction • Moving and Copying Databases • Dealing with Orphaned Users • CLR and .NET Assemblies

Management

Overview

What We’re Going to Cover

• Resource Governor • Policy-Based Management • Powershell • FILESTREAM • Snapshots

p. 184

Database Snapshots

• A database snapshot is a read-only “copy” of a database that “stops” at the point the snapshot is taken – Example: If you take a snapshot of the LearnItFirst database at 0800, the snapshot would have the entire database yet would not reflect any changes that occurred after 0800

p. 185

Database Snapshots

• About database snapshots: – Must exist on same instance as source – Available for Enterprise & Developer editions only – Debuted in SQL Server 2005 – Multiple snapshots can exist of same source db • Some sites create a snapshot for each day of the week

p. 186

Database Snapshots

-- At 0800, we execute: CREATE DATABASE LIFSnapshot_0800 ON(NAME=„LearnItFirst‟ ,FILENAME=„D:\LIF_0800.ss‟) AS SNAPSHOT OF LearnItFirst -- Logical file name must be -- same as source. Also note -- that we do not define a log p. 187

Database Snapshots

USE LIFSnapshot_0800 GO -- Returns data up through 0800: SELECT * FROM dbo.MyTable GO -- Changes after 0800 are unseen

p. 188

Database Snapshots

• Reads against the snapshot: – Will use the source database if the data requested has not changed since snapshot creation • Acquire SHARED locks at source to prevent blocking

– Will return the snapshot copy if the data has changed • This provides a “at creation” level of consistency to data

p. 189

Database Snapshots

• Database snapshots are generally used for three purposes: – Reporting/History: reads against a snapshot are consistent to the point-in-time of creation – Restore point: can restore a database using the snapshot; transactions after the snapshot was taken are lost – Testing: can test effect of changes and revert back to snapshot in the event of disaster/error

p. 190

Database Snapshots

• Example restore: -- Only allowed if: -- (1) there is only one snapshot -- (2) all source db files exist

RESTORE DATABASE LearnItFirst FROM DATABASE_SNAPSHOT = „LIFSnapshot_0800‟

p. 191

Database Snapshots

• Database snapshots use sparse files to minimize disk space – Feature of NTFS that allows NTFS to allocate disk space on an “as needed” basis – When you create a snapshot, database pages are not copied to the snapshot – As pages become dirty, the “original” data page is copied to database snapshot • When that happens, the sparse file’s size stays the same yet the “Size on disk” increases

p. 192

Database Snapshots

• A 1.1GB database with a “fresh” snapshot

p. 193

Database Snapshots

• After performing UPDATEs to source:

p. 194

Database Snapshots

• Snapshots get stale – Have to plan to delete old snapshots – If they stick around, the can become larger than source!

p. 195

Database Snapshots

• Final thoughts… – Pros: • Great way to provide users with a point-in-time view of database • Snapshots are quick and easy to create thanks to sparse files

– Cons: • No way to create using SSMS graphical tools; T-SQL only • Cannot change user permissions for snapshot vs. source – A snapshot is a read-only copy of the source

• Added overhead to source database whenever changes occur – Transaction is not complete until snapshot is up to date

p. 196