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


In a previous post, I started the process of my study for the 70-432 exam. I’m working on that (as I have time) and I promised to post my notes here. My notes aren’t totally inclusive, they are just things I found interesting as I was doing my reading.


As I’ve been using a few new resources, I think they may have some bad info that I’ll correct as I go. If I don’t know if something is accurate, I’ll mention that until I verify it. As always, these are just my thoughts, so this list certainly isn’t inclusive:


 


Resources:


http://infoweb2007/sites/learning/EmployeeResources/Internal%20Test%20Centers/Pages/default.aspx#find


http://learning.microsoft.com/Manager/default.aspx


I’ve added this book, since it has practice tests as well: http://www.amazon.com/MCTS-Self-Paced-Training-70-432-PRO-Certification/dp/0735626057/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1238461542&sr=8-1


 


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 Acounts

  • Use the Configuration Manager to change the service accounts – it does things other than just set the account

  • 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


 


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


 


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


 


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.

 


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


 


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


 


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


 


Manage schema permissions and object permissions- manage schema ownership


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


 


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.

 


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 fiel algorythm 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


 


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


 


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


 


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.

 


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


 


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


 


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


 


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


 


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


 


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


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


 


Implement log shipping- monitoring, configuring, failover


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


 


Implement replication- monitoring, configuring, failover


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


 


Uncategorized:



  • Tables –

  • Numeric and decinal 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.

Skip to main content