HOMEWORK
CAP613T: DATABASE ADMINISTRATION

Q1. Profiles and accounts are managed in SQL Server 2008 using various stored procedures. Create profile and its accounts and manage them using those stored procedures and give the screen shots with results.
Ans.
sysmail_add_profile_sp
[pic]

sysmail_add_account_sp
[pic]

sysmail_add_profileaccount_sp
[pic]

sysmail_update_profile_sp
[pic]

sysmail_update_account_sp
[pic]

sysmail_update_profileaccount_sp
[pic]

Q2. Create Event Notification for updation and deletion of table.
Ans. Creating Event Notification for updation:

CREATE EVENT NOTIFICATION NotifyUpdateEvents
ON DATABASE
FOR UPDATE_TABLE
TO SERVICE '//AdventureWorks/EventNotificationService' , 'current database'
GO

Creating Event Notification for deletion:

CREATE EVENT NOTIFICATION NotifyDeleteEvents
ON DATABASE
FOR DELETE_TABLE
TO SERVICE '//AdventureWorks/EventNotificationService' , 'current database'
GO

Q3 “A common configuration is to use the same account for both SQL Server Service and SQL Server Agent Service” comment. Are there some extra conditions or rights in windows operating system for the accounts used by SQL Server Agent, if yes. Discuss them?
Ans.
• Adjust memory quotas for a process: This privilege determines who can change the maximum memory that can be consumed by a process. This privilege is useful for system tuning, but it can be misused, for example, in a denial-of-service attack.

• Act as part of the operating system: This user right allows a process to impersonate any user without authentication. The process can therefore gain access to the same local resources as that user. Processes that require this privilege should use the LocalSystem account, which already includes this privilege, rather than using a separate user account with this privilege specially assigned.

• Bypass traverse checking: This user right determines which users can traverse directory trees even though the user may not have permissions on the traversed directory. This privilege does not allow the user to list the contents of a directory, only to traverse directories. • Log on as a batch job: This security setting allows a user to be logged on by means of a batch-queue facility. For example, when a user submits a job by means of the task scheduler, the task scheduler logs that user on as a batch user rather than as an interactive user. • Log on as a service: This security setting determines which service accounts can register a process as a service. • Replace a process-level token: This security setting determines which user accounts can call the CreateProcessAsUser()application programming interface (API) so that one service can start another. An example of a process that uses this user right is Task Scheduler.

Q4. Being DBA, Design the sequence of backups which can be performed over a database so that its backup size can be easily managed. Give variation of sizes using various examples.
Ans. SQL Server performs the following actions: • Logs the BACKUP statement in the transaction log. • Issues a Checkpoint causing all outstanding dirty buffer pages to be written to the disk. • Writes all data pages specified by the FULL, DIFFERENTIAL, FILE, or FILEGROUP backup options to the backup media. • Writes all data modifications recorded in the transaction log that occurred during the backup to the backup media. • Logs the completion of the backup in the transaction log.

SQL Server 2008 Backup Types • Full Backup • Differential Backup • File/Filegroup Backup • File/Filegroup with Differential • Transaction Log Backup • Partial Backup • Copy Only Backup

Full Backup: The Full backup simply backs up all the data in the database and records all database file locations.

BACKUP DATABASE SmallWorks
TO DISK = ‘D:\SQLBackups\SmallWorksFull.BAK’
WITH DESCRIPTION = ‘SmallWorks FULL Backup’

Differential Backup: Differential backups are used to back up only the data that has changed since the last Full backup.

BACKUP DATABASE SmallWorks
TO DISK = ‘D:\SQLBackups\SmallWorksDiff.BAK’
WITH DIFFERENTIAL, DESCRIPTION = ‘SmallWorks Differential Backup’

File/Filegroup Backup: When a database is divided across many files and filegroups, these files and filegroups can be backed up individually. This type of backup is particularly useful for very large databases.

BACKUP DATABASE SmallWorks
FILEGROUP = ‘SWUserData1’
TO DISK = ‘D:\SQLBackups\SmallWorksUserData1FG.BAK’
WITH DESCRIPTION = ‘SmallWorks SWUserData1 Filegroup Backup’

File/Filegroup with Differential: An additional option available when backing up files or filegroups is the ability to perform a Differential File or Filegroup backup.

BACKUP DATABASE SmallWorks
FILEGROUP = ‘SWUserData1’
TO DISK = ‘D:\SQLBackups\SmallWorksUserData1FGDIFF.BAK’
WITH DIFFERENTIAL, DESCRIPTION = ‘SmallWorks Filegroup Differential Backup’

Transaction Log Backup: In Full or Bulk-Logged recovery models, it is imperative that periodic Transaction Log backups are completed to both maintain the size of the transaction log within reasonable limits and to allow for the recovery of data with the least amount of data loss. Transaction Log backups come in three forms: • Pure Log backups • Bulk Log backups • Tail Log backups.

--Pure or Bulk Log Backup of SmallWorks
BACKUP LOG SmallWorks
TO DISK = ‘D:\SQLBackups\SmallWorksLog.TRN’
WITH DESCRIPTION = ‘SmallWorks Log Backup’

--Tail Log Backup of SmallWorks
BACKUP LOG SmallWorks
TO DISK = ‘D:\SQLBackups\SmallWorksTailLog.TRN’
WITH NO_TRUNCATE, DESCRIPTION = ‘SmallWorks Tail Log Backup’

Partial Backup: A Partial database backup consists of the Primary filegroup, Read Write filegroups, and any Read Only filegroup specified.

BACKUP DATABASE SmallWorks READ_WRITE_FILEGROUPS
TO DISK = ‘D:\SQLBackups\SmallWorksPartial.BAK’
WITH DESCRIPTION = ‘Partial Backup of all Read/Write filegroups’

Copy Only Backup: Copy Only backups can be performed on database files and transaction logs to create a backup without affecting the chain of backups required to restore a database.

BACKUP DATABASE SmallWorks
TO DISK = ‘D:\SQLData\SmallWorksCopyOnly.BAK’
WITH COPY_ONLY, DESCRIPTION = ‘Copy only backup’

****************