Exam 70-432 TS: Microsoft SQL Server 2008, Implementation and Maintenance - My Notes as of 5/3/09

Well, here's where I'm at so far:

Requirements:
1. Install SQL Server 2008 and related services - file locations; default paths; service accounts

http://msdn.microsoft.com/en-us/library/ms143547.aspx

• NTFS is required for installation, but if you upgrade FAT32 won't be blocked.
• Read-only and compressed installs are blocked.
• Dot Net requires the restart
• MSDE will upgrade to Express
• 2K5 Express will go to Workgroup
• Evaluation will upgrade to "real" versions
• IS and client components are not Instance related
• The Windows firewall may close TCP port 1433, which is the default port for SQL Server. UDP Port 1434 is the SQL Browser service, and if you have multiple instances you need to open that as well
• SQL Server 2008 is supported in virtual machine environments running on the Hyper-V role in Windows Server 2008 Standard, Enterprise and Data Center editions.
• If you use EFS, the database files will be encrypted under the identity of the account running SQL Server
• SQL Server 2008 will install side-by-side with everything from 2K up, with the exception of IA64
• Windows 2008 Server Core not supported since there is no dot-net.
• Installation requires Shared Memory, Named Pipes, or TCP/IP protocols
• For server, you need Windows 2003 Standard SP2 or higher
• Use Unicode to store multi-lingual data
• SQL Server 2008 supports setting collations at the following levels:

1. Server
2. Database
3. Column
4. Expression

• The server-level collation can either be changed during setup, or by changing the Windows system locale before installation.
• Binary is the fastest sorting order and is case-sensitive, but can yield unexpected sort orders.
• Three editions have all features in SQL Server: Enterprise, Developer and Evaluation. Only Enterprise can be used in Production.
• http://msdn.microsoft.com/en-us/library/ms143504.aspx - Service Accounts
• Use the Configuration Manager to change the service accounts - it does things other than just set the account
• SQL Configuration Manager works only for the local instances.
• The DBTA requires an administrator to run the first time, then a dbo can run it on their own databases after that
• For SQL Server and SQL Server Agent to run as services in Windows, SQL Server and SQL Server Agent must be assigned a Windows user account.
• IS is not Instance-aware
• The Workgroup edition is where the memory limits start - at 4GB. Express is 1 GB.
• By default, sample databases and sample code are not installed as part of SQL Server Setup.
• Up to 16 Instances are available for install on all editions except Enterprise, which can have 50.
• You need administrative rights to configure Reporting Services.
• To update an expired service account for Reporting Services you must use SSMS.
• Currently stopped at: http://msdn.microsoft.com/en-us/library/bb510455.aspx
• http://msdn.microsoft.com/en-us/library/cc281953.aspx

 

2. Configure SQL Server instances - sp_configure

http://msdn.microsoft.com/en-us/library/ms188787.aspx

 

Configure SQL Server services - configuration manager; SQL browser

http://msdn.microsoft.com/en-us/library/ms174212.aspx

http://msdn.microsoft.com/en-us/library/ms181087.aspx

 

Implement database mail - set up and configure

http://msdn.microsoft.com/en-us/library/ms175887.aspx

 

Configure full-text indexing - enable/disable, index population

http://msdn.microsoft.com/en-us/library/ms142571.aspx

• Full Text Indexes work on Filestream, XML and MAX character columns.
• A Full Text Catalog contains one or more Full Text Indexes
• The IN PATH clause is deprecated
• Full Text Catalogs should have their own FileGroup
• Full Text Indexes require a type column for binary data
• Full Text Indexes require a single column to make the row unique
• You can use Contains and FreeText operations to search Full Text data - Contains has more flexibility
• Pass all Full Text terms as Unicode

 

Manage SQL Server Agent jobs - create and schedule jobs; notification of job execution; disable/enable jobs; change job step order; logging

http://msdn.microsoft.com/en-us/library/ms187061.aspx

• You can find errors in the msdb.sys.jobhistory table.

 

Manage SQL Server Agent alerts - performance condition alerts; SQL event alerts; Windows Management Instrumentation (WMI) alerts

http://msdn.microsoft.com/en-us/library/ms191508.aspx

• You can create alerts on performance counters, SQL Server Errors and WMI queries.
• Alerts can send notifications or start a job.

 

Manage SQL Server Agent operators - operator schedules; fail safe operator; add a new operator; notification methods

http://msdn.microsoft.com/en-us/library/ms179336.aspx
 

Implement the declarative management framework (DMF) - create a policy; verify a policy; schedule a policy compliance check; enforce a policy; create a condition

http://msdn.microsoft.com/en-us/library/bb510667.aspx

 

Back up a SQL Server environment - operating system-level concepts

http://msdn.microsoft.com/en-us/library/ms187048.aspx

• The EMERGENCY mode of a database allows SELECT operations by members of the db_owner role.
• You cannot add a file or shrink a database during backups.
• The "Mirror To" statement can make up to 4 copies of a backup, but they must be the same type and use the "WITH FORMAT" parameter.
• Differential Backups copy pages, not transactions.
• You must backup the public and private keys to access encrypted data, if it uses both.
• A Full or Filegroup backup is required to begin a restore sequence.
• The "Standby" option allows you to issue queries while you are restoring transaction logs.
• The EMERGENCY state of a database allows SELECT operations by the db_owner role.
• To restore a database page:
• Find the PageID of the damaged page
• RESTORE with PAGE = (page's ID number)
• Restore differential backups
• Restore transaction logs
• Create a transaction log backup
• Restore it.

• To restore from damaged media, use the CONTINUE_AFTER_ERROR statement
• If you have database errors, immediately perform a transaction log backup.
• To restore an encrypted backup, you must have the certificate or password to regenerate the Database Key.

 

Manage logins and server roles- create logins; disable/enable logins; security model (authentication mode); password policy enforcement; fixed server roles; alter logins

http://msdn.microsoft.com/en-us/library/bb510476.aspx

• Endpoints have a transport and a payload, and include TCP and HTTP
• HTTP Endpoints use SOAP calls.
• Sp_configure now handles the SAC tool features.

 

Manage users and database roles - user mapping; user-defined roles; fixed roles; guest, public, dbo; creating and deleting user roles

http://msdn.microsoft.com/en-us/library/ms189121.aspx

• Principals can be: SQL Server, Windows user, Windows group, certificate, or an asymmetric key.
• The securityadmin server role cannot change passwords.
• The setupadmin role can link servers.
• Login-less users replace application roles.
• The db_backupoperator can back up a database but cannot restore it.

 

Manage SQL Server instance permissions- logon triggers; permissions vs fixed role membership; cross-database ownership chaining; impersonation

http://msdn.microsoft.com/en-us/library/ms191291.aspx

• To grant rights to dm_exec_requests, you need the VIEW SERVER STATE permission.
• Signatures allow you to elevate a principal to be able to run code, to bridge the gap in an ownership chain.
• Metadata within SQL Server is secured.
• You cannot impersonate a principal that has been mapped to a certificate or master key.

 

Manage schema permissions and object permissions- manage schema ownership, Keys

http://msdn.microsoft.com/en-us/library/ms190387.aspx

• The Server Master Key, which is encrypted by the SQL Server service account, encrypts a Database Master Key.
• Asymmetric keys and certifications provide the highest degree of data security.
• An encrypted column cannot be indexed.
• A Hash is a one-way encryption algorithm.

 

Audit SQL Server instances- use DDL triggers and logon triggers; C2; common criteria; login failures; event notifications

http://msdn.microsoft.com/en-us/library/ms187634.aspx

http://msdn.microsoft.com/en-us/library/bb153837.aspx

http://msdn.microsoft.com/en-us/library/ms186406.aspx

http://msdn.microsoft.com/en-us/library/cc645917.aspx

• Change Tracking is enabled on a database, and enabled for a table.
• Not all DDL Statements execute within a transaction.
• The Eventdata() function provides XML with a structure that depends on the event.
• You first create a Server Audit object and then tie that to a Database Audit Specification.
• C2 auditing has been superceded by the Common Criteria.

 

Manage transparent data encryption- impact of transparent data encryption on backups

http://msdn.microsoft.com/en-us/library/bb934049.aspx

 

Manage and configure databases- files, file groups, and related options; database options; recovery model; attach/detach data

http://msdn.microsoft.com/en-us/library/ms187087.aspx

• The proportional file algorithm balances writes so that all files in a Filegroup fill up at the same time.
• SQL Server creates one thread per file in a database (not sure about this - author could be wrong).
• Always create a second Filegroup and designate it as the default for best performance.
• The Filestream feature allows you to associate files on a disk with a database, tied to a Filegroup.
• Index operations affect bulk-logged recovery models.
• The restricted_user option allows only the db_owner, db_creator and sysadmin roles to have access.
• Issuing ALTER DB statements wait for all transactions to complete unless you specify a ROLLBACK qualifier.

 

Manage database snapshots- create, drop, revert

http://msdn.microsoft.com/en-us/library/ms175158.aspx

• To create a database snapshot, you must use the same logical filenames.
• The maximum size of a database snapshot will only ever be as large as the size of the original database.
• You can restore a database from a snapshot.
• Filestream and Full Text is not compatible with many backup and restore features.

 

Maintain database integrity- DBCC CHECKDB; suspect pages

http://msdn.microsoft.com/en-us/library/ms176064.aspx

• You should enable PAGE_VERIFY for production databases.
• If a database is mirrored, it can recover a corrupt page from the mirror.

 

Maintain a database by using maintenance plans- Maintenance Plan Wizard; Maintenance Plan Designer

http://msdn.microsoft.com/en-us/library/ms187658.aspx

 

Import and export data- BCP; BULK INSERT; OPENROWSET; GUI tools

http://msdn.microsoft.com/en-us/library/ms162802.aspx

http://msdn.microsoft.com/en-us/library/ms175915.aspx

• BCP needs only SELECT, INSERT and ALTER TABLE permissions.
• Queryout limits data exported
• Triggers are disabled by default for BCP.
• SSIS does not understand Hierarchy or spatial data types

 

Manage data partitions- switching data from one partition to another; add a Filegroup; alter a partition function; alter a partition scheme

http://msdn.microsoft.com/en-us/library/ms188730.aspx

• Process for creating a partitioned table, index or view:
• Create a partition function
• Create a partition scheme, mapped to the partition function
• Create an object on the scheme

• All columns used to partition a populated table must be deterministic
• You can partition a populated table, but you have to recreate the indexes
• You can have 1000 partitions for objects (not sure about this one)
• Using the ALL qualifier limits you to one per FileGroup
• Filegroups must already exist to be used in a scheme
• You need enough FileGroups +1 to hold all of the partitioned data
• The SWITCH operator has several requirements,  one of which is that the data and indexes must be aligned.
• For SWITCH to work you cannot move data from one FileGroup to another or exchange partitions with data.
• MERGE and SPLIT statements introduce a new boundary point. SWITCH  moves partitions between tables.
• The $PARTITION function limits a query to a specific partition.

 

Implement data compression- sparse columns; page/row

http://msdn.microsoft.com/en-us/library/cc280449.aspx

 

Maintain indexes- create spatial indexes; create partitioned indexes; clustered and non-clustered indexes; XML indexes; disable and enable indexes; filtered index on sparse columns; indexes with included columns; rebuilding/reorganizing indexes; online/offline

http://msdn.microsoft.com/en-us/library/ms188783.aspx

• There are three types of index pages: root, intermediate and leaf.
• Indexes can have 16 columns.
• INCLUDE columns are part of the index at the leaf level only.
• Filtered indexes simply indicate a WHERE clause in the index.
• FILLFACTOR specifies the space to be left, and applies only to the leaf level.
• If you build an index offline, you lock the table until it is complete.
• A primary XML index is built on all nodes.
• In a spatial Index, a query that is covered in a cell is not tessellated.
• There are three types of XML Indexes: Path, Value and Property
• When you rebuild clustered indexes, only the clustered index is rebuilt, unless you set one of the ALL parameters, in which case the non-clustered indexes may be rebuilt.
• A rebuild operation takes lots of locks unless you specify otherwise, reorganize operations do not, since they only defragment the leaf level.
• If a clustered index is disabled, the entire table is disabled.
• When you re-enable an index it must be rebuilt.

 

Manage collations- column collation; database collation; instance collation

http://msdn.microsoft.com/en-us/library/ms143503.aspx

 

Identify SQL Server service problems- DB Engine service; SQL Agent service; SQL Browser service

http://msdn.microsoft.com/en-us/library/ms143504.aspx

• The first thing to do to recover space is to back up the transaction log.

 

Identify concurrency problems- blocks, locks, deadlocks, activity monitor; relevant Dynamic Management Views
 http://msdn.microsoft.com/en-us/library/ms190615.aspx

• You can only correlate a SQL Trace and Profiler data if you capture the "start time" column.
• Modes of locks are: Shared, Exclusive and Update.
• Types of locks are: Row, Page, Table and Database.
• Lock Scopes are: Session, Transaction, and Cursor.
• Locks and blocks are shown in sys.dm_exec_requests.
• A blocked process has a non-zero value in the blocking_session_id column.
• Deadlocks cause a 1205 error message.

 

Identify SQL Agent job execution problems- proxy accounts; credentials; job history

http://msdn.microsoft.com/en-us/library/ms189064.aspx

 

Locate error information- error log; agent log; job execution history; event logs

http://msdn.microsoft.com/en-us/library/ms191202(sql.90).aspx

• You can read an event log using the sys.xp_readerrorlog system table.
• If tempdb runs out of space it affects every database on the instance.
• Space error are 9002 (log) and 1101 or 1105 for the database.
• If the database starts up and shuts down, check permissions.
• Device activation errors often have to do with data or log file devices. Tempdb errors of this type cause the entire Instance to shut down while other databases will simply go offline.
• To rebuild the master database use the Installation Center.
• Startup errors can be in master, tempdb and mssqlsystemresource databases.

 

Implement Resource Governor

http://msdn.microsoft.com/en-us/library/bb933866.aspx

 

Use the Database Engine Tuning Advisor

http://msdn.microsoft.com/en-us/library/ms188639.aspx

 

Collect trace data by using SQL Server Profiler

http://msdn.microsoft.com/en-us/library/ms187929.aspx

 

Collect performance data by using Dynamic Management Views (DMVs)

http://msdn.microsoft.com/en-us/library/ms188754.aspx

• DMV Categories:
• dm.db* - General database and index info
• dm.exec* - Query info
• dm.io* - Disk info
• dm.os* - Hardware info

• To show the last time an index was used: sys.dm_db_index_usage_stats.

 

Collect performance data by using System Monitor

http://msdn.microsoft.com/en-us/library/ms191246.aspx

 

Use Performance Studio

http://msdn.microsoft.com/en-us/library/bb677179.aspx

 

Implement database mirroring- monitoring, configuring, failover

http://msdn.microsoft.com/en-us/library/ms189852.aspx

• Database Mirroring and Service Broker can use NTLM, Kerberos or negotiation.
• Database Mirroring requires a TCP endpoint - you can specify a port.
• Filestream is not compatible with database mirroring.
• Three servers possible: Principal, Mirror, Witness.
• DBM Modes are: High availability, High performance, and High safety.
• High availability produces automatic failover and synchronous data transfer , and requires a witness.
• High performance provides manual failover and asynchronous data transfer and does not require a witness.
• High safety provides synchronous data transfer, but does not need a witness.
• The new version of MDAC supports Transparent Client Redirect.

 

Implement a SQL Server clustered instance- monitoring, configuring, failover

http://msdn.microsoft.com/en-us/library/ms189134.aspx

• Majority node clusters are for three or more nodes.
• To support encryption of the filesystem in a cluster, you must enable kerberos and have trusted service accounts.
• Clustered drives may not be dynamic or use mount points.
• In Windows 2003 Server, you should cluster MSDTC.
• Service accounts in a cluster must belong to the domain.
• The two health checks in a cluster are ping and Name selects.
• The SQL Server services in a cluster must be set to manual start.

 

Implement log shipping- monitoring, configuring, failover

http://msdn.microsoft.com/en-us/library/bb895393.aspx

• SSIS has a Transfer Logins task.
• Can be used to create a reporting server in Standby mode.
• Jobs:
• Backup - on the primary
• Copy - on either system
• Restore - on the backup

• Many objects are not transferred , such as the replication data.

 

Implement replication- monitoring, configuring, failover

http://msdn.microsoft.com/en-us/library/ms151198.aspx

• You need a domain or local user account for the SQL Server Services to use replication.
• Articles are tables, views, stored procedures or functions.
• You can filter by rows or columns in an article.
• Filter types are: Static, Dynamic (Merge), and Join (Merge).
• The distributor runs the Agent.
• Peer to Peer replication is available only in Enterprise Edition.

 

Uncategorized:

• Tables -
• Numeric and decimal types are the same.
• Precision is total, scale is the numbers to the right.
• Substituting MAX for the volume of the character type or VARBINARY gives you 2GB of space.
• The XML datatype is limited to 2GB (not sure about this one, the author might be wrong)
• Collation can be specified at the Instance, database and column levels.
• Database Mirroring does not work with Filestream data or a Database Snapshot.
• Sparse columns must allow NULLs, cannot be compressed, and is designed for tables with many NULLs.
• Computed columns can be persisted.
• You can define up to 30,000 columns (need to check this) but the data still cannot exceed 8096 bytes per row.
• A column-level constraint cannot reference other columns.
• A table-level constraint can only reference objects in that table.
• Parent tables require a Primary Key for a Foreign Key to reference.