Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
82 Cards in this Set
- Front
- Back
|
Configuring SQL Server Agent Account - groups/roles
|
- The service account should not be a member of the local administrators group.
- Account must be a member of the sysadmin role. The account is automatically assigned this during install, but if changed later you have to manually add it. - Must add account to TargetServersRole in msdb on the master database to support multi-server job processing |
|
|
SQL Server Agent Service Account, Permissions for domain based account
|
- The logon service as a right, assigned via Group Policy
- Membership of the Pre-Windows 2000 compatible access security group at the domain level. Jobs will fail for any jobs owned by domain users who are not members of the local admin group. |
|
|
Supporting SQL Server Agent proxies - Permissions
|
you must assign the SQL Server Agent service account additional permissions. Otherwise only member of the sysadmin server role will be able to create jobs.
|
|
|
Assign permissions via Group Policy Object
|
Computer Configuration\Policies\Windows Settings\Security Settings\Local Policies\User Rights Assignment node
1. Bypass Traverse Checking 2. Replace a Process-Level Token 3. Adjust Memory Quotas For a Process 4. Log On as a Batch Job. |
|
|
SQLAgentUserRole
|
This is the least privileged SQL Server Agent role. Role members have permissions only on the local jobs and job schedules they own. This role does not allow use of multi-server jobs.
|
|
|
SQLAgentReaderRole
|
- Has all permissions assigned to SQLAgentUserRole.
- Can view the properties and history of all available jobs and job schedules, including multi-server jobs. |
|
|
SQLAgentOperatorRole
|
- Has all the permissions assigned to the SQLAgentReaderRole
- Can execute, stop, or start all local jobs and delete job history for any local job - Can also enable and disable all local jobs and schedules |
|
|
sp_add_job
|
Enables you to create jobs
|
|
|
sp_add_jobstep
|
Adds a job step to an existing job
|
|
|
sp_add_schedule
|
Creates a schedule that can be used by any job
|
|
|
sp_attach_schedule
|
Attaches an existing schedule to an existing job
|
|
|
sp_add_jobserver
|
Adds a job to a server
|
|
|
Monitoring Multi-Server Environments - Server Setup
|
- Use SQL Server Agent to manage jobs across multiple servers.
- Multi-server environments use master servers and target servers. Target servers report to master servers, and a target server can report to only a single master server. |
|
|
sp_msx_enlist
|
to enlist the target server in a master server
|
|
|
sp_msx_defect
|
to defect target server from multi-server
|
|
|
Full database backups
|
A full database backup includes all database objects, system tables, and data
|
|
|
Differential backups
|
A differential backup backs up data that has altered since the last full backup.
|
|
|
Transaction log backups
|
A transaction log backup records the changes that have occurred since the previous transaction log backup and then truncates the transaction log. Transaction log backups function incrementally rather than differentially.
|
|
|
File and filegroup backups
|
- back up individual database files and filegroups rather than performing a full database backup.
- Backs up very large databases. - Must back up the transaction log when performing a file and filegroup backup - Cannot use this method if the Truncate Log On Checkpoint option is enabled. |
|
|
Copy-only backups
|
- Copy-only backups are functionally the same as full database or transaction log backups but do not affect the backup sequence.
- Cannot be used as the basis for a differential backup or transaction log backup. |
|
|
Incremental backups
|
back up data that changed since the last full or incremental backup.
|
|
|
Differential backups
|
back up data that changed since the last full backup, even if there has been a subsequent differential backup.
|
|
|
Backup restrictions
|
- Cannot backup offline data
- File management operations such as using ALTER DATABASE with ADD FILE or REMOVE FILE initiated during the backup only execute after the backup completes |
|
|
Using Backup Compression
|
Reduces the amount of space required to store a backup at the cost of increased CPU load during the compression process.
|
|
|
T-SQL – to enable backup compression on an instance
|
EXEC sys.sp_configure [backup compression default], 1
GO RECONFIGURE WITH OVERRIDE GO |
|
|
T-SQL Changing the recovery model
|
ALTER DATABASE [AdventureWorks2012] SET RECOVERY FULL;
|
|
|
Simple Recovery Model
|
- No transaction log backups are taken
- Recover to the most recent database backup only - Does not allow point-in-time recovery. |
|
|
Full Recovery Model
|
- recovery requires you to take transaction log backups and full backups
- Enables point-in-time recovery. |
|
|
Bulk-logged Recovery Model
|
- Minimizes transaction log activity during bulk operations
- Cannot perform point-in-time recovery |
|
|
What is the recovery model for system databases? |
Simple |
|
|
Master |
This database hosts system-level information. Back up this database regularly.
|
|
|
Model
|
You need to back up the template database only after you have made modifications. Because changes to the template database are usually infrequent, this database does not need to be backed up regularly. |
|
|
Msdb
|
This database hosts scheduling data for alerts and jobs and hosts operator information. It also stores the backup and restore history tables. You should back up this database whenever you make changes to jobs and alerts.
|
|
|
Tempdb
|
This database is re-created each time the Database Engine starts. You can’t back up the tempdb database.
|
|
|
Resource (RDB)
|
- Read-only database holds copies of all system objects. |
|
|
Configure Distribution instance in replication
|
- Present only when the instance holds the distributor role in a replication topology
- Stores history information and metadata related to replication and transactions for transactional replication - Should be backed up with the same frequency as the publication and subscription databases. |
|
|
Backing Up Mirrored Databases
|
- You only backup the principal database.
- You cannot use BACKUP LOG WITH NORECOVERY option |
|
|
Backing Up AlwaysOn Replicas - Prefer Secondary
|
This is the default. Backups should occur only on a secondary replica, which allows backups to be taken on the primary replica if that is the only replica online.
|
|
|
T-SQL - Change the backup preference by using ALTER AVAILABILITY GROUP
|
ALTER AVAILABILITY GROUP [AG_ALPHA] SET ( AUTOMATED_BACKUP_PREFERENCE = SECONDARY_ONLY);
|
|
|
Using Database Checkpoints
|
- A checkpoint writes all modified pages held in memory to disk and then records that information in the transaction log. |
|
|
T-SQL - Add a backup device
|
EXEC sp_addumpdevice 'disk', 'Alpha-Backup', 'c:\backup\alpha-backup.bak'
|
|
|
Backing Up Media Sets
|
- A media set can include a single file, multiple files, a tape, or multiple tapes. |
|
|
T-SQL Full Backups
|
BACKUP DATABASE [AdventureWorks2012] to [Alpha-Backup];
|
|
|
T-SQL - Differential Backup
|
BACKUP DATABASE [AdventureWorks2012] to [Alpha-Backup] WITH DIFFERENTIAL;
|
|
|
T-SQL - Transaction Log Backup
|
BACKUP LOG [AdventureWorks2012] to [Alpha-Backup];
|
|
|
T-SQL Determine whether a backup was successful
|
SELECT database_name, backup_finish_date, backup_size, type FROM msdb.dbo.backupset;
|
|
|
Restoring backup full backups only |
restore the database by using the last full backup
|
|
|
Restoring backups - Full and differential backups |
Restore the database to the point in time when the last differential backup was taken |
|
|
Restoring backup full and transaction log backups
|
1. If possible, you first backup the current transaction log with the NO_TRUNCATE option.
2. Recover the most recent full backup using NORECOVERY. 3. Restore each transaction log backup, in order of datetime taken, with NORECOVERY until the most recent transaction log, which you restore using the RECOVERY option. |
|
|
Restoring backup Full, differential, and transaction log backups
|
1. If possible, you first backup the current transaction log using NO_TRUNCATE |
|
|
Restore Limitations
|
- You can recover databases backed up on SQL Server 2000, 2005, 2008, and 2008 R2 to 2012
- You cannot restore databases backed up by using SQL Server 2012 on previous versions of SQL Server. - You cannot restore system database backed up on previous versions of SQL Server on SQL Server 2012 |
|
|
RESTORE WITH RECOVERY
|
returns the database to normal operation
|
|
|
RESTORE WITH NORECOVERY
|
- recovered but cannot be accessed
- Used when placing a database on a mirror in a mirroring set, or when you need to recover additional transaction log or differential backups |
|
|
RESTORE WITH STANDBY
|
- recovered by left in read-only mode
- Additional transaction logs can be applied, but it is possible for users with appropriate permissions to query the database to verify the integrity of the data. |
|
|
How must a database be configured to use File Restores?
|
Can be used if a database contains multiple files or filegroups and uses the full or bulk-logged recovery model
|
|
|
When should you perform a file restore?
|
If the volume that hosts a particular file fails or a file becomes corrupt, but the other files used by the database do not have any problems.
|
|
|
Offline file restores
|
- Performed when the database is taken online
- You must take a tail-log backup prior to performing |
|
|
Online file restores
|
- Enable you to restore files in secondary filegroups as long as the primary filegroup is online.
- You must take a log backup after performing the file restore because this allows the recovered file to be brought into a state consistent with the rest of the database. |
|
|
Performing Page Restores
|
- Can repair a small number of damaged pages in an otherwise healthy database
- Can be quicker than performing other types of restores - Note - If a database file constantly suffers from damaged pages, it is likely that the volume that hosts the file is faulty. |
|
|
Conditions for Page Restores
|
- Database must use full or bulk logged recovery model.
- You can perform page restores only on read/write filegroups. - You can restore only database pages. You cannot restore pages in the transaction log, the full-text catalog, the database boot page, or the file boot page. - 2012 Enterprise edition supports online page restore. All editions of 2012 support offline page restore. |
|
|
T-SQL Restoring pages
|
1. RESTORE DATABASE HOVERCRAFT PAGE='2:42, 2:81, 2:1023' FROM beta_file_backup WITH NORECOVERY;
2. Restore any transaction logs taken after the file backup by using the NORECOVERY option. 3. Perform a new log backup. 4. Restore the newly created log backup by using the RECOVERY option. |
|
|
AlwaysOn Availability Groups and database mirrors - page repairs and errors
|
- Support automatic page repair
- If an error occurs on the primary database, the primary broadcasts a request to all secondaries and retrieves the page from the first secondary to respond. |
|
|
Restoring a Database Protected with Transparent Data Encryption
|
- Must have access to the certificate and private keys.
- Include the certificate used to create the Database encryption key and the database encryption key in your backup scheme. - Prior to performing a restore, you must re-create the certificate using the backup of the original. |
|
|
Restoring System Databases - Model and msdb databases
|
- Do not use the full recovery model by default
- You do not need to worry about restoring transaction log backups with these databases |
|
|
T-SQL - Restore the master database from backup
|
RESTORE DATABASE master from <backup device> WITH REPLACE
|
|
|
Repairing system databases - Command Line |
if the database engine is unable to start, |
|
|
Restoring Replicated Databases and System Databases |
You must also restore the master and msdb database on the instances
|
|
|
T-SQL – check status
|
SELECT databasepropertyex ('databasename', 'Status');
|
|
|
Output of databasepropertyex ('database', 'Status') |
- ONLINE - The database is operating normally. |
|
|
T-SQL – check which users can access a database
|
SELECT databasepropertyex ('databasename', 'UserAccess');
|
|
|
Output of SELECT databasepropertyex ('databasename', 'UserAccess');
|
- SINGLE_USER - Only a single user with the db_owner, dbcreator, or sysadmin roles can access the database.
- RESTRICTED_USER- Only members of the db_owner, dbcreator, and sysadmin roles can access the database. - MULTI_USER- The database is accessible to multiple users. |
|
|
T-SQL – check whether the database is in read/write or read-only
|
SELECT databasepropertyex ('databasename', 'Updateability');
|
|
|
Monitoring Multi-Server Environments - Choosing a service account
|
- Use a domain-based account when choosing the SQL Server Agent service account
- Use local accounts only if all instances are hosted on the same computer |
|
|
Using Backup Compression - Calculating the compression ratio
|
Query the backup set history table, using backup_size and compressed_backup_size
|
|
|
Which system databases should you backup at the publisher when using replication? |
Master, msdb, and publication |
|
|
Which system databases should you backup at the distributor when using replication? |
Master, msdb, and distribution databases |
|
|
Which system databases should you backup at the subscriber when using replication? |
Master, msdb, and subscription databases
|
|
|
System database backups with snapshot and transactional replication
|
You should set the sync with backup option on the publication and distribution databases.
|
|
|
Backing Up AlwaysOn Replicas - Secondary Only
|
All automatic backups occur on secondary replicas. Backups should not be taken if the primary replica is the only replica online.
|
|
|
Backing Up AlwaysOn Replicas - Primary
|
Backups should always be taken on the primary replica. This allows differential backups to be taken.
|
|
|
Backing Up AlwaysOn Replicas - Any Replica
|
Backups can occur on any replica in the availability group. Automated backups use the backup priority setting applied in the preference when choosing a replica as the target for a backup.
|
|
|
AlwaysOn Availability Groups and database mirrors -Status of Automatic page repairs
|
1. AlwaysOn Availability Groups - Query the sys.dm_hadr_auto_page_repair dynamic management view.
2. Database mirroring - Query the sys.dm_db_mirroring_auto_page_repair dynamic management view. |
