SQL DBA Questions

1)What is table partitioning?
2)log shipping related jobs
3)merge replication conflict primary key how to solve?
4)what is db mirroring end points?
5)how to upgrade two server in cluster?
6)during installation could we get missing msi file error in sql
7)can we issue dbcc freeproccache what would happen?
8)what is checkpoint in SQL Server?
9)what is CXPACKET how do we identify in sql
10)fragmentation how to we see through dmvs
11)if timeout occurs in site what would we do to check in SQL?
12)scenario
   backup happens sat
   diffrential happens every 10pm
   tlog happens every 1 hour all day
   disaster occurs thu 9pm

How to receover to thu 8pm?

13)what is happening when you issue CheckDB Command?

14)why truncate_truncate command removed from future versions?

15)what is tail log backup command and T-SQL Statement in SQL?

16)why in place upgrades have drawbacks?

17) MAX DOP OPTION With example

18) backup with compression also asks page/db compression

19) SQL Profiler not suggest to run in live db so what methods

used to get the output

20) what is use of profiler?

None.The transaction log is automatically truncated when the database is using the simple recovery model. If you need to remove the log backup chain from a database, switch to the simple recovery model.

21) index rebuild and index reorganize?

22) how to solve orphan users in db migration

23) windows login account and mixed mode account

24) Can we able to shrink tempdb? if again not able shrinked space in tempdb what to do in next step?

solution 1:
shrink tempdb file like tempdev,templog

solution 2:
add mdf file in tempdb to allocate space/remove space

solution 3:
move tempdb file to relocate to new drive file using alter database  dbname modify file option

solution 4:
restart services


25) what is lazzy writer?

26) diffrence between lazzy writer and buffer cache

27) How to verify/Validate Backup files in SQL Server? that is how to check backup file should restore without any issue?

28)what are minimum pre requests for intallation on SQL Server?
how many memory need to install SQL Server?
hardware requirements for SQL Server installation

Minimum 512 MB or 1 GB RAM Memory
               2 GB hard drive
               .Net Framework 2.0 or 3.5 SP1 or more



29)what is sid in SQL Server?

30) What Trace Flags do we need to Turn on for Capture Deadlock in SQL Server?

31) Difference Between Database Mirroring and Log Shipping

32) Can we Set Up More Than one Database Between Principal and Mirroring in Database Mirroring?

33) Is Database Mirroring Database Level or Server Level?

34) Steps for Performance Tuning in SQL Server?

Trace Flag 1204 and Trace Flag 1222

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.
  
DBCC TRACEON (1204, 1222)

How to turn off logging of all successful backups in your SQL Server error logs?
Using trace flag 3226.

DBCC TRACEON (3226,-1)

T-SQL script to take a database backup.

BACKUP DATABASE AdventureWorks2012 TO DISK='e:\advwork.bak'

Backup to multiple files using T-SQL.

BACKUP DATABASE AdventureWorks2012 TO DISK='e:\advwork.bak' ,DISK='d:\advwork.bak' ,DISK='f:\advwork.bak'

T-SQL script to restore a database.

RESTORE DATABASE AdventureWorks2012 FROM DISK='e:\advwork.bak'

T-SQL script to set RECOVERY FULL model.

ALTER DATABASE dbrnd_DB SET RECOVERY FULL;

T-SQL script to take Database Log backup.

BACKUP LOG dbrnd_DB TO DISK = 'd:\dbrnd\dbrnd_tran01.trn'  WITH retaindays = 3

T-SQL script to restore a multiple transaction log backups.

1 2 3 4 5 6 7 8 9 1 1 1 2 2 2 2

RESTORE DATABASE AdventureWorks2012 FROM AdventureWorks2012_1 WITH NORECOVERY; GO RESTORE LOG AdventureWorks2012 FROM AdventureWorks2012_log WITH FILE = 1, NORECOVERY; GO RESTORE LOG AdventureWorks2012 FROM AdventureWorks2012_log WITH FILE = 2, WITH NORECOVERY; GO RESTORE LOG AdventureWorks2012 FROM AdventureWorks2012_log WITH FILE = 3, WITH NORECOVERY; GO RESTORE DATABASE AdventureWorks2012 WITH RECOVERY; GO

Consider a situation where database backup size is 80 GB and you have three different disks and each has 30 GB free space.
Now the question is, can you split your database backups?
Yes, it is possible. We can split the backup files into different places and the same can be restored.

BACKUP DATABASE AdventureWorks TO DISK = ‘D:\AdventureWorks1.bak’, DISK = ‘E:\AdventureWorks2.bak’, DISK = ‘F:\AdventureWorks3.bak’ RESTORE DATABASE [AdventureWorks] FROM DISK = ’D:\Backup\MultiFile\AdventureWorks1.bak’, DISK = ’E:\AdventureWorks2.bak’, DISK = ’F:\AdventureWorks3.bak’

We have configured every Sunday 11pm FULL backup. Every 11pm differential backups and every 1h.r transaction log backups.
The Database was failed at 11.30 pm on Friday. Then what are the database recovery steps?

• Take Tail log backup to get 11-11.30pm transactions on Friday.
• Restore last Sunday full backup with NO_RECOVERY.
• Restore Friday 11pm differential backup with NO_RECOVERY.
• Restore tail log backup with RECOVERY.

What is File or File Group backup?

Using the file or filegroup backup you can backup an entire filegroup or files within the filegroup. These backups are essential when the database size is so large that backups must be done in parts because it takes too long to backup the entire database.

T-SQL Script to take File Group backup.

BACKUP DATABASE AdventureWorks2012 FILEGROUP='PRIMARY', FILEGROUP ='Secondary' TO DISK ='D:\AdventureWorks2012_FileGroup.bak'

What is Mirrored backup?

Mirrored database backups can be used to create multiple copies of the database backups on different locations.

T-SQL Script to take Mirrored backup.

BACKUP DATABASE AdventureWorks2012 TO DISK = ‘C:\AdventureWorks2012.bak’ MIRROR TO DISK = ‘D:\AdventureWorks2012_mirror.bak’

What is a log chain?

A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery.

Is it possible in any situation when differential backup grows more than the Full backup?

Yes, it is possible in case when you do not take Full backup of the database for months and change in the databases grow more than the size of the Full backup.

Is it mandatory to take a Full backup if we switch the recovery model of a database?

Yes, It is mandatory to take a Full backup of the database after switching the recovery model of the database to initiate the log chain. Otherwise Diff. or Transaction logs will fail.

What is the below error?
Msg 3023, Level 16, State 2, Line 1
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

This error occurs when we try to run a backup, shrink, or alter database command in SQL Server in parallel.

Can you take differential backup of Master Database?

No, differential backup of master database is not allowed.

Can you take backup of a database which is in emergency mode?

We can’t take the database backup if the database is in emergency mode.

SQL Server has native backup. Why pay money for a tool to do it?

SQL Server backups work extremely well, but you’re going to have to do some work to get them set up and even more to get them automated.
A good third party product will make this automation process very simple.
For example, some tools more efficient at compressing backups, saving even more disk space and time during your backup processes.

Why we require compressed backup?

Simple, Large amounts of data take a long time to write to disk and require almost same space of the database.

For example, when our database backup file is 50GB, database allocated size is also 50GB.
When the database data file has lots of free space, backup size is smaller than actual occupied database size.
The slowest thing in the backup process is usually writing the backup file, whether it’s over the network or to local disk.
SQL Server 2008 introduced a Native Compressed Backup option, in which you can take database backup using WITH COMPRESSION.

T-SQL Script to take a single backup using WITH COMPRESSION:

BACKUP DATABASE AdventureWorks2012 TO  DISK = D:\Backup\AW2012_compress.bak' WITH COMPRESSION

T-SQL Script to enable Database compression for all the Native backups:

1 2 3 4 5 6 7 8 9

USE master; GO EXEC sp_configure 'show advanced option', '1'; RECONFIGURE GO EXEC sp_configure 'backup compression default', '1'; RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'show advanced option', '0'; RECONFIGURE GO  What are the different types of Restore options? Restore an entire database. Restore part of a database. Restore specific files or filegroups. Restore specific pages to a database. Restore a specific transaction log. Basic steps to verify backups regularly. Check SQL Server error log for backup related entries. Check msdb.dbo.backupset table for backup related entries. Check the backup existence in the file system. How to check if your database backups are restorable? You can use RESTORE VERIFYONLY command to validate the backup. Syntax: RESTORE VERIFYONLY FROM DISK = C:\dbrnd.BAK How you can automate the backup process? Backups can be automated by using T-SQL script and you can execute and configure this T-SQL script using SQL Job. You can also configure automated Database Maintenance Plan in which you can configure all different types of backups. What do you know about the online backup? In addition to doing backups to your local disks there are several companies that offer online backups via the internet.  Instead of you have to manage your backup files locally these services allow you to backup your databases via the internet and therefore you have an external copy stored outside of your company’s network. How you consider the recovery model between Full recovery model and Simple recovery model? If you are dealing with huge amount of data and failure chances are also less, you should use the simple recovery model. If data failure risk is very high, you should use the full recovery model. What are the three basic phases for database recovery? Analysis Redo – rolls forward committed transactions Undo – rolls back any incomplete transactions What is a point in time recovery? A point in time recovery is restoring a database to a specified date and time. When you have completed a point in time recovery, your database will be in the state it was at the specific date and time you identified when restoring your database. A point in time recovery is a method to recover your database to any point in time since the last database backup. What do you require for point in time recovery? In order to perform a point in time recovery you will need to have an entire series of backups (complete, differential, and transaction log backups) up to and/or beyond the point in time in which you want to recover. I’ve just deleted a table! I know it is in the log. How do I get it back? If the tables were dropped, ApexSQL Recover can recover them even from databases in the simple recovery model. ApexSQL Recover can recover both table structure and table records. Another approach is,  You should immediately take a log backup of your database and need to perform a process called a point in time recovery. What is a Tail-Log Backups? The tail-log backup captures records on the transaction log that were written since the last transaction log backup. If you’re going to restore a database to the point of failure, then you need to take a tail-log backup before you start the restore operation. In which situation Tail-Log Backups is not required? You do not need a tail-log backup if the recovery point is contained in an earlier log backup. A tail-log backup is unnecessary if you are moving or replacing (overwriting) a database and do not need to restore it to a point of time after its most recent backup. Why Tail-Log Backups have an incomplete Backup Metadata? Tail log backups capture the tail of the log even if the database is offline, damaged, or missing data files. This might cause incomplete metadata from the restore information commands and msdb. However, only the metadata is incomplete; the captured log is complete and usable. Can you take Tail-Log Backup in simple recovery model? No, you can take Tail-Log backup only with full or bulk-logged recovery models. In which scenarios, you require a Tail-Log Backup? If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. If a database is offline and fails to start and you need to restore the database, first back up the tail of the log. If a database is damaged, try to take a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option of the BACKUP statement. What are the issues you faced in backup and restore process? Error 3201 – Cannot open backup device. Error 3205 – Too many backup devices specified for backup or restore. Error 4305 – an earlier transaction log backup is required. Can you access the database during a backup operation? SQL Server backups are an online process; the data stored in SQL Server is highly available during this time. Operations such as INSERT, UPDATE, and DELETE are allowed, as are SELECT statements. However, operations that would modify the underlying table or file space architecture, such as ALTER DATABASE, ADD FILE, or SHRINKFILE, can’t be done while the backup is running. What are your steps to prevent the increasing size of Transaction log file? Backup the log files and make disk spaces free. Moving the log file to another disk. Adding a new log file on a different disk. Shrinking the log file. What are the restore options available? With Recovery: Database is ready to use, and user can connect to database, user can change data inside database. No Recovery: Database is not ready, there are few more backups that have to be applied to this database instance. The User cannot connect to database because it is in Restoring Status. Standby / Read Only: Database is ready to use but database is in Read Only mode, user can connect to database but they cannot change data inside database. What is the difference between Hot and Cold Backup? Performing backup while the database is online is called Hot backup. Stopping SQL server service and copying MDF and LDF files is called cold backup which is not really happens in production. Can I backup databases from multiple instances of MS SQL Server? Yes. You can backup databases from multiple instances of MS SQL Server running on your local computer. However, you may not be able to backup the databases from multiple instances of the MS SQL Server hosted at multiple locations in your network. Can you perform Log shipping and Mirroring using Simple Recovery Model? No, You cannot perform Log shipping and Mirroring with Simple Recovery Model. Can we perform a SQL Server compressed backup? Starting SQL Server 2008, we can perform a compressed backups. All types of backups can be performed with compressed option. When a checkpoint file is updated? Database file creation and zeroing is completed. After each backup set is processed. The redo part of a recovery is finished. If your database size is bigger, why database backup performance is important? It reduces the time that the extra I/O workload (and potentially CPU workload for compressed backups) is present on the system. It reduces the potential for the transaction log file to have to grow to accommodate the transaction log generated while a backup operation is running, especially for long-running full database backups. How you are improving your backup performance? The easiest way to improve backup performance is to allow the backup operation to parallelize, which is known as backup striping. By default, there’s a single data reader thread for each drive letter or mount point being read from and a single data writer thread for each backup device being written to. In general, having more reader and writer threads should improve performance up to the point where the I/O subsystem becomes a bottleneck either for reads or writes. A more advanced way to improve backup performance is to manually specify the number of backup I/O buffers to use (with the BUFFERCOUNT option) and the size of each buffer (with the MAXTRANSFERSIZE option). What do you suggest to improve backup performance? Backup stripping / perform parallel with multiple backup device. Setting of maxtransfersize parameter. Setting of buffercount parameter. Setting of compression parameter. What is Native Backup Compression (SQL Server 2008)? Database backup compression helps in creating a database backup in a compressed format. T-SQL Script to take compressed backup. 1 2 3 BACKUP DATABASE AdventureWorks2012 TO  DISK = D:\Backup\AW2012_compress.bak' WITH COMPRESSION T-SQL Script to calculate compression ratio. 1 2 SELECT backup_size/compressed_backup_size FROM msdb..backupset How compressed backup impact to the performance of the database? By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. What are the restrictions of compressed backup? Compressed and uncompressed backups cannot co-exist in a media set. Previous versions of SQL Server cannot read compressed backups. NTbackups cannot share a tape with compressed SQL Server backups. My backup was failed what may be the possible scenarios? Disk was Full or Server was busy. May be problem with the network. If the domain is not running and SQL Server service is running with domain account. Problem with MSDB and SQL Agent. Error while reading the transaction log file. CHECKSUM errors Database has entered into Suspect and restoring mode. Transaction log file is full. What is RESTORE WITH RESTART option? RESTORE WITH RESTART option, which lets you restart an interrupted restore operation. Periodically, a restore operation writes a checkpoint file that describes the point to which the restore has progressed. This option is really meant for large backups that span several tapes; it lets you restart a restore operation without having to go back to the first tape. However, it can also be useful for restoring disk-based backups. For differential backups, how is the data determined for those backups? DCM page contains information about the extent which are changed after the Full backup. Diff. backup process reads information about the changed extents and those extents are added in the differential backup. What is the meaning of the values in the Type column in backupset table? D – Database I – Differential database L – Log F – File or filegroup G – Differential file P – Partial Q – Differential partial What is Recovery Time Objective (RTO)? Recovery Time Objective (RTO) is the amount of time which data or hardware is desired to be restored after a data corruption or hardware failure. What is Recovery Point Objective (RPO)? Recovery Point Objective (RPO) describes a point in time that data can be restored from. For instance, if there is data corruption, Data loss or unavailability, at what point in time can a valid copy of the data be restored from? RPO is usually measured as minutes, hours, days, weeks etc.

What is a BACKUP WITH FORMAT option?

BACKUP WITH FORMAT option tells SQL Server whether or not to overwrite the media header information. The FORMAT option will erase any information in a backup set that already exists when the backup is initialized.

What is a BACKUP WITH SKIP option?

Using the SKIP parameter will cause SQL Server to skip the expiration check that it normally does on the backup set. It doesn’t care if any backups existing in the backup set have been marked for availability to be overwritten.

What is BACKUP WITH CHECKSUM option?

When generating a backup checksum, BACKUP verifies that the data read from the database is consistent with any checksum or torn-page indication that is present in the database.
When creating a backup checksum, a backup operation does not add any checksums to pages. Pages are backed up as they exist in the database, and the pages are unmodified by backup.

How many copies are allowed when taking a backup using MIRROR Backup option?

Three copies are allowed in a Mirror backup apart from the original copy.

What is MAXTRANSFERSIZE option in Backup database command?

MAXTRANSFERSIZE : specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 64 KB ranging up to 4194304 bytes (4 MB).  The default is 1 MB.

What is BUFFERCOUNT option in Backup database command?

BUFFERCOUNT specifies the total number of I/O buffers to be used for the backup operation.   The total space that will be used by the buffers is determined by: buffercount * maxtransfersize.

What is Windows Azure Blob storage service Database backups?

SQL Server 2012 SP1 CU2, enables SQL Server backup and restore directly to the Windows Azure Blob service. Backup to cloud offers benefits such as availability, limitless geo-replicated off-site storage, and ease of migration of data to and from the cloud.

What are the Benefits with Windows Azure Blob storage service?

Flexible, reliable, and limitless off-site storage: Storing your backups on Windows Azure Blob service can be a convenient, flexible, and easy to access off-site option.
No overhead of hardware management
Cost Benefits: Pay only for the service that is used. Can be cost-effective as an off-site and backup archive option.

What is the Difference between WITH RECOVERY and WITH NORECOVERY parameter?

The RECOVERY command requests the current RESTORE to roll back all uncommitted transactions.  When all uncommitted transactions are rolled back the database is placed into a ready state. The database is ready to use.
NORECOVERY allows the Database Administrator to restore additional backup files such as Differential or Transactional backups. While the database is in this state then users are not able to connect or access this database.

What is a Disaster Recovery?

Disaster recovery is a process that you can use to help recover information systems and data, if a disaster occurs.
Some examples of disasters include a natural or a man-made disaster such as a fire, or a technical disaster such as a two-disk failure in a Redundant Array of Independent Disks (RAID) 5 array.

What are your strategies to recover data when the disaster occur?

• Failover Clustering
• AlwaysOn Avaliability
• Database Mirroring
• Transaction Replication
• Log Shipping
• Strong backup strategies
• Virtual Machine Replication

Before designing a backup strategy, what should be your questions?

• How many hours a day do applications have to access the database?
• How frequently are changes and updates likely to occur?
• Are changes likely to occur in only a small part of the database or in a large part of the database?
• How much disk space will a full database backup require?

Can you encrypt data while creating a backup?

Starting in SQL Server 2014, SQL Server has the ability to encrypt the data while creating a backup. By specifying the encryption algorithm and the encryptor (a Certificate or Asymmetric Key) when creating a backup, you can create an encrypted backup file.

What is Partial Backups?

Partial backups are designed for use under the simple recovery model to improve flexibility for backing up very large databases that contain one or more read-only filegroups.
Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups.

Why can’t I just backup SQL Server’s data files with my Windows backup tool?

SQL Server is not like a word processing application. It manages its own files internally in order to guarantee the ACID (Atomic, Consistent, Isolated, Durable) properties of its databases.
If you simply copy the data file, ignoring the locks and ignoring the transactions that may be currently in progress, it means that when you attempt to attach that database later you will have a database file that is in an inconsistent state. It will generate errors.

It is only a small database. Why can’t I just write every table to disk to back it up?

Yes, you can use a tool like SQLCMD to write the tables to flat file but then, instead of a straightforward, single statement to restore the database, you will need a whole series of commands.

Can someone change the contents of a backup?

There is no direct way to modify the contents of a backup file. Since the backup is a page-by-page copy of the database.
When SQL Server reads each page, during the restore, it will calculate a checksum, based on its contents, and compare it to the value when it read the page during the backup. If someone did manage to change the data within the backup, these values won’t match and SQL Server will flag the page as corrupt.

What is Piecemeal Restores?

Piecemeal restore allows databases that contain multiple filegroups to be restored and recovered in stages. Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one or more secondary filegroups. Piecemeal restore maintains checks to ensure that the database will be consistent in the end.

SQL Server Database Administrator Interview Questions and Answers 

 

How to check server side configuration?

1	SELECT * FROM sys.configurations;

How to check the current location of data and log files for the system databases?

1 2 3

SELECT name, physical_name AS current_file_location FROM sys.master_files WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

What are the types of CHECKSUM?

SQL Server supports three types of checksums: a checksum on pages, a checksum in log blocks, and a backup checksum.

What is a SQL Server Credential?

A SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server.

How to remove all SQL Job related history from the related system tables?

Using this system stored procedure – sp_purge_jobhistory .

What are Database Maintenance Plans?

Database Maintenance Plans allow us to automate many database administration tasks in Microsoft SQL Server. Maintenance plans create a workflow of the tasks required to make sure that our database is optimized, regularly backed up, and free of inconsistencies.

What all options we have in a Database maintenance plans which helps a DBA to keep the database healthy?

• Check Database Integrity
• Shrink Database
• Reorganize Index
• Rebuild Index
• Update Statistics
• Clean Up History
• Execute SQL Server agent job
• All type of Database Backup
• Maintenance clean up task

In which Database all the Maintenance Plans are stored?

MSDB database contains information about all the Database Maintenance plans.

Can we execute TSQL Statement using Database Maintenance Tasks?

Yes, we can run TSQL Statement in a Database Maintenance Plan.

Can we run a SQL Server Agent job using Database Maintenance Tasks?

Yes, we run a SQL Server Agent job in a Database Maintenance Plan.

Is it possible to mark Primary File Group as Read only?

No it’s not possible to make Primary File Group read only.

How to make the File Group read only?

1	ALTER DATABASE AdvantureWorks2012 MODIFY FILEGROUP SecondaryFile Read_Only;

What are the benefits of Read only file groups?

• Can be compressed (using NTFS compression)
• During recovery you don’t need to apply logs to recover a read-only file group
• Protection of data from accidental modifications

What are the important dynamic management views to find different type of fragmentations?

• dm_db_index_operational_stats
• dm_db_index_physical_stats
• dm_db_partition_stats
• dm_db_index_usage_stats

What are the important dynamic management views to find space usage related information?

• dm_db_file_space_usage
• dm_db_task_space_usage
• dm_db_session_space_usage

What is the Query Hints option?

Query hints override the default behavior of the query optimizer for the duration of the query statement.
You can use query hints to specify a locking method on the affected tables, one or more indexes, a query processing operation such as a table scan or index seek, or other options. Query hints are applied to the entire query.

What is BCP? When does it used?

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

Can we add CPU to sql server?

Yes: Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer.

What are the most common ports of SQL Server?

• MSSQL Server: 1433
• HTTP TCP: 80
• HTTPS TCP: 443

What is Singleton Lookup?

A singleton lookup is a seek operation whereby a single record/page of data is retrieved. This can occur, for example when you perform a query that needs to access only a single record, or a few records from a single page.

What is a Range Scan?

A range-scan is a scan operation whereby a range of pages are retrieved – think of this as a full-scan with boundaries.

What is Checkpoints?

A checkpoint creates a known good point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

What are the different type of Checkpoints?

• automatic – Issued automatically in the background.
• indirect – Issued in the background to meet a user-specified target recovery time for a given database.
• manual – Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection.
• internal – Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.

What is Database Snapshot?

A database snapshot is a view of what the source database looked like at the time at which the snapshot was created. This means that all the objects will be the same as what it was when the snapshot was taken and all of the data will be exactly as it was then.
To use database snapshots to recover from an unwanted DML statement, you need to have a suitable snapshot in place. Snapshots can only be created by using a T-SQL statement.

What is Sparse file in Database Snapshot?

To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data.
As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file.

Explain Sparse file Size in database snapshot.

At the creation time sparse file will take very little space, but as the data changes occurred into the parent database, data page before the changes copied into the sparse file.
Thus sparse file grows. Sparse files are the feature of NTFS file system. As the sparse file grows NTFS will allocate the space to sparse file gradually.

How to create database snapshot?

Based on the current size of the source database, ensure that you have sufficient disk space to hold the database snapshot. The maximum size of a database snapshot is the size of the source database at snapshot creation.
Issue a CREATE DATABASE statement on the files using the AS SNAPSHOT OF clause. Creating a snapshot requires specifying the logical name of every database file of the source database.

How does a snapshot work?

• When you create a snapshot a sparse file is created for each data file
• When data is modified in the source database for the first time, the old value of the modified data is copied to the sparse file
• If the same data is the subsequently changed again, those changes will be ignored and not copied to the snapshot
• When you query the snapshot, it first checks if the data is available in the snapshot. If it’s there it reads if from the snapshot. If it’s not there, I reads through to the source database and gets the data from there instead, because that means the data has not yet changed since the time the snapshot was taken.

Can we change the permissions in a database snapshot?

No it’s not possible.

Can we take a backup of a database snapshot?

No.

What are the benefits of Database Snapshots?

• Snapshots can be used for reporting purposes.
• Using a mirror database that you are maintaining for availability purposes to offload reporting.
• Safeguarding data against administrative and user error.

What is hot standby and warm standby server?

Warm servers are often used for replication and mirroring.
A hot server is a backup server that receives regular updates and is standing by ready (on hot standby) to take over immediately in the event of a failover.

What is copy-on-write operation in database snapshot?

Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot.
This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created.

Is database snapshot transitionally consistent?

Each database snapshot is transitionally consistent with the source database at the moment of the snapshot’s creation. When we create a database snapshot, the source database will typically have number open transactions.
Before the snapshot becomes available, the open transactions are rolled back to make the database snapshot transitionally consistent. Just like it follows the recovery interval step and it will not affect the source database.

Can SQL Servers linked to other servers like Oracle?

SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

What is SQL Profiler?

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.
Use SQL Profiler to monitor only the events in which you are interested.
If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected.

Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What is Builtin/Administrator?

The Builtin/Administrator account is basically used during some setup to join some machine in the domain. It should be disabled immediately thereafter. For any disaster recovery, the account will be automatically enabled. It should not be used for normal operations.

How can SQL Server instances be hidden?

To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties.
After selecting properties you will just set Hide Instance to “Yes” and click OK or Apply. After the change is made, you need to restart the instance of SQL Server to not expose the name of the instance.

What are the authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode – SQL and Windows. To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

How can we check whether the port number is connecting or not on a Server?

TELNET [HOSTNAME] PORTNUMBER

Can we uninstall/rollback a service packs from SQL Server?

No: To rollback a SP you have to uninstall entire product and reinstall it.

What do you know about the Hotfixes?

Hotfixs are software pathces that were applied to live i.e. still running systems. A hotfix is a single, cumulative package that includes one or more filfes that are used to address a problem in a software product.

Why shrinking of database is bad in SQL Server?

Shrinking a database increases fragmentation which causes performance problems.
I talked about how databases are like boxes being filled with content every day. As the box gets filled, the amount of free space decreases. But if you know that you will need to fill it up with more content, wouldn’t you go get a bigger box? Sadly, that’s not what we’re doing when we’re shrinking databases.
We’re basically throwing away the box with free space to replace it with a smaller box with just enough space to keep all of the existing content. Imagine what you would do to accomplish this task – empty the box of its contents, throw away the box, replace it with a smaller box and put the contents back in.

Can we install SQL Server using a configure file?

Yes: We can prepare a configuration file. While installing SQL Server the path to the configuration file is specified in the “Ready to Install” page in the configuration file path section.

Which Dynamic memory view is used to check memory usage by SQL Server?

We can use this view sys.dm_os_process_memory.

What do you know about the Lock Page in Memory?

Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems.

What do you know about the SQL Server Latches?

• A latch is used to cordinate the physical execution of multiple threads within a database.
• A latch is a lightweight synchronization object used by the Storage Engine to protect memory structures used internally by SQL Server. A latch is nothing more than a so-called Critical Section in multi-threaded programming.
• For row-level locking it was very important to introduce a concept like latching, because otherwise it would give rise to phenomena like Lost Updates in memory.

What are the different types of Lathces in SQL Server?

• IO Latches
• Buffer Latches (BUF)
• Non-Buffer Latches (Non-BUF)

Can DBA control or manage the SQL Server Latches?

No: It is not possible, It’s internally manage by the SQL Server. We can just check the different statuses of latches.

What is a User Database?

A user database is a database that we create to store data and start working with  the data.

Why is all of the memory allocated for SQL Server not shown in Windows Task Manager?

The” Mem Usage” column shows the amount of memory for the working se t. The working set of a process is the set of pages in the virtual address space of the process that are currently resident in physical memory.
To find out how much memory SQL Server has allocated through the AWE mechanism, you can use the sys.dm_os_memory_clerks DMV.

Which autogrowth database setting is good?

Setting an autogrowth in multiples of MB is a better option than setting autogrowth in percentage (%).

What is Network Packet Size in SQL?

NPS(Network Packet Size) is the size of the TDS (tabular data stream) packets used to communicate between your applications and your relational database engine and default packet size is 4 kilobytes and its manily depends on network packet size configuration option.

How do I resolve ‘SQL Server Setup cannot valid the service accounts’ error message during the setup?

The error message typically happens when you use domain accounts as service account. If the Activity Directory Service (ADS) is not available currently, so the setup cannot valid the domain accounts you specify.

What is difference between OSQL and Query Analyzer ?

Both are the same but there is little difference OSQL is command line tool which is execute query and display the result same a query analyzer but query analyzer is graphical and OSQL.
OSQL have not ability like query analyzer to analyze queries and show static on speed of execution and other useful thing about OSQL is that its helps in scheduling.

How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL is a command line tool. Server in single user mode and -f is used to start the SQL Server in minimal configuration mode.

What is the use of the Public database role in SQL Server?

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.

Explain Certificate based SQL Server Logins/Principals?

Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

• ##MS_SQLResourceSigningCertificate##
• ##MS_SQLReplicationSigningCertificate##
• ##MS_SQLAuthenticatorCertificate##
• ##MS_AgentSigningCertificate##
• ##MS_PolicyEventProcessingLogin##
• ##MS_PolicySigningCertificate##
• ##MS_PolicyTsqlExecutionLogin##

What are steps to load a .NET code in SQL SERVER ?

Write the managed code and compile it to a DLL/Assembly.
After the DLL is compiled using the “CREATE ASSEMBLY” command you can load the assemby into SQL SERVER. Below is the create command which is loading “mycode.dll” into SQL SERVER using the “CREATE ASSEMBLY” command.
CREATE ASSEMBLY AssemblyName FROM ‘C:/MyAssmbly.dll’

How can we drop an assembly from SQL SERVER?

DROP ASSEMBLY AssemblyName

Are changes made to assembly updated automatically in database?

No: it will not synchronize the code automatically. For that you have to drop the assembly (Using DROP ASSEMBLY) and create (Using the CREATE ASSEMBLY again).

If I want to see which files are linked with which assemblies?

Use sys.Assemblies_files system tables have the track about which files are associated with what assemblies.

Does .NET CLR and SQL SERVER run in different process?

.NET CLR engine (hence all the .NET applications) and SQL SERVER run in the same process or address space. This “Same address space architecture” is implemeted so that there no speed issues. If the architecture was implemented the other way (i.e. SQL SERVER and .NET CLR engine running in different memory process area) there would have been reasonable speed issue.

What is SQL Server VSS Writer?

The SQL Writer Service provides added functionality for backup and restore of SQL Server through the Volume Shadow Copy Service framework. When running, Database Engine locks and has exclusive access to the data files.
When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup.
Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running. It must be running when the Volume Shadow Copy Service (VSS) application requests a backup or restore. To configure the service, use the Microsoft Windows Services applet. The SQL Writer Service installs on all operating systems.

What issues SQL Server Upgrade Advisor helps you to identify that might affect your upgrade to latest version of SQL Server?

SQL Server Upgrade Advisor can help identify key issues that might affect your upgrade, such as deprecated or discontinued features, breaking changes and behaviour changes.

What is the quickest way to identify the cause of SQL Server Installation failures?

You can do that by reviewing the summary log files and individual component log files. If you review the summary log file and find installation errors, you should review the specific log file for that component for more information.

Briefly explain the purpose of SQL Server Reporting Services (SSRS)

You create reports that include content from a variety of data sources, including relational databases and OLAP databases with SQL Server Reporting Services (SSRS). You can format the reports on-demand by using a variety of formats, including Microsoft Excel, HTML, and PDF.
You can generate ad hoc (on-the-fly) reports or schedule them for recurring distribution. The primary tools you use to create reports are Report Builder and SQL Server Data Tools (SSDT) – Report Designer.

What is the purpose of SQL Server Full-Text Search Service?

You can query for words and phrases in columns in the database with SQL Server Full-Text Search. You can search for multiple forms of a word. SQL Server Full-Text Search is implemented by the SQL Server Database Engine.

What is Bound Connection?

Bound connections allow two or more connections to share the same transaction and locks. Bound connections can work on the same data without lock conflicts.
Bound connections can be created from multiple connections within the same application, or from multiple applications with separate connections. Bound connections make coordinating actions across multiple connections easier.

 

List out Dynamic Management Views (DMV’s) and the value that they provide to developers or DBAs.

• dm_tran_locks – Locking and blocking.
• dm_clr_loaded_assemblies – Assemblies in available in SQL Server.
• dm_db_file_space_usage – Database file usage to determine if databases are getting low on space and need immediate attention.
• dm_exec_cached_plans – Cached query plans available to SQL Server.
• dm_exec_sessions – Sessions in SQL Server.
• dm_exec_connections – Connections to SQL Server.
• dm_db_index_usage_stats – Seeks, scans, lookups per index.
• dm_io_virtual_file_stats – IO statistics for databases and log files.
• dm_broker_connections – Service Broker connections to the network.
• dm_os_memory_objects – SQL Server memory usage sys.dm_tran_active_transactions – Transaction state for an instance of SQL Server.

Please explain the relationship between logins and users in the system and user databases.

Logins – All logins reside in the master database

Users – All users reside in the master database, other system databases and in the user defined databases.

Briefly explain the purpose of SQL Server Integration Services (SSIS)?

SQL Server Integration Services (SSIS) is a platform for data integration and workflow applications. It was first introduced with SQL Server 2005 replacement for Data Transformation Service (DTS). You build extraction, transformation, and load (ETL) packages with SSIS that extract, transform, and load data. A package is an SSIS object that defines the data source, transformation, and data destination used to integrate data from diverse data sources.

SSIS supports a variety of formats that you can use as the data source when you import data. These formats include: Microsoft Excel, Flat files, OLE-DB data sources, XML data sources etc. SSIS also supports SQL Server and these formats as data destination formats.

You have a default instance for SQL Server 2012 running on one computer. You install SQL Server 2014 on a different computer. You want to migrate the user databases from SQL Server 2012 to SQL Server 2014. What should you do?

You should register the SQL Server 2012 instance in SQL Server 2014 Management Studio and use the Copy Database Wizard to migrate the database.

You are upgrading a default instance of SQL Server 2012 to SQL Server 2014. What will be used as the SQL Server service account?

The service account specified for the SQL Server 2012 instance will be used as the SQL Server 2014 service account.

If you encounter this kind of an error message, what you need to look into to solve this problem?
[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL Server not found.

Check if MS SQL Server service is running on the computer you are trying to log into.
Check on Client Configuration utility. Client and Server have to in sync.

What kind of LAN types do you know?

Ethernet networks and token ring networks.

What is SQL Azure?

SQL Azure is a cloud based relational database as a Service offered by Microsoft. Conceptually it is SQL server in the cloud.

How can we migrate from SQL server to SQL Azure?

For Data Migration, we can use BCP or SSIS. And for schema Migration we can use Generate Script Wizard. Also, we could use a Tool called SQL Azure migration wizard available on codeplex.

What is the system function to get the current user’s user id?

USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

What are the built-ins used for processing rows?

• Get_group_row_count(function)
• Get_group_selection_count(function)
• Get_group_selection(function)
• Reset_group_selection(procedure)
• Set_group_selection(procedure)
• Unset_group_selection(procedure)

How many files can a Database contain in SQL Server?

A Database can contain a maximum of 32,767 files.

How do I troubleshoot SQL Server connectivity issues?

• Check your SQL Server Error log to make sure that it enables TCP/IP protocol and establish the listening on a specific TCP port.
• Enable TCP/IP protocol if you could not find the message in the Error Log.
• Check firewall if you cannot telnet to the specific TCP port.
• Ensure that the TCP port on which SQL Server is listening has been put into the exceptions of your firewall.

What is the maximum limit of SQL Server instances for a standalone computer?

50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster.

Why did a T-SQL script fail to run a job when it could successfully run in SQL Server Management Studio ?

If the SQL Server Agent Service account is different from the login account and if it does not have enough permission to execute the query, the job will fail.
You can simply give the SQL Server Agent Service account permissions to execute the query, this is not the best way from a security perspective. For SQL Server Agent Service account, it is recommended to give it the least amount of privileges.

How can I degrade a SQL Server database from a higher version to a lower one?

Yes, degrading a SQL Server database is not supported, but you can use a flexible workaround to resolve this issue. You can use SQL Server Management Studio to first generate scripts for a lower ersion and then use Data Import/Export Wizard to transfer data.

What is the difference between dropping a database and taking a database offline?

Drop database deletes the database along with the physical files, it is not possible to bring back the database unless you have a backup of the database.
When you take a database offline, you the database is not available for users, it is not deleted physically, it can be brought back online.

What is the main role of Model Database in SQL Server?

The model database, as its name implies, serves as the model (or template) for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from model every time SQL Server starts up.

What is a collation in SQL Server?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

What is Use of @@ SPID in SQL Server?

A SPID is the returns sessions ID of the current user process. And using that session ID, we can find out that the last query was executed.

What is cloud computing?

Cloud computing is a model for enabling ubiquitous, convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, servers, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction.

How do I rename my SQL Server instance?

SQL Server doesn’t support renaming the instance name directly so you can uninstall the existed instance and install it with the new name and change the computer name and rename the part of the instance name that corresponds to the computer name.

How do I store data in multiple languages within one database?

The easiest way to manage character data in international databases is to always use the Unicode nchar, nvarchar, and nvarchar(max) data types, instead of their non-Unicode equivalents, char, varchar, and text. Unicode is a standard for mapping code points to characters.

How do I configure SQL Server to enable distributed transactions via Linked Server?

To enable distributed transactions across different servers, you need to configure MS DTC on both servers and some of your SQL Server server options.
Please run “services.msc” from windows run dialog to check DTC service is running or not.
Please make sure this service could access the network and could receive connection from other server.
Please enable the XACT_ABORT.

What is recompilation?

When the cached execution plan for a query cannot be used so the procedure recompiles.

How do I configure Windows Firewall to allow remote connections using TCP/IP protocol for SQL Server?

On the server on which SQL Server runs, we need to find out which ports that SQL Server is listening and add which to Windows Firewall Inbound Exceptions.
Firstly, we need to add TCP port on which SQL Server service is listening to the Windows Firewall Inbound Exceptions so that the client can make connections to SQL Server over Windows Firewall.
Secondly, if we do not specify TCP port in the connection string and SQL Server is not listening on the TCP 1433 port, we need to add UDP 1434 port on which SQL Server Browser Services is listening to the Windows Firewall Inbound Exceptions.

What is parallelism?

SQL Server can perform a query or index operation in parallel by using several operating system threads, the operation can be completed quickly. When a single query runs of multiple CPUs is known as query parallelism.

What is the default query threshold for parallelism?

The query optimizer decides to utilize multiple SPIDS running on different processors to query / transfer data. Default threshold is 5 seconds.

How to start the SQL Server with minimal configuration?

If there are any configuration problems that prevent the server from starting, you can start an instance of Microsoft SQL Server by using the minimal configuration startup option. This is the startup option -f.  Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode.

What is auto close option?

AUTO CLOSE option:
When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.
When set to OFF, the database remains open after the last user exits.

What do you know about the MSDTC service?

MSDTC (aka Microsoft Distributed Transaction Control) is a piece of software which a lot of people use, but they don’t really know what it does, or how it works. MSDTC is used by SQL Server and other applications when they want to make a distributed transaction between more than one machine.

What do you know about the port UDP 1434?

UDP port 1434 is used for SQL Server named instances. The SQL Server Browser service listens on this port for incoming connections to a named instance. The service then responds to the client with the TCP port number for the requested named instance.

What do you know about the SQL Server Browser Service?

While commonly thought of as “one of those other SQL Server services”, life without the SQL Server Browser Service would be a bit more of a hassle. Its main purpose is to aid in the detection of SQL Server instances and provide information necessary for connecting to them.  It listens on UDP port 1434 and returns port numbers and version information for requested instances on a machine.
When a user connects to the default instance, the connection is passed to TCP 1433 automatically.  When connecting to a named instance and the port number is unknown, the first connection made is to the Browser Service at UDP 1434, which then returns the proper port number on which to initiate the TCP connection.

What is the Instance-aware and Instance-unaware services in SQL Server?
Instance-aware services are associated with a specific instance of SQL Server.

• SQL Server
• SQL Server Agent
• Analysis Services
• Reporting Services
• Full-text search

Instance-unaware services are shared among all installed SQL Server instances.

• Integration Services
• SQL Server Browser
• SQL Server Active Directory Helper
• SQL Writer

Difference between Database Mirroring and Log Shipping in SQL Server:

Database mirroring is a functionality in the SQL Server engine that will read from the
transaction log and then copies those transactions from the principal server instance to the
mirror server instance. Database mirroring can operate synchronously or asynchronously. If
configured to operate synchronously, the transaction on the principal will not be committed until
it is hardened to disk on the mirror. Database mirroring supports only one mirror for each
principal database. Database mirroring also supports automatic failover if the principal
database becomes unavailable. The mirror database is always offline in a recovering state, but
you can create snapshots of the mirror database to provide read access for reporting, etc.

Log shipping is based on SQL Server Agent jobs that periodically take log backups of the
primary database, copy the backup files to one or more secondary server instances, and restore
the backups into the secondary database(s). Log shipping supports an unlimited number of
secondaries for each primary database. It also can provide you with a more flexible solution to
use a reporting server instance with older data.

In my experience, database mirroring is preferable to log shipping in most cases, although log
shipping does have the following advantages:

1. Provides backup files as part of the process
2. Multiple secondaries are supported
3. Possible to introduce a fixed delay when applying logs to allow the secondary to be used for
recovering from user error or data corruption

Difference between Database Mirroring and Log Shipping in SQL Server:

Sno	Item	Database Mirroring	Log Shipping
1	Jobs	Jobs are not involved. Manual Copy/Restore method copy data principal to Mirror supported. Attach/detach method not supported	SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s).
2	Server Limitation	Only one mirror Server Supported	Can be applied to multiple standby server
3	Recovery model	Full Recovery Model only	Full & Bulk Recovery Model
4	Read from Destination	Mirror DB always recovering state To Read it we need to use Database Snapshot	We can use stand-by option to read db on stand by server
5	DB support	We can support more than one db between principal and Mirror. It works database level	We can support multiple db between instances and it works both server and db level
6	Role Change	Fully automatic	Manual
7	Auto page Recovery	Auto page Recovery supported from SQL 2008 onwards. It will recover damage pages	Auto Recovery Page not supported
8	Failover	It s automatic in high safety with automatic failover only	Manual changes required
9	Failover Duration	Failover is Fast, sometimes <3seconds but not more than 10 seconds	Can make more than 30 mints
10	Data Transfer	Individual T-Log Records are Transferred using TCP endpoints	T-Logs are Backedup and Transferred to Secondary server.

1) What is default port of Database Mirroring Endpoint?
Ans : 5022
2) Database Mirroring comes with which edition?
Ans: SQL Server 2005 SP1. Or SQL Server 2005 RTM with trace flag 1400

3) When I configure mirroring I’m receiving the below errror,
One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click start mirroring again
Ans: The fully qualified computer name of each server can be found running the following from the command prompt:
IPCONFIG /ALL
Concatenate the “Host Name” and “Primary DNS Suffix”
Host Name . . . . . . . . . . . . : A
Primary Dns Suffix . . . . . . . : corp.mycompany.com
Then FQDN of your computer name is just A.corp.mycompany.com.

4) How to enable mirroring by Script ?
Ans: – Specify the partner from the mirror server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://A.corp.mycompany.com:5022′;
– Specify the partner from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://B.corp.mycompany.com:5022′;
Note: Replace the dbname before using the above script

5) How to disable mirroring by script?
Ans: ALTER DATABASE [AdventureWorks] SET PARTNER OFF
Note: Replace the dbname before using the above script

6) How to do manual failover to Mirror when principle is working fine?
Ans: ALTER DATABASE <DB Name> SET PARTNER FAILOVER

7) Why I’m getting the below error message while configuring database mirroring?
Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring
Ans : You need to restore the Full backup from principal server using With NoRecovery option and also one transactional log backup from principal server using With NoRecovery option and then start configuring mirroring.

8) Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?
Ans: Nope its not possible, both principal and mirror should have same edition

9) Is it possible to take backup of mirrored database in mirror server?
Ans: No

10) Is it possible to perform readonly operation at mirrored database in mirror server?
Ans: Yes, You can create database snapshot for the same

11) Can I create multiple endpoints for configuring different databases for mirroring and point each database to unique endpoint.

Ans: No

12) Can I configure a single database to be mirrored to more than one server. i.e) One source & many destination like logshipping
Ans: No

13) How can I increase Heartbeat time between principal and mirror server?? By default its 10 sec.
Ans: ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT 30
Note: Before using the script change the dbname.

14) What status of mirroring has if secondary is down?
Ans: If secondary is down principle or Mirror show status disconnected

15) What status of mirroring has if principle is down?
Ans: If principle is down mirror will be disconnected with in recovery instead of synchronized with restoring

16) What status of mirroring has if mirroring is paused?
Ans: Is mirroring is set to paused from principle then then both principle & mirror in suspending

17) How to bring mirror DB online if Principle is down?
Ans: ALTER DATABASE <DB Name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
If you try to do failover like normal situation when principle is online [ALTER DATABASE <DB Name> SET PARTNER FAILOVER] then you will receive below error.
ALTER DATABASE <DB Name> SET PARTNER FAILOVER
Msg 1404, Level 16, State 10, Line 1
The command failed because the database mirror is busy. Reissue the command later.

18) System Store Procedure to monitor Mirroring?
Ans: MSDB.SYS.SP_DBMMONITORRESULTS

19) What are different possible Mirroring Stats?
Ans:

SYNCHRONIZING
SYNCHRONIZED
SUSPENDED
PENDING FAILOVER
DISCONNECTED

1. What are the Editions of SQL Server available?
2. What is SQL Server Instance?
3. What is the difference between default and Named Instance? (Video Answer)
4. How many SQL Server Instances can be installed on one Server? (Video Answer)
5. What is Collation? What Collation will you pick while installing SQL Server?
6. Can you have different Collation on different Databases in SQL Server Instance? (Video Answer)
7. Can Instance Level Collation be different from Database Collation? (Video Answer)
8. What are the best practices to place data files, log files and tempdb on Storage?
9. What are file groups? How would you relate File groups with Partitioned Table?
10. Is it best practice to have Auto Shrink enable on Database? (Video Answer)
11. While installing SQL Server instance , you have used default accounts. Later you want to change to Service account, how would you do that? (Video Answer)
12. What is TDE and why do we use it?
13. If TDE is enabled on Database, Do we have to write some special code on application side to access data?
14. What are the steps to enable TDE on database?
15. Is there any performance overhead because of TDE enabled on Database?
16. If you need to restore TDE Enabled database to different server, what steps are involved?
17. What are different types of data compression available in SQL Server?
18. How would you allocate minimum and maximum memory to SQL Server Instance?
19. What is the difference between Offline and Emergency state of a Database?
20. Can you set Database into ReadOnly state?
21. What is CPU Affinity?
22. What is IO Affinity? (Video Answer)
23. What is MAXDOP , How do you change it?
24. How would you find out who has dropped database?
25. If you have to find out that how many times the backup of a database is taken, Where will you look for that information?
26. How would you find open transactions in SQL Server?
27. How would you find blocked processes in SQL Server?
28. What is locking in SQL Server? (Video Answer)
29. How would you trace deadlock in SQL Server? ( Video Answer)
30. Let's say you have lost the password for sa, How would you recover that? (Video Answer)
31. What is TUF(Transaction Undo File) file and in which process it is created? (Video Answer)
32. What is link Server? Can link server be created for Oracle from SQL Server? (Video Answer)
33. What is sparse column? What are the advantages and disadvantages?
34. Which DBCC Commands have you used often as SQL Server DBA?
35. How would you script all SQL Server Agent Jobs? (Video Answer)
36. How would you script entire database (Tables, SPs,Views etc.) with data?
37. How would you migrate a database from SQL Server Instance to Another SQL Server Instance?  (Video Answer)
38. You have taken a backup of a database from SQL Server 2008R2, Can you restore this to SQL Server 2005 Instance?
39. What will be your strategy when you need to migrate SQL Server 2008 to SQL Server 2012? (Video Answer)
40. What is difference between Backup/Restore and Detach/Attach Database? (Video Answer)
41. When should we update Statistics on SQL Server Database and why? (Video Answer)
42. How would you find out how much space is allocated to Database Log file and how much is used? (Video Answer)
43. What is Database Engine Tuning Advisor and where do you have used it? (Video Answer)
44. If you need to Kill all processes related to a Database , how would you do that?
45. Explain the steps to perform Table Partitioning and best practices? (Video Answer)
46. How would you determine the version and Edition of SQL Server Instance you are working on? (Video Answer)
47. What are the steps to Restore a SQL Server Database to a Point in Time? (Video Answer)
48. How would you shrink the tempdb database in SQL Server? (Video Answer)
49. Which built in tool you have used to Monitor SQL Server activity? (Video Answer)
50. How would you identify table level locks in SQL Server? (Video Answer)
51. What is isolation level and what are four types of them? (Video Answer)
52. If you need to query Oracle database from SQL Server, how would you do that? (Video Answer)
53. What are the differences between DMV's and DMF's? (Video Answer)
54. The tran log of a database has grown huge, How would you shrink the log file? (Video Answer)
55. What are the important points you consider before coming up with Backup strategy? (Video Answer)
56. You have installed SQL Server Instance on Window 2008 Server. Total Memory of Server is 64 GB but you need to assign only 12 GB to SQL Server Instance, how would you do that? (Video Answer)
57. What is the difference Between Index Rebuild and Index Reorganize? (Video Answer)
58. How would you create a SQL Server Agent job that only should run on 5th business day of each month? (Video Answer)
59. You have provided some permission on a Database to a users, but user is not able to connect to that Database. Where will you see the error details in SQL Server? (Video Answer)
60. You have a big database, Before taking the full backup you want to estimate the total time for backup. How would you do that? 
61. You have received a large( 2 GB in size) .sql file, How would you execute that .sql file on SQL Server?
62. You need to find out the Job execution history for last ten days, Where will you search for that? (Video Answer)
63. You are planning to move database from one server to another server. But there are changes the stored procedures are using some cross database queries. How would you find out if any of the Stored Procedure is using DatabaseTest in definition?
64. Your company has found out that the account SQL Services are running is not what it should be. They want you to change to Service account, How would you do that? (Video Answer)
65. What is the differences between differential database backup and Transaction log backup? (Video Answer)
66. What permissions will you grant to a users in SQL Server database, so He/She can truncate table?
67. What are DDL Triggers? Where and why have you created them? (Video Answer)
68. How would you perform Schema comparison between two databases? (Video Answer)
69. You need to perform Data Comparison between two tables,How would you do that? (Video Answer)
70. You need to backup all the logins and Jobs, How would you do that? (Video Answer)
71. You have installed Name Instance of SQL Server, After installation you realized that you misspelled the instance name. Can you correct the name of SQL Server Instance without re- installation?
72. You have installed SQL Server instance on Windows 2008 (MyServerName\MySQLInstance). If your want to change the computer name to only ServerName, Will SQL Server Instance will or You have to re-install SQL Server Instance?
73. What is DAC (Dedicated Administrator Connection) ? Is it enabled or disabled by default? Why and where we use this feature?
74. You need to create full backup of all the databases from one of the SQL Server Instance every night. If any new database is created that should also become the part of backup process. What would be your approach to perform this? (Video Answer)
75. All the sudden, users start complaining that the SQL Server is running slow. What steps you would take to analysis the problem? (Video Answer)
76. What is by default SQL Server Agent Job history Retention? If you need to keep two week of history for each of the job what steps will you take when some of the jobs run every five minutes and some run one time a day? (Video Answer)
77. If you need to find all the processes running by specific login, How would you do that? (Video Answer)
78. How would you find out how many transactions/second SQL Server Instance is performing? (Video Answer)
79. How would you find currently running queries on SQL Server Instance? (Video Answer)
80. How would you find orphan users and fix them? (Video Answer)
81. How would you script user with permission from a SQL Server Database?
82. Your company has a Database for which the Tran Log grows very fast. What strategies you should adopt to keep the Tran log reasonable? (Video Answer)
83. What is the difference between Actual Execution plan and Estimated Execution plan? (Video Answer)
84. What is the difference between Differential Backup and Full Backup? (Video Answer)
85. What are two Server Authentication modes available in SQL Server? (Video Answer)
86. If you have installed SQL Server Instance with Windows Authentication Mode and later you want to change to Mix Mode (SQL Server and Windows Authentication Mode), How would you do that and does it require service restart? (Video Answer)
87. What is the difference to Restore database "Restore With NoRecovery" and "Restore With StandBy"? (Video Answer)
88. What are major differences between SQL Server 2005 and SQL Server 2008 version?
89. What are the major difference between SQL Server 2008/R2 and SQL Server 2012 version?
90. What is the difference between Full Backup and Copy option in Full Backup? (Video Answer)
91. How would you find out that How long SQL Server Instance is running? (Video Answer)
92. What is index fragmentation? (Video Answer)
93. What is the difference between Physical and Logical Reads? (Video Answer)
94. Does SQL Server allow duplicate indexes ( indexes with different name but same definition)? If yes, How would you find them and keep only one of them? (Video Answer)
95. What is the difference between shrinking and truncating database log file? (Video Answer)
96. What are the best practice to configure TempDB? (Video Answer)
97. What is Latch? What is the difference between Latch and Lock? (Video Answer)
98. What is the difference between Schema and Database?
99. What is the Statistics in SQL Server? (Video Answer)
100. What are Trace Flags in SQL Server? (Video Answer)
101. What is SQL Server hash operator?
102. What is the difference between 64-Bit and 32-Bit releases of SQL Server? (Video Answer)
103. What are the best practices to shrink a database? 
104. What is Service Account in SQL Server? (Video Answer)
105. What is SQL Server Column Encryption? What are the steps to create Column Encryption?
106. What is the difference between Service Master Key and DataBase Master Key?
107. To See encrypted data by Column Encryption, What permissions are required by user to access data?
108. If you need to Backup and Restore Column Encrypted Database, What steps has to be performed?
109. If we enable Column Level Encryption in SQL Server, Will there any impact on performance?
110. What is Encryption Hierarchy in SQL Server?
111. What is Replication in SQL Server? (Video Answer)
112. Why do we use Replication, Provide couple scenarios? (Video Answer)
113. What are the types of Replication? (Video Answer)
114. When do we use snapshot replication? (Video Answer)
115. What exactly merge replication is? (Video Answer)
116. Can you schedule replication? And under what circumstances do we schedule replication? (Video Answer)
117. Your team needs to know if replication breaks/fails, what exactly would you do to accomplish that? ( Video Answer)
118. What is re-initializing means in replication? (Video Answer)
119. Under what circumstances will you re-initialize replication?
120. How would you add new tables in existing replication? 
121. Can you explain what would be the replication overhead on production server? 
122. Transactional replication is set on production source, article schema changed on source but target is not showing that change, what exactly is the issue? (Video Answer)
123. How will you truncate the replicated table? (Video Answer)
124. How would delete replicated database? (Video Answer)
125. Can you bring replicated database offline? (Video Answer)
126. What is orphan replication? And how would you cleanup replication? (Video Answer)
127. What is the difference between log shipping and replication? (Video Answer)
128. Under what scenarios would you use log shipping? (Video Answer)
129. What is new in SQL server 2012 that's not available in previous versions of SQL servers? (Video Answer)
130. What is AlwaysOn in sql server 2012? (Video Answer)
131. What are prerequisites of AlwaysOn? (Video Answer)
132. What is Availability Group? (Video Answer)
133. How many databases can be in One Availability Group? (Video Answer)
134. What is Listener in AG? (Video Answer)
135. Why do we use Listener in Availability Group? (Video Answer)
136. Under what circumstances Availability Group fails over? (Video Answer)
137. What is primary replica in AG? (Video Answer)
138. What is secondary replica in AG? (Video Answer)
139. How many secondary replica can be configured? (Video Answer)
140. What are some advantages of using AlwaysOn feature? (Video Answer)
141. Can you rename Availability Group? (Video Answer)
142. You have setup AlwaysOn and send application team to connect with SQL server using Listener, application can't connect with Listener name, what could be the issue?
143. Can you configure Listener using static port? (Video Answer)
144. Listener port is set to 1533, can you connect to SQL server using Listener name? (Video Answer)
145. Availability Group is in resolving state, what does it mean? (Video Answer)
146. What are SQL server Browsing services? (Video Answer)
147. Why do we use SQL server Browsing services? (Video Answer)
148. What is recommended configuration of SQL server browsing services? (Video Answer)
149. Can you setup replication with AlwaysOn? (Video Answer)
150. Can you use primary or secondary replica as a distributor? (Video Answer)
151. What are the issues using primary or secondary replica as your main distributor? (Video Answer)
152. What is Auto failover of primary replica? (Video Answer)
153. What does read intention mean in AG? (Video Answer)
154. What does in-memory Store procedure mean? (Video Answer)
155. What are tempdb recommended settings in sql server 2012?
156. SQL server connection is timing out, what would you do to resolve it?
157. Tempdb log is full, how would you shrink tempdb? (Video Answer)
158. Can you move tempdb files location without restarting sql server services?
159. What is SQL Server Clustering? (Video Answer)
160. Why do we use clustering? (Video Answer)
161. What are the prerequisites of sql server clustering?
162. How to add a resource in existing cluster? (Video Answer)
163. What is failover? 
164. What is shared storage in SQL server cluster?
165. How would you find out if SQL server failover happened? (Video Answer)
166. Applications can't connect to SQL Server after failover, what could be the issue? (Video Answer)
167. SQL Server Agent resource is not coming online, what could be the issue?
168. What is the difference between cluster mode and standard mode sql server installation? (Video Answer)
169. SQL server cluster installation failed, where would you look the cause of failure? (Video Answer)
170. How many SQL server instances can you add in a cluster? (Video Answer)
171. Can you find out using SSMS current node for SQL Server Services? (Video Answer)
172. What is alias in sql server?
173. How can you find out sql server IP address?
174. How can you find out SQL server Port? 
175. What is active-active cluster? (Video Answer)
176. What is active-passive cluster? (Video Answer)
177. What is cluster aware? (Video Answer)
178. Is SSRS cluster aware? (Video Answer)
179. Is SSIS cluster aware? (Video Answer)
180. Is it recommended to install SSRS and SSIS during cluster mode SQL Server Installation? (Video Answer)
181. What is scale out deployment in SSRS? (Video Answer)
182. Can you configure SSIS as cluster aware?
183. Some of your external vendors can not connect to SQL server in intranet, what could be the cause? (Video Answer)
184. You can't connect SQL server from client machine with in intranet, what could be the cause? (Video Answer)
185. Client can connect to SQL server using IP address but can't connect using SQL server instance name, what could be the cause? (Video Answer)
186. How to configure SSIS to store packages in MSDB? (Video Answer)
187. How to configure SSIS to store package in central location?
188. What is backward compatibility means in SSIS?
189. How to configure SQL server's memory? (Video Answer)
190. What are best practices to configure SQL server's memory?
191. If SQL server's memory is not configured, what can happen to the system? (Video Answer)
192. System Admin sends you an email that SQL Server services are taking almost all the memory, what steps would you take to resolve it? (Video Answer)
193. SQL server lost connection with AD, what will happen to SQL Server?  (Video Answer)
194. SQL services are not starting, where would you look for the cause? (Video Answer)
195. Can you restore master database? (Video Answer)
196. You want to know if SQL services restarted or stopped via email, how would you accomplish that? (Video Answer)
197. How do you keep up with SQL server updates?
198. Have you ever installed SQL server patches?
199. What is the latest service pack available in sql server 2012? (Video Answer)
200. You are restoring the databases by using SQL Server agent job,How would you find that how much percentage of restoration is completed?
201. What is deployment ? Have you been involved in deployment process?
202. If you have to deploy 20 SSIS Package to SQL Server Integration Services, How would you do that?
203. If you have to deploy SSIS Packages to File system, What steps would you follow?
204. What is Version Control? Which software you have used for version controlling?
205. What is Team Foundation Server? Why did you use it?
206. What is Check-In and Check-Out in TFS?
207. What are the best practices when you need to deploy DDL and DML scripts to Database?
208. How would you deploy SSRS report to Report Server? If you have to deploy 100 reports to Report Server how would you do that?
209. Can Data Sources, Data Sets deployed from SSRS Project to Report Server? or You have to create them manually?
210. How would you deploy SSAS Cube to SQL Server Analysis Services Server?
211. Can we deploy more than one Cube in SSAS Database?
212. What are the best practices for SQL Server Change Management?
213. A users has left the organization, How would you drop his login and user name from all databases from SQL Server?
214. You need to set Recovery mode =Simple for all the databases on development machine. How would you do that quickly? 
215. How would you rename logical files of SQL Server Database? 
216. How would you move data or log file of a Database from one drive to another? 
217. What are the types of Database files? How would you get the information about them?
218. What is Contained Database? In which version of SQL Server they were introduced?  (Video Answer)
219. What is Policy- Based Management in SQL Server? (Video Answer)
220. How would you identify the isolation level used by the query when dead lock occurs? (Video Answer)
221. What is FileTable Feature in SQL Server, How does it works? (Video Answer)
222. You need to import large amount of data to tables in a database, you want to keep the transaction log size small while import the data, How would you do that? (Video Answer)
223. You need to save passwords in SQL Server Table, What type of encryption would you use?
224. For what purpose do you use Dynamic management views?
225. You have 35  Databases on one of the SQL Server instance, You need to create process to take full backup nightly and transaction log backup every hour, how would you do that? (Video Answer)
226. You need to store and manager unstructured data in SQL Server, Which approach you would use it? (Video Answer)
227. What is FileStream in SQL Server? (Video Answer)
228. What are advantages of FileStream?
229. What is the difference between FileStream and FileTable?
230. You have a Server that has 24 processors, You need to install multiple SQL Server instances on it.What method you will use to allocate processors to each instance depending upon the requirement? (Video Answer)
231. Suppose you have a big table with million of rows. You need to provide a solution in which the most recent data should be stored on fastest storage and old data on slow storage, What solution would you provide for this scenario? (Video Answer)
232. Why do you use Resource Governor? (Video Answer)
233. Can you use Resource Governor for SSAS,SSIS and SSRS services? (Video Answer)
234. How would you find out when SQL Server was restarted last time?
235. You have CDC enabled on a database, If you take a backup of CDC enabled Database and restore to another instance, Will CDC Tables available?
236. How would you restore CDC enable Database to different instance in a way that CDC tables should be available?
237. If You have to include new article ( table, view, Stored Procedure or function) in Replication, how would you do that?
238. What are different type of indexes available in SQL Server? (Video Answer)
239. What are filtered Index in SQL Server? (Video Answer)
240. You need to save credit card information in SQL Sever, What would be your suggestion for encryption?
241. You need to save some image file for your application, What would you suggest? You are using SQL Server 2014.
242. Can you create two tables with different collation in a database? (Video Answer)
243. You get the request to create ER( Entity Relationship Diagram) for the tables in a database, Which tool/s would you use to create that? (Video Answer)
244. What is Full Text Search in SQL Server? Where do you need to use this feature? (Video Answer)
245. Do you recommend auto growth for Database files? If not why? (Video Answer)
246. ANSI explain 4 types of isolation, SQL Server has  one more type in SQL Server , What is the name of that? (Video Answer)
247. What is default Isolation level set for SQL Server Instance? (Video Answer)
248. Can you have In memory Table without Index or without Primary key? If not then Why? (Video Answer)
249. What's your experience with Backup Compression, It does compress the backup file, Does it take long to restore compressed backup?
250. How Database Backup Encryption in SQL Server 2014 is different from TDE ( Transparent Data Encryption).
251. Is it recommended to Run DBCC CheckDB on Production servers? If not then what solution you suggest? (Video Answer)
252. What is mount point? Why do we need to use it? (Video Answer)
253. What are data pages in SQL Server? (Video Answer)
254. What are Extends in SQL Server? (Video Answer)
255. How many files do you need for Tempdb? (Video Answer)
256. Can SQL Server Instance be up without TempDB? (Video Answer)
257. Is TempDB created based on Model database properties?
258. How would you find out if CDC ( Change Data Capture ) is enable or disable on a database?
259. How would you enable CDC on a Database?
260. How would you disable CDC on a Database?
261. How would you configure CPU Affinity?
262. How would you track changes of a particular period by using Change Data Capture function?
263. How would you disable CDC ( Change Data Capture ) on a table?
264. How to Configure existing standalone sql server named instance if Machine names changes? (Video Answer)
265. Replication Error, can't connect to actual server, @@servername returns Null (Video Answer) 
266. How to give read permission to non administrative accounts to Event Viewer in Windows 2008R2/2012 ? (Video Answer)
267. How to resolve Availability Group Listener Errors?

Refer for more on SQL Server Database Mirroring
http://www.techbrothersit.com/search/label/SQL%20SERVER%20DBA%20INTERVIEW%20QUESTIONS
 
https://mssqlfun.com/2014/11/13/script-to-monitor-sql-server-database-mirroring-status/
https://mssqlfun.com/2014/11/10/add-witness-to-existing-sql-server-database-mirroring/
https://mssqlfun.com/2014/11/03/sql-server-database-mirroring-monitoring/
https://mssqlfun.com/2014/10/29/add-database-file-on-database-invloved-in-db-mirroring-3/
https://mssqlfun.com/2014/10/27/remove-witness-from-sql-server-database-mirroring/
https://mssqlfun.com/2014/10/14/database-mirroring-states-sql-server/
https://mssqlfun.com/2014/10/13/configure-sql-server-database-mirroring/
Reference : Rohit Garg (https://mssqlfun.com/)
You can find and follow MSSQLFUN :-
http://www.facebook.com/mssqlfun
https://mssqlfun.com/feed
https://twitter.com/Rgarg86
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
http://beyondrelational.com/members/RohitGarg/default.aspx

Ref:
https://mssqlfun.com/2014/11/17/interview-questions-on-sql-server-database-mirroring/




Ref:
http://www.dbrnd.com/sql-server-advance-important-interview-questions-answers-for-database-administrators-developers/
http://sqltechtips.blogspot.in/