Database Administrator Certificate Capstone Project Evaluation Checklist

(1)

Database Administrator Certificate Capstone

Project Evaluation Checklist

The following checklist will be used by the Capstone Project instructor to evaluate your project. Satisfactory completion of the Database Administrator Capstone Project is required to receive the Database Administrator Certificate.

Project Objectives

• Demonstrate the understanding of Relational database design and the fundamental skills required to build a database schema.

• Install and configure both a default and named instance of SQL Server 2008 using different levels of Authentication.

• Present a plan for multiple levels of security schemes as it pertains to Server, Database and object level permissions.

• Implement a database recovery model and demonstrate how it would be effective in a "real-world" scenario.

• Demonstrate a plan for monitoring the health of both server and databases.

• Demonstrate performance tuning techniques using both T-SQL and SQL Server Management Studio (SSMS).

• Create a High Availability solution using Replication.

Documentation Requirements

Install and Configure SQL Server 2008 Developer Edition.

P NI Requirement

Install and configure SQL Server 2008 Developer Edition.

Create an NT local account that will act as the service account for SQL Server and SQL Agent services using PowerShell.

Install a Default Instance Install a Named Instance

Create two directories where all of your database files will reside, with the following path <Drive Letter>:\DB\MSSQL\Data and <Drive

Letter>:\DB\MSSQL\Log or both log and data folder can reside under the same DB directory if only one

(2)

drive is available. Create these directories using PowerShell

Use best practices for the SA account on SQL Server. Ensure that cmd-shell is enabled.

Install PowerShell if not using Windows 7 Operating System.

Stop and Start SQL Server services for your default instance using PowerShell

P= Pass NI=Needs Improvement Comments:

Relational Database Design.

Create a Database Entity Relationship Diagram (Can be hand written and scanned electronically, or if you have access to Visio, can also be a Visio diagram.) Make sure to indicate PK and FK constraints.

Create a SQL build script for the database, with sample data. Minimum three records per table to showcase relationships between the tables and how the data is organized within the database. Database MDF and LDF file placement to adhere to file structure indicated in the “Installation and Configuration Requirements” portion of this document. Name the database whatever you like and remember to follow best practices when naming both database and the object within your database.

Build this database on your “Named Instance” of SQL Server.

Create a backup file .BAK of the database sent to the instructor via e-mail, once it has been created on SQL Server.

Create a Database Entity Relationship Diagram (Can be hand written and scanned electronically, or if you have access to Visio, can also be a Visio diagram.) Make sure to indicate PK and FK constraints.

(3)

Create a SQL build script for the database, with sample data. Minimum three records per table to showcase relationships between the tables and how the data is organized within the database. Database MDF and LDF file placement to adhere to file structure indicated in the “Installation and Configuration Requirements” portion of this document. Name the database whatever you like and remember to follow best practices when naming both database and the object within your database.

Build this database on your “Named Instance” of SQL Server.

Create a backup file .BAK of the database sent to the instructor via e-mail, once it has been created on SQL Server.

P= Pass NI=Needs Improvement Comments:

Create Project Databases.

Using the scripts provided by the instructor, create the databases on the default instance of your SQL Server installation.

Add a secondary data file (.ndf) to one of your databases using T-SQL.

Create a Security Model/Data access method for each Server instance/database(s).

Create users for each database using both Windows

Authentication and SQL Server Standard logins. At least one of each type in each of the three databases.

Create Linked Server connections between the two SQL Server

instances.

Write at least two T-SQL queries that utilize each of the

LinkedServers.

Create at least one Database Role in each database. Use T-SQL to create these and save the scripts as such. Add at least one user to this role.

(4)

Document each login’s permissions for each type of user i.e. at a server level, database level or object level. Produce the T-SQL equivalent of each operation.

For at least one user limit the column access it has on a

particular table.

Create at least one view and assign this view to a database role.

Create a new user Schema in one of your project databases. Create a few tables in this schema that have the same name in the dbo schema.

Assign or create a new SQL Server Standard login and set this logins default schema to the newly created schema.

Connect to this new schema as new user and run a few select statements. Note the behavior, and any specific changes, if any, to your select statements.

For one of your Windows authenticated accounts, give it the ability to run traces. Grant this permission using T-SQL and save the script.

Backup and Recovery.

Implement a backup and recovery strategy based on fictional business requirements; however you must have at least one database whose database is set to “Full” recovery mode. Provide documentation describing the plan in detail to the instructor.

Demonstrate the loss of data, and restoring from backups. Using SSMS as well as T-SQL scripts.

Demonstrate a recovery scenario when the database is set to Bulk-Logged recovery mode. Using SSMS as well as T-SQL scripts.

Demonstrate restoring from a File or Filegroup backup. Using SSMS as well as T-SQL scripts.

Using T-SQL, restore one of the databases from the default instance and restore it to the named instance as a different name of your choosing.

(5)

Monitoring Database Server Health and Troubleshooting.

Demonstrate the use of Performance Monitor to monitor server activity.

Using the Dedicated Administrator Connection utility demonstrate the kinds of activities a DBA could do from this console.

Create and save a Profiler template that is designed to troubleshoot queries that have a duration time of over 5 minutes to complete.

Create a Maintenance plan that runs nightly on your default instance of SQL Server.

Describe in writing what the output represents and why and how DBCC SHOWCONTIG is used.

Demonstrate the use of Dynamic Management Views. Pick one of your choosing, and demonstrate its usage and where it might be applicable in day to day administration. (In class Presentation)

Replication.

Configure the default instance as both the Publisher and Distributor.

Named instance will be the subscriber using a pushed publication.

Replicate all tables and stored procedures to a newly created database.

Use Transactional Replication. Demonstrate its effectiveness by inserting data on the publishing database and ensure that it has replicated to the subscriber. (In class Presentation)