Wednesday, November 10, 2010

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






Entity or Record

More than one row

Record Set


Relational DataBase Management System




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


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


  • Select
  • Update
  • Delete
  • Insert


  • 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)



  • 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

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



OS/SQL on Mirror or RAID5

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

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



Windows authentication

Mixed Authentication (Windows and SQL)



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


Authentication Mode

Data directories





After installation check SQL Server Configuration

Check the services

Check the Protocols









































No comments:

Post a Comment