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
Relational DataBase Management System
Data Definition Language
Purpose of SQL:
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"
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)
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
- SQL Database
- SQL Agent
For security reasons (best practices), every service should be running under a different domain account
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)
NDF files (more)
Filestream (large blocks of data out of the database itself) - separate files on a NTFS file - they do need to be backed up!!!
Why more instances?
- Security; An instance is like a Forest , its the security boundary.
- 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)!!!!
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!!!
Installing SQL 2008
Pre -Reqs: .NET Framework 3.5 = right WIndows Edition
Installation: Setup files
What to install
After installation check SQL Server Configuration
Check the services
Check the Protocols