Sql Server 2005 Whats New For The Dba

  • Uploaded by: jpaulino
  • 0
  • 0
  • November 2019
  • PDF
Download

This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA

Overview

Download & View Sql Server 2005 Whats New For The Dba as PDF for free.

More details

  • Words: 3,938
  • Pages: 92
Technical Community Resources 

TechNet Belgium & Luxembourg www.microsoft.com/belux/technet/



Resources For IT Professionals

www.microsoft.com/belux/technet/community     

Webcasts, Bloggers Newsgroups, Most Valuable Professionals, Interesting Websites...

SQL Server 2005 What’s new for the database administrator

Marc Mertens Instructor/Developer Flexcom-Azlan [email protected] http://www.azlan.be

I.P. Marc Mertens

Agenda       

Sql2005 Architecture Administrative Tools Securing SQL2005 Monitoring Sql2005 Maintaining Indexes Disaster Recovery Data availability

Sql2005 Architecture          

Relational Database Engine CLR Runtime support Analysis Services Notification Services (Available now) System Integration Services Reporting Services (Available now) Replication Service Broker Native HTTP Support Full-Text search

Agenda       

Sql2005 Architecture Administrative Tools Securing SQL2005 Monitoring Sql2005 Maintaining Indexes Disaster Recovery Data availability

Administrative Tools 

Sql Server Management Studio 



SQL Computer Manager 



Replacement of Service Manager

sqlcmd utility 



Replacement of Enterprise Manager and Query Analyzer

Replacement of ISQL and OSQL

SMO 

Replacement of DMO

Sql Server Management Studio     

Based on Visual Studio .NET Manages RDBMS, Cubes, Reporting Services and CE Databases Project Centered View Different editors for T-SQL, XMLA and MDX Nice things     

Multithreaded Scheduling Scripting Filtering Disconnected editing

Sql Computer Manager  

MMC snap-in to manage all the SQL Services and connectivity settings Replaces Server Network Utility, Client Network Utility and SQL Service Manager

Sqlcmd.exe 

Enhancements     

Uses OLE DB Variables DAC (Dedicated Administrator Connection) Passes error info to calling environment Commands

DEMO

Using SQL Computer Manager Using sqlcmd

I.P. Marc Mertens

Agenda       

Sql2005 Architecture Administrative Tools Securing SQL2005 Monitoring Sql2005 Maintaining Indexes Disaster Recovery Data availability

Security Concepts 

Principal (Receiver from a permission) 

3 Levels   



Securable (Object to get a permission on) 

3 Scopes   



Windows (Users and groups in SAM or domain) SQL Server (logins and server roles) Database (Database users, Database roles, Application Roles and Database groups)

Server Database Schema

Permissions 

Rules that specify the level of access that principal has to securable

What’s new in security?      

  

SP’s are replaced by CREATE LOGIN , CREATE USER, DROP LOGIN, … Password policy Hierarchical scopes Separation of owner and schema Server level permissions Limited metadata visibility (you see only metadata for objects you have permission on) Execution context Master keys Support for certificates

Creating Principles 

Logins CREATE LOGIN [FlexcomAzlan\jan] FROM WINDOWS WITH DEFAULT_DATABASE=[AdventureWorks]



Users CREATE USER [jan] FOR LOGIN [FlexcomAzlan\jan]



Roles CREATE ROLE Accounting

Password Policy 

Problem with trusted connections  

 



Firewalls Application can’t choose security context

Sql authentication solves these problems but is less secure in SQL 2000 because there is no password policy. SQL 2005 allows you to use the Windows Password Policy (Complexity requirements, expiration, history …) for sql logins. Example: CREATE LOGIN [Joris] WITH PASSWORD=N'a_complex_1_password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

Securable scopes 

Server 



Database 



Logins, HTTP Endpoints, Certificates, Databases … Users, Roles, Tables, Views, Functions, Assemblies …

Schema 

Tables, Views, Functions, Procedures

Examples of using scope 

Server scope GRANT ALTER ON DATABASE :: pubs TO userAlice GRANT ALTER ANY DATABASE TO loginAlice



Database scope GRANT SELECT ON DATABASE :: pubs TO jan GRANT INSERT ON SCHEMA :: test TO jan



Schema scope GRANT SELECT ON OBJECT :: test.authors TO piet GRANT SELECT ON dbo.righttest TO piet

Schema 

What? 

Namespaces for database objects 

SQL2000 FQN ...



SQL2005 FQN ..<schema>.



Why? 



In SQL2000 owner has all the rights on a object and users can not be dropped when they own objects, the owner name is thus not a good candidate to classify objects by there name. By using the schema scope you can manage all similar objects as one unit

Schema scenario    

A database contains tables for production, human resources and sales Some users do work on production, human resources and sales All related tables should have same owner (authorized user). Solution:    

Create schemas for the production, human resources and sales tables Give users (roles) the necessary rights on the correct schema Set defaults for users so they can use the object name If needed you can move objects to other schemas or give them different owners

Schema scenario (cont.) 

Create a schema CREATE SCHEMA Production AUTHORIZATION admin



Set the search path for objects to production. then dbo. ALTER USER alice WITH DEFAULT_SCHEMA=Production



Create objects in a schema (automatically owned by admin) CREATE TABLE Production.Products (nr int, …)

Schema scenario (cont.) 

Give a user (role) select rights on all the tables in a schema GRANT SELECT ON SCHEMA :: Production TO managers



Move objects to another schema ALTER SCHEMA HumanResources TRANSFER Person.Address



Change the owner of a table ALTER AUTHORIZATION ON OBJECT::Product to admin

Server level permissions  

Delegation of administrative work SQL2000 (Server roles) 



Limited to changing membership of server roles

SQL2005 (Server level permissions)    

Server roles still supported Possibility to give right on objects in the server scope or even the server itself Every permission is now grantable Examples: GRANT CONTROL SERVER TO alice GRANT CREATE ANY DATABASE TO joris

Execution context 

SQL2000 



The only way to give users rights to execute a stored procedure (function), even if they have no rights on the objects used in the stored procedure, is by a unbroken chain (all objects references has the same owner as the SP)

SQL2005 

You can specify the execution context of a SP (function) to the following values    

CALLER (execute using the identity of the user who executes the SP (is the SQL2000 case)) SELF (execute using the creators identity) OWNER (execute using the owners identity) User_name (specified user)

Execution Context (cont.) 

Examples: CREATE PROCEDURE GetProducts WITH EXECUTE AS SELF AS SELECT * FROM Production.Products CREATE PROCEDURE GetTitles WITH EXECUTE AS ‘dbo’ AS SELECT * FROM Books.Titles

Certificates 

What  



Used in 





Digitally signed document containing public key of a public/private key pair Generated by Certificate Authorities or Certificate server (like SQL Server) Authentication (message signed with private key is guaranteed to come from the owner of the certificate) Encryption (message encrypted with public key can only be decrypted by private key).

Stored in user database (backup, export, import)

Certificate scenarios 

Service Broker  



Authenticate communications Encrypted messages

Merge replication via HTTP 

Uses SSL and certificates

Master keys

Managing master keys 

Service Master key   

Encrypts database master keys Created during installation of instance Must be backed up (and restored during recovery)

BACKUP SERVICE MASTER KEY TO FILE=‘c:\masterkey’ PASSWORD=‘P@ssword’ RESTORE SERVICE MASTER KEY FROM FILE=‘C:\masterke’ PASSWORD=‘P@ssword’

Managing Master Keys (cont)  Database master key 





Stored in database encrypted by given password and also encrypted by service master key (backup database) Used to encrypt certificates and (a)symmetric keys if no encryption password is specified for them. Creating CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword'



Opening (needed when creating keys or certificates) OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssword'



Closing

CLOSE MASTER KEY

Creating certificates 

Create a self signed certificate and associate it with a user CREATE CERTIFICATE [CustomerClientCertificate]

AUTHORIZATION [CustomerClientUser] WITH SUBJECT = ‘My certificate', START_DATE ='02/02/2005‘, EXPIRE_DATE = ’10/10/2005’, ACTIVE FOR BEGIN_DIALOG=ON



Dump a certificate to a file so it can be distributed (rights must be given to associated user), this user can act as a proxy account DUMP CERTIFICATE [CustomerClientCertificate] TO FILE = 'C:\CustomerClient.cer'

Creating certificates (cont.) 

Create a certificate from a file and associate it with a user CREATE CERTIFICATE [CustomerServiceCertificate] AUTHORIZATION [ProxyCustomerServiceUser] FROM FILE = 'C:\CustomerService.cer'

Agenda       

Sql2005 Architecture Administrative Tools Securing SQL2005 Monitoring Sql2005 Maintaining Indexes Disaster Recovery Data availability

Monitoring SQL2005     

Sql Profiler Enhancements DDL Triggers Event Notifications Static Metadata Dynamic Metadata

SQL Profiler Enhancements    

Profiling of Analysis Services Graphical view of deadlock and showplan Ability to save in XML Aggregate data

DDL triggers 

  

User provided code that automatically executes after a DDL operation is performed Is able to rollback the operation Retrieve event information using eventdata() Typical scenarios   

Logging of DDL operations Forcing a naming convention Replicating of DDL statements

DDL Triggers Example create trigger CheckTableName on database for create_table as declare @data XML set @data=eventdata(); if convert(varchar(100),@data.query('data(//O bjectName)')) NOT LIKE 'tbl_%' begin RAISERROR('Invalid table name',10,1) rollback tran end

DEMO DDL triggers

Using DDL triggers for logging and naming conventions

I.P. Marc Mertens

Event Notifications 

Traps SQL Server events   



DML events DDL events Trace events

Uses Service Broker   

Works asynchronously unlike triggers Message type and contract are predefined To use create:   

A queue A service A route

Event Notifications 

Scenario:    



Logging activity on database Security auditing Less impact on the server because it uses the broker services All events can be centrally collected

Warning! 

Is unrelated to notification services

Demo

Using Event Notifications to log ddl events

I.P. Marc Mertens

Static Metadata 

 

Catalog views in the sys schema to retrieve metadata information about databases and database objects. Preferred to querying system tables Examples:   

sys.assemblies sys.databases sys.schemas

Dynamic Metadata 

 

Dynamic management views that provide information about the current state of a server Replaces the pseudo tables of SQL2000 Examples:   

sys.dm_tran_locks sys.dm_os_threads sys.dm_tran_active_transactions

Agenda       

Sql2005 Architecture Administrative Tools Securing SQL2005 Monitoring Sql2005 Maintaining Indexes Disaster Recovery Data availability

Maintaining Indexes 

What’s new?         

ALTER INDEX Online index operations Parallel index operations Locking options Disabling index Included columns Partitioned indexes XML Indexes Database tuning wizard

ALTER INDEX 



New statement that will replace DBCC INDEXDEFRAG, DBCC DBREINDEX and CREATE INDEX WITH DROP EXISTING Used for:    

Rebuilding indexes Reorganizing indexes Disabling indexes Changing index options

Online index operations 

SQL2000   



Creating a index caused a exclusive table lock Rebuilding index caused a shared or exclusive table lock Bottom line, data is unavailable when maintaining indexes

SQL2005 

Allows the creation or altering of a index during which users still have access to the table

Online Index operations 

Example: CREATE INDEX i_authors ON authors(au_lname) WITH ONLINE=ON ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, ONLINE = ON, STATISTICS_NORECOMPUTE = ON)

Parallel index operations 

Problems  



Creating, dropping, altering of a index can monopolize the processors If previous operation are done in parallel deadlocks can occur

Solution 

Control the number of processors that are used via the MAXDOP option in the CREATE INDEX or ALTER INDEX statements

Locking options 

ALLOW_PAGE_LOCKS  



ALLOW_ROW_LOCKS  



On, access to the index is via table or page locks Off, page locks are not used On, access to the index is via table or row locks Off row locks are not used

Scenarios If locking overhead on memory resources is to great, you can reduce it by disallowing row locks or even page locks If you get to many blocking locks, you can enable row locks

Disabling indexes 

Scenario 





Closure programs, cleanup programs and import programs typically do a lot of insert, update and delete operations. Index maintenance can then significantly slow down these programs and indexes can become heavenly fragmented To avoid this, some or all indexes are dropped before the execution of these programs and recreated afterwards. Problem: 



If a clustered index is dropped and recreated, the non clustered indexes will be dropped and recreated during the drop and create. Dropping and creating indexes can be cumbersome

Disabling indexes 

Solution: Disabling indexes  

 

Index is not maintained neither used anymore for optimization. Disabling a clustered index on a table makes the table unusable, however you can disable a clustered index on a view while still using the table Disabled indexes must be rebuild before they can be used again To disable a index or rebuild it, use ALTER INDEX

Included columns 

Problem:  



Covering of a query by a non clustered index is a important design principle However adding key columns to a index increases the number of leaf and nonleaf pages

Solution: 



Included columns can be added to the leaf pages without increasing the number of non leaf pages Example: create index IncludedIndex on Sales.SalesOrderDetail(productid) include (unitprice)

Partitioned indexes 

What? 



Uses a partition function to spread the index rows over different filegroups

Benefits    

Balances load over different files Reduces contention Increases changes of parallel IO operations Possibility to rebuild only one partition of a index

Partitioned index (cont.) 

Scenarios: 





When dealing with history data, only the rows belonging to the last year are changing, so if a index is created on the history date, we should rebuild only the last year part on a regular base. If we partition tables then we must typical partition the indexes in the same way Partitioning of tables and indexes improves availability (if a filegroup is offline, you still access data stored in the other filegroups)

XML Indexes 

What?  



Allows you to create indexes on columns of the XML data type Improves the XQUERY operations on these columns

Typical use 

Semi structured data

Database tuning wizard  

Replaces the index tuning wizard New features:   



Time bound tuning Cross database tuning Support for more physical design structures than just indexes (partitions, indexed views …) XML Output

Demo

Included columns Partitioning of indexes Disabling of indexes

I.P. Marc Mertens

Agenda       

Sql2005 Architecture Administrative Tools Securing SQL2005 Monitoring Sql2005 Maintaining Indexes Disaster Recovery Data availability

Disaster recovery      

Database snapshots Backup operations Restore operations Online restore operations Backup device mirroring Media reliability

Database snapshots 

What? 



Read only, consistent view of the state of a database at a point in time

How?  





Uses sparse files (NTFS is needed) If a page in the database is modified for the first time, the original version is written in the corresponding datafile of the snapshot database. If a modified page is read via the snapshot, the page comes out of the datafiles from the snapshot If an unmodified page is read it comes from the original database

Database snapshots (cont) 

Limitations 

   

Does not protects you against failure of the data, protection is only against user or programming errors Snapshots are read-only Backup, Restore, Attach and Detach are not possible (you can export data however) Can take up extra disk space Impact source database    

Can not alter location of files Can not be dropped Security constraints can not be changed Slows done modifications (especially if there are many snapshots)

Database snapshots (cont) 

Scenarios:     

Protection against dangerous operations Consistent reporting, without locking or row versioning Maintaining a history of different versions of the database Export data Restore database to previous snapshot

Database snapshots (cont) 

Creating a snapshot CREATE DATABASE Production_01012003 ON (NAME=Production_data,FILENAME=‘C:\Snap shots\Production_data_snap.mdf’) AS SNAPSHOT OF Production  A file specification must be given for every data file in the source database



Dropping a snapshot DROP DATABASE Production_01012003

Backup operations 

What’s new?  



No support anymore for NO_LOG, TRUNCATE_ONLY and named pipes Partial backup allows you to backup all the data in read/write filegroups + the specified read-only filegroups. Copy only backups   

Do not change the next differential backups Do not truncate the log if it is a log backup Use if you don’t want to influence the daily backup routine

Restore operations 

What’s new   



Point in time recovery is possible for full and differential backups Partial restore of a partial backup (example recovering of a table) Page level restore allows you to restore only damaged pages, greatly reducing the recovery time Piecemeal restore allows you to restore file groups in stages, allowing users to use the part that is already restored  

Any filegroup for full, bulk logged recovery Only filegroups that where read-only since backup for simple recovery

Online restore 

What? 





The database becomes online, when restoring filegroups or data pages, once the primary filegroup is restored Greatly reduces the perceived downtime for users

How? 

Automatically for the Enterprise edition

Media reliability   

Mirroring Checksum calculation Continue after error

Mirroring 

What?  





Write the backup data to two backup devices If one of the backup media becomes unavailable during backup the backup will fail You can however do the restore using only one backup media

Why? 

To protect you against media failure

Checksum calculation 

What?  



WITH CHECKSUM option for BACKUP and RESTORE Backup will calculate a checksum of the data pages and fails if the checksum is incorrect. The checksums are also written on the backup media. Restore will calculate the checksums and compares them with the checksum of the backup, failing if there is a difference?

Continue after error 

What? 



WITH CONTINUE_AFTER_ERROR option of the RESTORE statement, allows you to continue the restore even if errors are detected during the restore

Why? 

Sometimes wrong data is better than no data at all

Demo

Snapshots Page restore Online restore

I.P. Marc Mertens

Agenda       

Sql2005 Architecture Administrative Tools Securing SQL2005 Monitoring Sql2005 Maintaining Indexes Disaster Recovery Data availability

Data availability 

What’s new  

Database mirroring Clustering  

Up to eight nodes are supported Analysis Services, Notification Services and SQL Server replication can be clustered

Data Availability Attribute Failure detection Automatic failover Perceived downtime Potential data loss Masking of storage failure Special hardware Distance Scope

Clustering Yes Yes 30 seconds + recovery Yes – one copy of data No – shared disk Certified storage and servers 100 miles System and user databases

Log shipping

Database mirroring

No No

No Yes

N/A

3 seconds

Yes - latest transaction log

Yes – in some configuratio ns

Yes

Yes

No

No

Unlimited

Unlimited

User databases

User databases

Database mirroring 

What? 



A database can be mirrored on another server (mirror server), so that if the first server (principal server) becomes unavailable the mirror can take over SQL Native Client Library (SNAC) knows about the mirror and will, if the principal server fails, connect to the mirror (if automatically failover is enabled)

Database mirroring (cont) 

How? 



Log records of the principal server are send to the mirror server and applied to the mirror database Two modes of mirroring 

Synchronous 



The COMMIT of a transaction waits until the mirror server has received the log records of the transaction and applied them

Asynchronous 

The commit does not wait on the send of the log records

Database mirroring (cont) 

Which operation mode? 

Synchronous   



No complete transactions will be lost Transaction performance is determined by communication speed. Supports failover (manual or automatic)

Asynchronous   

Could loose complete transactions No overhead on transactions No failover support (although forcing the use of the mirror is possible)

Database Mirroring (cont) 

Snapshots on mirror 



The mirror database is not available but you can create snapshots on it for reporting.

Issues  



Need to recreate snapshots as mirror is updated Snapshots on mirror can reduce performance of principal in synchronized mode During failover snapshot is not automatically transferred to the former principal

Database mirroring (cont) 

Server roles 

Principal server  



Mirror server  



Contains the principal database Used by the users Server holding the mirror database Unavailable for the users unless a failover or a force takes place

Witness server (optional)  

Used for automatically failover Monitors the principal and mirror server

Manual control 

Manual failover  



Force service on mirror  



Works only in synchronous mode Minimal data loss (incomplete transactions). Mirror becomes principal server and principle becomes mirror. You can lose complete transactions Works in synchronous or asynchronous mode. Mirror becomes principal server and principle becomes mirror upon connection (if enough log is available)

Break mirror

Quorum 

Quorum 





Is a relationship between servers that are in communications with each other (connected state). When quorum exists the session and the servers are said to have quorum. If a server instance becomes disconnected from a mirror session the instance loses quorum. Only applicable for synchronous mode

Quorum (cont.) 

High-Protection mode (no witness server, synchronous mode) 





Quorum requires principal and witness. If either partner loses quorum both partners lose quorum Failover is only possible if the quorum exists, you can force the mirror however. The database becomes unavailable if the quorum does not exists

Quorum (cont). 

High-Availability mode (witness server)  



Quorum involves principal (p), mirror (m) and witness (w) Full quorum (p,m,w), every server fulfills its own role unless a manual failover takes places. Quorum (p,w), the principal database is set to disconnected but is still available and mirroring is in suspended state. If the mirror becomes available again it regains quorum as mirror and begins resynchronizing

Quorum (cont). 

High-Availability mode (witness server) 

Quorum (m,w) 



Automatically does a failover, mirror becomes principal and principal will become mirror when started. Database is still available (SNAC clients connect to new principal)

Partner to partner Quorum (p,m) 

No automatically failover is possible and the session temporarily operates as HighSecurity mode. Manual failover is possible

Asynchronous mode  



Witness server can still be used, but has no functionality If principal server becomes unavailable you can manual force service on the mirror If mirror server becomes unavailable the principal database is still available. Mirror must be recovered and the session must be resumed

Setting up mirroring 

Create endpoints for principal, mirror and if needed witness

CREATE ENDPOINT endpoint_mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 5023 ) FOR DATABASE_MIRRORING (ROLE=PARTNER); CREATE ENDPOINT endpoint_mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 5024 ) FOR DATABASE_MIRRORING (ROLE=WITNESS);

Setting up mirroring (cont)  



Backup the principal database. Restore the principal database backup on the mirror server with the norecovery option. Create partnership on the mirror ALTER DATABASE TestMirror SET PARTNER='TCP://boojum:5022'



Create partnership on the principal ALTER DATABASE TestMirror SET PARTNER='TCP://boojum:5023'



Change operation mode if needed ALTER DATABASE TestMirror SET SAFETY off

Setting up mirroring (cont) 

Establish a witness server if needed ALTER DATABASE TestMirror SET WITNESS='TCP://boojum. :5024'

Controlling mirroring 

Manual failover ALTER DATABASE TestMirror SET PARTNER FAILOVER



Force service to mirror ALTER DATABASE TestMirror SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS



Break mirror ALTER DATABASE TestMirror SET PARTNER OFF

Demo

Mirroring

I.P. Marc Mertens

Questions?

I.P. Marc Mertens

Related Documents

Sql Server 2005 Whats New For The Dba
November 2019 1
Sql Server 2005 Dba Training
April 2020 3
Sql Server Dba Best Practices
November 2019 5
Sql Server 2005 Advantages
November 2019 15
Sql Server 2005 Architecture
November 2019 14
Sql Server 2005 Presentation
June 2020 11

More Documents from "api-26344848"

Sql 2005 Disk Io Performance
November 2019 1
Sql Server 2005 Whats New For The Dba
November 2019 1
Ug - Procedure Best Practices V2
November 2019 0

Copyright © 2024 PDFCOKE.