SQL Server 2005 Database Administration and Scalability

1
SQL Server 2005 Database Administration and
Scalability

• Peter ter Braake
• CompuTrain

2
Agenda

• High Availability
• Database mirroring
• Database snapshot
• Partial Availability
• Partitioned tables
• Security
• Encryption
• Snapshot isolation

3
High Availability
Kunnen gebruikers doen wat ze moeten doen wanneer
ze het moeten doen?

• Waarom niet?
• Site is onbereikbaar
• Systeem is niet beschikbaar
• Database is niet beschikbaar
• Database is slechts deels beschikbaar
• Tabel is niet beschikbaar
• Data is niet beschikbaar

• Clustering
• Log shipping
• Replication (transactional)
• Mirroring

4
Wat Is Database Mirroring?

• Log shipping with automatic failover
• per database
• Clustering on database level without single point
  of failure (without shared disk)

5
Wat Is Database Mirroring?
Quorum?
Witness
Snapshot?
Principal Database
Mirror Database
Client
6
Rollen

• Principal de productie database
• Mirror de partner van de principal die alle
  transacties uit de log ook uitvoert (REDO)
• Is in Recovery state
• Is niet benaderbaar
• Witness Controleert via uitgebreide hartbeat
  de beschikbaarheid van beide partners en bepaalt
  in overleg (quorum) wie welke rol speelt

7
3 manieren van opzetten

• High availability synchroon met witness
• automatic failover en manual failover
• Safety FULL
• High protection synchroon zonder witness
• Alleen manual failover (Alter Database Set
  Partner Failover)
• Safety FULL
• High performance asynchroon zonder witness
• Alleen forced failover (Alter Database Set
  Partner Force_Service_Allow_Data_Loss)
• Safety OFF

8
Randvoorwaarden

• Principal moet gebruik maken van FULL recovery
  model
• Mirror is geïnitialiseerd with backup of
  principal with NO RECOVERY
• Databases hebben dezelfde naam
• Maak Endpoints (for DATABASE_MIRRORING)
• Elke SQL Server instance login moet het recht
  hebben een connectie met de andere servers te
  maken via endpoints (gebruik certificaten bij
  non-trusted domains)
• Zelf zorgen voor gelijke server objecten zoals
  logins, linked servers, mail settings etc.

9
DEMO

• Northwind gemirrored

10
State waarin partner kan verkeren

• Exposed principal is operating but no log
  records are send to mirror
• Cannot serve the database server is operational
  but cannot get a quorum therefore denies access
  to the database
• Isolated no contact with other servers in the
  mirroring session

11
principle loss, then new principle loss
12
Principle loss, then witness loss
13
Stappen

• (start SQL Server met traceflag 1400)
• Backup database
• Restore database on Mirror server(incl.
  eventuele log backups)
• Create Endpoints
• Alter database set partner N
• Client connection string Data
  SourceAFailover PartnerB

14
Database Snapshot

• Point in time kopie van de database
• Copy on write principe
• Read-only database

15
(No Transcript)
16
Agenda

• High Availability
• Database mirroring
• Database snapshot
• Partial Availability
• Partitioned tables
• Security
• Encryption
• Snapshot isolation

17
Database Components

• Database consists of
• Filegroups consist of
• Files consist of
• Extents consist of
• Pages consist of data

Primary
Readwritefilegroup
File1
File2
2004
File3
Readonlyfilegroups
0
4
8
12
16
20
24
28
File Header
2003
File4
1
5
9
13
17
21
25
29

File5
2002
2
6
10
14
22
18
30
26
2001
File6
3
7
11
15
19
23
27
31
extent0
extent1
extent2
extent3
Log
18
Partitioning

• Functional partitioning
• niets nieuws
• B.v. Archived data in aparte filegroup op aparte
  schijf.
• RAID level?
• Backup strategie?
• Partitioned tables (indexes)
• Nieuw in SQL Server 2005
• Automatic horizontally partitioned view

19
Voordelen van Partitioning

• Makkelijker/sneller sliding window scenarios
• Partition manipulation outside of active table
• Piecemeal Backup
• Van actieve delen vaker een backup maken
• Partial Database Availability
• In SQL Server 2005 kan de database gedeeltelijk
  offline zijn
• Online Piecemeal Restore
• Tijdens het restoren zijn goede filegroups
  gewoon beschikbaar

20
DEMO

• Partitioning

21
Partitioned Tables (Indexes)

• Tabel opgeslagen op meer filegroups
• Verbeterde performance met multiprocessor/multidri
  ve systemen
• Eenvoudiger management van grote tabellen
• Eerder beschikbaar hebben van actieve data met
  online-piecemeal-restore

22
Stappen

• Create partition function
• Datatype
• Grenswaarden
• Create partition scheme
• Map groepen naar filegroups
• Create table
• On partition scheme

23
Scenario 1 disk gaat kapot(tabel is partitioned)

• SQL Server 2000
• Database is suspect
• Gebruikers kunnen database niet benaderen

• SQL Server 2005
• Filegroup is offline
• Gebruikers kunnen overige data gewoon benaderen

24
Verschillen SQL Server 2000 en 2005 tijdens
recovery

• In SQL Server 2000
• Database is in a restoring state
• Users are unable to access the database
• File needs to be recreated and zero initialized
• File Restore can proceed offline
• In SQL Server 2005
• Filegroup is in a restoring state
• Users are able to access undamaged data
• File can be recreated with instant file
  Initialization
• Piecemeal Restore can proceed online

25
Agenda

• High Availability
• Database mirroring
• Database snapshot
• Partial Availability
• Partitioned tables
• Security
• Encryption
• Snapshot isolation

26
Security Enhancements

• Password policy for SQL Server logins
• Hierarchical security scopes
• Separation of user and schema
• Limited metadata visibility
• Declarative execution context
• Execute As Caller Self Owner user name
• Create, Alter, Drop
• Certificates
• Encryption

27
Wat zijn Permissions?
Securables
Permissions
Principals
Windows Group
Files
ACL
Domain User Account
Registry Keys
Local User Account
Windows
GRANT/REVOKE/DENY
CREATE ALTER DROP CONTROL CONNECT SELECT EXECUTE U
PDATE DELETE INSERT TAKE OWNERSHIP VIEW
DEFINITION BACKUP
SQL Server Login
Server
Server Role
SQL Server
Database
User
Schema
Database Role
Application Role
Database
28
Wat zijn Schemas?
Namespaces voor database objecten
dbo
Products
(Server1.AdventureWorks.dbo.Products)
SalesData
Orders
(Server1.AdventureWorks.SalesData.Orders)
29
Waarom encryption

• Nieuwe wetgeving (Sarbannes-Oxly, European Union
  Data Protection Directive, )
• Nieuwe features zoals Service Broker
• Voordelen
• Creation of a simple and easy-to-use, yet robust,
  encryption key management framework
• Ability to encrypt sensitive data without the
  need to encrypt an entire store
• Enable decryption within a view and easy access
  to encrypted data
• Ability to created digitally signed sprocs and
  simplify data encryption

30
Vormen van Encryption

• Symmetrisch zelfde sleutel voor versleutelen en
  ontsleutelen. Relatief snel, maar ook relatief
  makkelijk te kraken.
• Asymmetrisch gebruikt een private en een public
  sleutel en gebruikt de ene voor het versleutelen
  en de andere voor het ontsleutelen.
  Waarschijnlijk te traag voor grote hoeveelheden
  data.
• Combinatie gebruik symmetrische versleuteling
  voor de data, maar versleutel de symmetrische
  sleutel asymmetrisch.

31
Symmetric encryption process

• Algoritmen
• RC2
• RC4
• DES
• AES

32
Asymmetric encryption process
Algoritme RSA (512, 1024, 2048)
33
Hybrid Encryption
34
Asymetrisch versleuting

• Scenario Piet wil Jan een bericht sturen
• Piet vraagt Jans public key
• Piet versleuteld bericht middels deze key
• Jan ontsleuteld bericht middels zijn private key
• Probleem wat als Piet denkt Jans public key te
  krijgen, maar die van Boris Boef krijgt?

35
Certificaten

• document dat een identiteit koppelt aan een
  public key en wordt uitgegeven door een
  certificate authority
• vergelijk paspoort (certificate) koppelt
  handtekening (public key) aan naam
  (identiteit))
• Digitally signed (reverse process of encryption
  encrypted using a private key and everybody being
  able to decrypt using the public key knows for
  certain who signed it)
• SQL Server complies with IETF X.509v3

36
Hiërarchie
37
SQL Server 2005

• Service Master Key
• Gemaakt tijdens setup
• Gebaseerd op Windows DPAPI (Data Protection API)
• Zelf versleuteld op basis van Service Account
• Pas op met SQL Service account wijzigen
• Gebruikt om Instance level instellingen (b.v.
  linked servers) te versleutelen
• Zorg voor backup
• Database Master Key
• Maak middels Create Master Key statement
• Versleuteld middels password Service Master Key
• Gebruikt bij maken van certificaten /
  asymmetrische sleutels

38
DEMO

• Encryptie

39
Samenvatting

• Create master key
• Create certificate
• Create symmetric key
• EncryptByKey
• DecryptByKey / DecryptByKeyAutoCert
• Execute As
• Datatype must be varbinary

40
best practice

• Data transfer pass decrypted (clear text)
  through an encrypted tunnel
• Data at rest use SQL Server encryption features
• This way keys do not need to be shared!

41
Agenda

• High Availability
• Database mirroring
• Database snapshot
• Partial Availability
• Partitioned tables
• Security
• Encryption
• Snapshot isolation

42
Theorie

• A
• C
• I
• D

Atomicity
Consistency
Isolation
Durability
43
Concurrency problemen

• Lost updates
• Uncommitted dependency (dirty read)
• Inconsistent Analysis (nonrepeatable read)
• Phantom reads

44
Oplossingen

• Locking (pessimistic)
• SQL Server 2000 and earlier
• DB2
• Versioning (optimistic)
• Oracle

45
Isolation levels

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

SNAPSHOT READ COMMITTED SNAPSHOT
46
DEMO

• SNAPSHOT ISOLATION

47
When what?

• Read Uncommitted OLTP performance above
  everything else
• Read Committed (locks) only transactional
  consistent data be read
• Read Committed (ss) point-in-time consistency
  for long running queries and aggregations (single
  query reports)
• Repeatable read absolute accuracy for long
  running multi-statement transactions and must
  hold all requested data from other apps until the
  transaction completes
• Snapshot absolute accuracy for long running
  multi-statement transactions but does not plan to
  change it
• Serializable As repeatable read but then for
  sets instead of single rows (no data can be added
  to the set by others)

48
Possible scenarios for snapshot

• Use AS through the UDM to report directly on a
  transactional system, using snapshot isolation to
  prevent locking and blocking
• Good when contention is costing throughput
• Transactional Replication to offload reporting to
  another server. Heavy queries on the subscriber
  might block replicated data from arriving. - use
  snapshot isolation in subscriber
• Mixed workloads on transactional systems
  (statement level)
• Ad-hoc reporting on live data (especially when
  heavily updated)

49
2 possibilities

• Read Committed with Snapshot Isolation
• DB Setting (so no app. Change required)
  READ_COMMITTED_SNAPSHOT DB option
• Statement level
• Best used for existing reporting systems where
  most reports are based on a single query
• Each statement (even within same transaction)
  sees the changes that were committed before the
  statement began. (normal read committed before
  resource was accessed)
• Snapshot Isolation
• Isolation level setting (SET TRANSACTION
  ISOLATION LEVEL )
• Transaction level
• Best used in more complex, long running reports
• Each statement within the same transaction sees
  the same data while the data is available for
  modifications outside of the transaction
• ALLOW_SNAPSHOT_ISOLATION DB option must be ON

50
Samengevat
51
Performance

• Write slower because versions need to be made
• Read slower because SQL Server must traverse the
  version chain
• Versions are kept in TempDB

52
(No Transcript)
53
Tot Slot

• High Availability
• Database mirroring
• Database snapshot
• Partial Availability
• Partitioned tables
• Security
• Encryption
• Snapshot isolation

Vragen?pbraake_at_computrain.nl www.sqlpass.nl
54
T-SQL

• Alter Database Nwind Set Safety Full
• Connection string"Data SourceAFailover
  PartnerBInitial CatalogNwindIntegrated
  SecurityTrue
• Sys.database_mirroring_endpoints
• Sys.database_mirroring
• Sys.database_mirroring_witnesses

55
Phrases

• Exposed principal is operating but no log
  records are send to mirror
• Cannot serve the database server is operational
  but cannot get a quorum therefore denies access
  to the database
• Isolated no contact with other servers in the
  mirroring session

56
principle server loss
57
principle loss, then new principle loss
58
Principle loss, then witness loss
59
mirror loss, then principle loss
60
witness loss, then principle loss
61
Scenario with comm. Loss(3 independent lines)
62
Principal states
63
Principle states
64
Data management Views

• sys.dm_tran_active_snapshot_database_transactions(
  )
• sys.dm_tran_active_transactions
• sys.dm_tran_current_snapshot()
• sys.dm_tran_current_transaction()
• sys.dm_tran_database_transactions
• sys.dm_tran_locks
• sys.dm_tran_session_transactions
• sys.dm_tran_top_version_generators()
• sys.dm_tran_transactions_snapshot()
• sys.dm_tran_version_store()

65
Perfmon SQLServerTransactions

• Free space in TempDB
• Size of version store
• Rate of growth
• Number of conflicts
• Longest running active transaction