Implementing and Maintaining Microsoft SQL Server 2005

1
Implementing and Maintaining Microsoft SQL Server
2005
Implementing and Maintaining Microsoft SQL Server
2005
Bülent Sözge
Yazilim Egitimleri Ürün Müdürü
2
IÇERIK

• SQL SERVER 2005e GENEL BAKIS
• PARTITION TABLE
• GÜVENLIK VE SCHEMA KAVRAMI
• SQL SERVER 2005 ILE DISASTER RECOVERY
• SQL SERVER 2005IN MONITÖR EDILMESI
• SQL SERVER 2005 ILE HIGH AVAILABILITY ÇÖZÜMLERI
• SQL SERVER 2005 ve .NET FRAMEWORK ENTEGRASYONU

3
SQL SERVER 2005E GENEL BAKIS
SQL Server Integration Services
Notification Services
Analysis Services
SQL Server 2005
Relational Database Engine
Full-Text Search
Reporting Services
.NET CLR
Service Broker
Replication
Native HTTP Support
4
PARTITION TABLE

• Table data spread across multiple file groups
• Improved performance on multiprocessor/multidrive
  systems
• Easier management of large tables

5
PARTITION TABLE NEDIR?
Data is partitioned horizontally by range
lt 2003
2004 -- 2005
gt 2005
2003 -- 2004
Sales.Orders
6
PARTITION FUNCTION

• Partition functions define partition boundaries
• Boundary values can be assigned to LEFT or RIGHT

lt 2003
2003 -- 2004
2004 -- 2005
gt 2005
CREATE PARTITION FUNCTION pf_OrderDate
(datetime) AS RANGE RIGHT FOR VALUES
('01/01/2003', '01/01/2004', 01/01/2005')
7
PARTITION SCHEME

• A partition scheme assigns partitions to
  filegroups
• The next filegroup can also be defined

lt 2003
2003 -- 2004
2004 -- 2005
gt 2005
CREATE PARTITION SCHEME ps_OrderDate AS PARTITION
pf_OrderDate TO (fg1, fg2, fg3, fg4, fg5)
8
HANGI OPERASYONLAR UYGULANABILIR?

• SWITCH Swap a populated table or partition with
  an empty table or partition
• MERGE Combine two adjacent partitions into a
  single partition
• SPLIT Insert a boundary in an existing partition
  to create a new partition

SWITCH

SPLIT
lt 2003
2003 -- 2004
2004 -- 2005
2005 -- 2006
gt 2006
MERGE
9
SQL SERVER 2005 GÜVENLIGI
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
Group
Database
10
SCHEMA NEDIR?
Namespaces for database objects
Person
Contact
(Server1.AdventureWorks.Person.Contact)
Sales
Customer
(Server1.AdventureWorks.Sales.Customer)
dbo
ErrorLog
AdventureWorks
(Server1.AdventureWorks.dbo.ErrorLog)
11
SCHEMA NASIL ÇALISIR?
12
BACKUP TIPLERI
13
DDL TRIGGERLAR

• Triggers to trap DDL statement execution
• Database or server scope

Process
UPDATE STATISTICS SomeTable
DDL statement executed
1
DDL action performed
2
Trigger fires
EventData
3
14
DDL TRIGGER NASIL ÇALISIR?
Define the trigger name, scope, and event
Retrieve event information using eventdata()
Extract event data by using XQuery
CREATE TRIGGER UpdStats ON DATABASE FOR
UPDATE_STATISTICS AS ...
CREATE TRIGGER UpdStats ON DATABASE FOR
UPDATE_STATISTICS AS DECLARE _at_data XML . . . SET
_at_data eventdata() . . .
CREATE TRIGGER UpdStats ON DATABASE FOR
UPDATE_STATISTICS AS DECLARE _at_data XML DECLARE
_at_database NVARCHAR (100) SET _at_data
eventdata() SET _at_database _at_data.value('(/EVENT_I
NSTANCE/DatabaseName)1', 'nvarchar(100)') . . .
15
HIGH AVALIABILITY
16
DATABASE MIRRORING

• Alternative to failover clustering
• Mirror Server
• Provides a hot standby
• Witness Server
• Monitors the primary and mirror database servers
• Client redirection
• Automatic client redirection

17
DATABASE MIRRORING NASIL ÇALISIR?
Principal Database
Mirror Database
Client
18
.NET FRAMEWORK ENTEGRASYONU

• Database objects implemented in managed code
• Stored procedures
• User-defined data types
• User-defined functions
• Triggers
• User-defined aggregates

.NET Assembly
Agg
SP
Trgr
UDT
UDF
19
Sorular ?
20
Küresel Isinma (Global Warm)
21
Bunlari Yapmazsak

• Elektrik tüketimi daha düsük bilgisayarlar
  alinmali.
• Masaüstü PC yerine dizüstü bilgisayarlar tercih
  edilmeli.
• Yazicidan kagit çiktisi alinmasi asgariye
  indirilmeli.
• Bilgisayarlar bekleme konumunda birakilmamali.
• Kullanilmayan bilgisayarlar atilmamali.
• Gereksiz kâgitlar müsfette kullanim için
  saklanmalidir.

22
Bunlari Yapmazsak

• Enerji dostu ampuller kullanilmali.
• Televizyonlar bekleme konumunda birakilmamali.
• Evler isi kaybina karsi yalitilmali.
• Esyalar, radyatörleri kapatmayacak
• sekilde yerlestirilmeli.
• Daha az su tüketen yeni teknoloji
• rezervuarlar kullanilmali.
• Dis firçalama, bulasik yikama,
• tras esnasinda musluk açik birakilmamali.
• Yazicidan çikarilacak dokümanlarin kenar
  bosluklari ve font büyüklükleri azaltilmali.
• Ofislerde lambalarin tamami yerine, belirli bir
  kismi kullanilmali.

23
Böyle Olacak
24
Tesekkürler...