SQL 2008

070-450, Pro - Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008

 
 

SQL Server Configuration Manager: all SQL Services and what protocol is used

SQL Server Management Studio: Managing your SQL server

  
 

Terminology;

Column	Domain
Row	Entity or Record
More than one row	Record Set
RDBMS	Relational DataBase Management System
DML	ANSI SQL
DDL	Data Definition Language

  
 

Purpose of SQL: 

• Network
  
• management
  
• storage
  
• code/triggers/reports
  

  
 

 Each vendor comes up with their own variant of the SQL Language, Microsofts is Transact-SQL (T-SQL)

Data is organized in Pages, each page is max. 8KB but when you don't have enough space you can point to another Page = BLOB (Tekst or Binary data), 250 KB or less OR pointing to a(nother) file = FILESTREAM (2 GB!!)

A file(stream) does not live in an MDF or NDF file so when making a backup you have to backup these files. BLOBs live in different pages (and exist therefore in MDF or NDF)

  
 

A Transaction log is a kind of TO DO list. The only time in SQL that you really lose data is if you lose a database file AND the Transaction log file. Therefore keep your log file on a separate disk or backup the Transaction logs more times a day

 Index;

• Clustered Index (in order)
  
• Non-Clustered Index
  
• "Covering Index"
  

 DML;

• Select
  
• Update
  
• Delete
  
• Insert
  

 DDL;

• Create
  
• Drop
  
• Alter
  

  
 

  
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

Lesson 3

  
 

You can run more SQL Instances on 1 physical machine

If you do not specify a servername\SQL Instance you will be connected to the default instance.

  
 

You need different instances to have different serverwide configurations (security), another reason is for stability (CPU - Memory for specific instance)

  
 

Resources:

• CPU
  
• Memory
  
• Disk
  
• Network
  

  
 

Every instance needs a certain amount of these physical resources

  
 

  
 

You have SQL Services which are machine wide and you have SQL services per instance

  
 

  
 

Per Instance;

• SQL Database
  
• SQL Agent
  
• Reporting
  
• Analysis
  

For security reasons (best practices), every service should be running under a different domain account

  
 

Per machine;

• Full-Tekst
  
• Browser
  
• Integration
  

  
 

msdn.microsoft.com/en-us/library/cc281953.aspx

By default you have an MDF and an LDF file and sometimes you will  have an NDF-file (secondary MDF file)

  
 

File Placement strategies, its all about performance and reliability

Put your LDF (t-log) on a separate disk!!

OS files on their own disk

SQL Server program files on their own disk (or SQL Program files and OS files on one disk)

MDF files

NDF files (more)

MDF+NDF (Filegroup)

Filestream (large blocks of data out of the database itself) - separate files on a NTFS file - they do need to be backed up!!!

  
 

  
 

Lesson 4

  
 

Why more instances?

• Stability
  
• Security; An instance is like a Forest , its the security boundary.
  
• Performance
  
• Configuration (for example Windows or SQL Authentication mode)
  
• Compliance (separate instance  for security/auditing/backup and restore/ db config)
  

  
 

Be carefull for TOO MANY INSTANCES!

  
 

Start with a single instance

  
 

Storage;

OS/SQL on Mirror or RAID5

The more physical drives used, the better (to spread the files)

LDF files on separate disk(s)!!!!

  
 

Security;

Windows authentication

Mixed Authentication (Windows and SQL)

  
 

Availability;

Storage: log files separate from MDF

Use Windows Clustering, Windows Clustering needs to be installed on al the nodes

Learn Windows Clustering!!!

  
 

Lesson 5

  
 

Installing SQL 2008

Pre -Reqs: .NET Framework 3.5 = right WIndows Edition

Installation: Setup files

What to install

Service Accounts

Collation

Authentication Mode

Data directories

FIlestreaming

Analysis

Reporting

  
 

After installation check SQL Server Configuration

Check the services

Check the Protocols