1.What are the minimum Software requirements to install SQL Server 2012?
Internet Explorer 7 or a later version is required for Microsoft Management Console (MMC), SQL Server Data Tools (SSDT), the Report Designer component of Reporting Services, and HTML Help
SQL Server 2012 does not install or enable Windows PowerShell 2.0; however Windows PowerShell 2.0 is an installation prerequisite for Database Engine components and SQL Server Management Studio.
NET 3.5 SP1 is a requirement for SQL Server 2012 when you select Database Engine, Reporting Services, Replication, Master Data Services, Data Quality Services, or SQL Server Management Studio, and it is no longer installed by SQL Server Setup.
Dot NET 4.0 is a requirement for SQL Server 2012. SQL Server installs .NET 4.0 during the feature installation step. SQL Server Express does not install .NET 4.0 when installing on the Windows 2008 R2 SP1 Server core operating system. You must install .NET4.0 before you install SQL Server Express on a Windows 2008 R2 SP1 Server core operating system.
2. For SQL Server Setup installs what are the software components required?
Dot NET Framework 4.1
SQL Server Native Client
SQL Server Setup support files
3. Where will you find the SQL Server installation related logs?
%programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\
4. Where we will get summery.txt file?
5. How much minimum space required to install SQL?
6. What is “ConfigurationFile.ini” file?
SQL Server Setup generates a configuration file named ConfigurationFile.ini, based upon the system default and run-time inputs. The ConfigurationFile.ini file is a text file which contains the set of parameters in name/value pairs along with descriptive comments. Many of the parameter names correspond to the screens and options which you see while installing SQL Server through the wizard. We can then use the configuration file to install SQL Server with the same configuration instead of going through each of the installation screens.
Installation configuration file are saving in this location:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120119_083229\ConfigurationFile.ini
7. What is a service account?
Based on the selected components while doing the installation we will find respective service to each component in the Windows Services. e.g. SQL Server, SQL Server Agent, SQL Analysis Services, SQL Server integration Services etc. There will be a user for each and every service through which each service will run. That use is called Service Account of that service.
Mainly we categorize the Service account as below:
Local User Account: This user account is created in the server where SQL Server is installed; this account does not have access to network resources.
Local Service Account: This is a builtin windows account that is available for configuring services in windows. This account has permissions as same as accounts that are in the users group, thus it has limited access to the resources in the server.
Local System Account: This is a builtin windows account that is available for configuring services in windows. This is a highly privileged account that has access to all resources in the server with administrator rights.
Network Service Account: This is a builtin windows account that is available for configuring services in windows. This has permissions to access resources in the network under the computer account.
Domain Account: This account is a part of our domain that has access to network resources for which it is intended to have permission. It is always advised to run SQL Server and related services under a domain account with minimum privilege need to run SQL Server and its related services.
8. Do we need to grant Administrator permissions on the Windows server to SQL Service account to run the services or not, why?
No, it is not required. It’s not mandatory to grant Administrator permissions to the service account.
9. What permissions are required to install SQL Server on a server?
User through which we are installing SQL Server must have administrator permissions on the Windows server.
10. What are Shared Features Directory and its usages?
This directory contains the common files used by all instances on a single computer e.g. SSMS, sqlcmd, bcp, DTExec etc. These are installed in the folder <drive>:\Program Files\Microsoft SQL Server\110\ , where <drive> is the drive letter where components are installed. The default is usually drive C.
11. What are the shared services in SQL server installation?
12. What is an Instance?
An instance of the Database Engine is a copy of the sqlservr.exe executable that runs as an operating system service. Each instance manages its own system databases and one or more user databases. An instance is a complete copy of an SQL Server installation.
13. Type of Instance and maximum no. of instances which can be installed on a server.
There are two types of Instances.
Default instance
Named Instance
Each computer can run maximum of 50 instances of the Database Engine. One instance can be the default instance.
The default instance has no name. If a connection request specifies only the name of the computer, the connection is made to the default instance.
A named instance is one where you specify an instance name when installing the instance. A connection request must specify both the computer name and instance name in order to connect to the instance.
14. Can we install multiple instances on the same disk drive?
Yes, we can install multiple instances on the same disk drive because each installation creates its own folder with the below format.
MSSQL11.INSTANCENAME
15. What is a collation and what is the default collation?
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.
Default collation: SQL_Latin1_General_CP1_CI_AS
16. What is an RTM setup of SQL Server?
RTM stands for release to manufacturing.
17. What is a Service Pack, Patch, Hot fix and its difference?
Service Pack is abbreviated as SP, a service pack is a collection of updates and fixes, called patches, for an operating system or a software program. Many of these patches are often released before the larger service pack, but the service pack allows for an easy, single installation.
Patch – Publicly released update to fix a known bug/issue
Hotfix – update to fix a very specific issue, not always publicly released
18. What is the latest Service pack available for SQL Server 2012 in the market?
SQL Server 2012 Service Pack 2 (SP2)
Version: 11.0.5058.0
Release Date: 6/10/2014
19. What’s the practical approach of installing Service Pack?
Steps to install Service pack in Production environments:
First of all raise a change order and get the necessary approvals for the downtime window. Normally it takes around 45-60 minutes to install Service pack if there are no issues.
Once the downtime window is started, take a full backup of the user databases and system databases including the Resource database.
List down all the Startup parameters, Memory Usage, CPU Usage etc and save it in a separate file.
Install the service pack on SQL Servers.
Verify all the SQL Services are up and running as expected.
Validate the application functionality.
Note: There is a different approach to install Service pack on SQL Server cluster instances. That will be covered in SQL Server cluster.
20. Is it mandatory to restart the Windows server after installing SQL server service pack?
No, it’s not mandatory to restart Windows server after installing SQL Server service pack but it is always a good practice to do so.
21. How to check the SQL Server version and Service pack installed on the server?
select convert(varchar(50),SERVERPROPERTY(‘productversion’)) ,
convert(varchar(50),SERVERPROPERTY (‘productlevel’)) ,
convert(varchar(50),SERVERPROPERTY (‘edition’))
Or
select @@VERSION
22. How to check SQL Server name?
Select @@Servername
23. What is a slip stream installation and its usages?
SQL Server 2008 introduced a concept that’s called “Slipstream Installation”. This is a way to deploy a SQL Server instance with all the needed Service pack as part of the installation. Everything will be installed in one go, hence there is no need to deploy any other service packs on the installation.
There were various bugs which caused the failure of SQL Server 2008 and SQL Server 2008 R2 installations, so Microsoft has created some fixes to avoid the failures. But the setup media which was already released does not have these fixes, so a procedure called slip-stream was introduced where these fixes are merged with the main SQL Server setup media to avoid any known SQL Server setup failure issues. There are two ways of performing slip-stream, one is installing setup support files from SP1 or SP2 and then install the SQL Server 2008 R2, second method is to merge some of the setup files from SP1 or SP2 with the SQL Server 2008 or R2 media files and then running the install which will install SQL Server 2008 or SQL Server 2008 R2 plus the service pack 1 or Service Pack 2.
24. What is a silent installation and how can we use this feature?
The procedure to install SQL Server instance through command line using ConfigurationFile.ini file in Quite mode is known as Silent installation.
.25. What is the default port of a SQL Server instance?
SQL Server default instance by default listen on 1433 port.
26. Can we change the default port of SQL Server, How?
Yes, it is possible to change the Default port on which SQL Server is listening.
Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools >SQL Server Configuration Manager
Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration >Protocols for <Instance Name>
Step 3. Right Click on TCP/IP and select Properties
Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAllgroup. Now change the value to static value which you want to set for SQL Server port.
27. How to get the port number where the SQL Server instance is listening?
Below are the methods using which we can get the port information?
Method 1: SQL Server Configuration Manager
Method 2: Windows Event Viewer
Method 3: SQL Server Error Logs
Method 4: sys.dm_exec_connections DMV
Method 5: Reading registry using xp_instance_regread
28. What is a Filestream?
FILESTREAM was introduced in SQL Server 2008 for the storage and management of unstructured data. The FILESTREAM feature allows storing BLOB data (example: word documents, image files, music and videos etc) in the NT file system and ensures transactional consistency between the unstructured data stored in the NT file system and the structured data stored in the table.
29. What’s the location of SQL Server log files?
SQL Server error logs are stored in the below location.
Instance Root Directory\MSSQL\Log
30. How many SQL Server log files can be retained in the SQL Server error logs be default?
By default, there are seven SQL Server error logs; Errorlog and Errorlog.1 through Errorlog.6. The name of the current, most recent log is Errorlog with no extension. The log is re-created every time that you restart SQL Server. When the Errorlog file is re-created, the previous log is renamed to Errorlog.1, and the next previous log (Errorlog.1) is renamed to Errorlog.2, and so on. Errorlog.6 is deleted.
31. Is it possible to increase the retention of Error log files and How?
Yes it is possible to change the no. of Error logs retention. We can follow the below steps to change the Error log file retention.
Open SQL Server Management Studio and then connect to SQL Server Instance
InObject Explorer, ExpandManagement Node and then right click SQL Server Logs and click Configure as shown in the snippet below.
In Configure SQL Server Error Logs window you can enter the value between 6 and 99 for the number of error logs and click OK to save the changes
32. How to configure Errorlog?
Go to SSMS –> Object Explorer (F8) –> Management –> R.C on SQL Server Logs–> Configure
> Select checkbox “Limit the no of error log…..”
> Enter no of error logs: 30
> OK
Method-2 – From windows registry
Start–> Run –>regedit–> HKEY_LOCAL_MACHINE–>Software–>Microsoft
–> Microsoft SQL Server –> Select instance Folder –> MSSQLServer
33. Difference between Enterprise and Standard Editions?
34. Verifying instance folders
* For every instance a separate folder is created with the name
MSSQL10.<instanceName>\MSSQL
Backup
Default backup folder
DATA
Default location for data and T.Log files
Binn
Consists of exe and .dll files of that instance
FTData
Full Text Search files
Log
Consists of error logs
Job
Consists of Maintenance Plans
Install
Consists of installation scripts
repldata
Replication snapshot folder
Upgrade (2008)
Consists of upgradation scripts
35. How many instances are present in a machine?
Ans: 1. Using SSCM
– Check for SQL Server Services
2. Using Windows Registry
> Start –> run –> Regedit
> HKEY_LOCAL_MACHINE
> SOFTWARE
> MICROSOFT
> Microsoft SQL Server
> Instance Names
> SQL
36. How many files can a Database contain in SQL Server? How many types of data files exists in SQL Server? How many of those files can exist for a single database?
Answer: A Database can contain a maximum of 32,767 files.
There are Primarily 2 types of data files Primary data file and Secondary data file(s)
There can be only one Primary data file and multiple secondary data files as long as the total # of files is less than 32,767 files.
37. What are the things to be considered to install new SQL Server 2012 instance?
Note: Question can be asked related to different versions of SQL Server, but general steps will be applicable for during installation of all versions of SQL Server.
– Prepare documentation and implement the required Hardware and Software which includes Operating System, OS patches, features like clustering, .Net framework, etc.
– Study and document the SQL Server features which need to be implemented. Example, to use AlwaysON, need to have windows clustering feature enabled and other requirements need to be understood and documented.
– Study, document and implement all the pre-requisites required for the installation of SQL Server.
– Install SQL Server version.
– Apply latest Service Packs or Cumulative Updates or Security Updates.
– Check the Setup logs and Event logs and make sure there are no errors related to OS or SQL Server.
– Test and make sure you can connect to SQL Server from remote systems and all features which are installed are working properly.
38. How do you determine whether to apply latest Service Packs or Cumulative Updates?
Some of the common reasons for installing Service Packs or Cumulative Updates.
– If SQL Server is experiencing any known issue and it is found that the issue was fixed on a particular Service Pack or CU, then need to test the patch by applying on a test server and if application is working fine, then can go ahead and install the patches on production server.
– Security Updates are releases when some vulnerability is identified with the product, so need to apply these as soon as it is available.
– Service Packs can be applied as they are more safe than Cumulative updates. In general after a service pack is released, CU1 for that service pack will be released very soon, so good practice to apply a service pack as soon as it is available and then also install the CU1. Of-course, Service Pack should be first installed on Test server and application should be tested thoroughly to make sure it works without any problems.
– It is always good to be on latest build of SQL Server to avoid any known issues before they cause production issues. Quarterly patching of SQL Servers should be good.
39. How to apply service pack to SQL Server in cluster environment in SQL Server 2008 R2?
– First need to test applying the service pack on a test server to make sure that the application does not break after applying the service pack or cumulative update.
– On a two node cluster, make sure SQL Server instance and MSDTC and Cluster groups are all on one node of the cluster, which will become the active node.
– Perform backups of System and user databases.
– Remove the passive node from the SQL Server resource possible owners list.
– Install the service pack on the passive node.
– Add the passive node back to the SQL Server resource possible owners list.
– Failover SQL Server instance to node where we applied the Service Pack.
– Check Errorlogs to make sure upgrade scripts completed successfully and latest version is reflected in SSMS and SQL errorlog.
– Remove the new passive node from the SQL Server resource possible owners list.
– Install the service pack on the new passive node.
– Add the passive node back to the SQL Server resource possible owners list.
– Failover SQL Server instance to the newly upgraded node.
– Check Errorlogs to make sure upgrade scripts completed successfully and latest version is reflected in SSMS and SQL errorlog.
– Test the application.
Even skipping the steps of removing and adding the node name from possible owners from SQL Server resource properties, should be fine and is done that way by most of the DBA’s, but above is the recommended approach.
40. Can a Service Pack or Cumulative Update be uninstalled to rolled back in case of failures?
We cannot uninstall a service pack or Cumulative Update on SQL Server 2005 or lower, but starting with SQL Server 2008, we can uninstall a service pack or Cumulative Update from control panel add or remove programs – view installed updates window. To rollback a service pack or CU update on SQL Server 2005 or lower, we need to completely uninstall SQL Server and reinstall SQL Server 2005 to same build where it was before applying SP4, also need to have it installed with same collation as it was before. On SQL Server 2005 is installed and brought up to same build as it was before, replace the .mdf and .ldf files of all the system databases or be restoring the backups of all the system databases.
41. How do you install Service Packs or CU on SQL Server 2012 instances with AlwaysON Availability Group databases?
With AlwaysON Availability Group databases, we can install service packs or CUs with minimal downtime to the end users, but there can be impact if secondary replicas are used for reporting purposes. Below are the steps to install Service Packs or CU on SQL Server 2012 instances with AlwaysON Availability Group databases.
– Make sure that the AlwaysON Availability Group is running on one node, which will be the active node.
– Backup all the System and User databases.
– Install the service pack or CU on the secondary replica.
– Test the secondary replica, by checking Errorlogs and eventlogs to make sure there are no failures or errors.
– Failover AlwaysON Availability Group to secondary replica which will now become new primary replica.
– Backup all system databases.
– Install the service pack or CU on the new secondary replica.
– Test the new secondary replica, by checking Errorlogs and eventlogs to make sure there are no failures or errors.
– Failover AlwaysON Availability Group to the secondary server which will now become the primary server.
– Verify and Test the application.
42. What are the pre-requisites before installing a service pack or Cumulative Updates?
On critical servers, it is important to make sure to follow all the pre-requisites before installing service pack or Cumulative Updates, so that there are no issues after patching the critical production servers.
– Install the service pack or CU on test server with similar setup
– Check for any errors in SQL errorlogs or Eventlogs
– Test the application thoroughly to make sure it works without any issues.
– Document and Test the Rollback plan on test server to make sure that we can rollback successfully in case of any issues after applying the patches.
– Backup all System and User databases and verify that they can be restored.
– Install the service pack or CU on production servers.
– Checked SQL errorlog and eventlog and make sure there are no errors.
– Test the application thoroughly.
43. Where can I find the SQL Server Setup logs to troubleshoot any setup failures?
Setup logs can be found from C:\programfiles\Microsoft SQL Server\110\Setup Bootstrap\Log\Folder with time stamp with latest datetime. Change 110 to 100 for SQL Server 2008 R2, 90 for SQL Server 2005, 120 for SQL Server 2014.
There can be two summary files, one for main setup work flow and other for component update. There is file with name detail.txt which has all the informational, warning and error messages related to setup, this file mostly points to the exact exception or error which caused the setup failure.
Reviewing summary and details.txt should help in identifying where exactly the problem is.
44. What are ways of migrating SQL Server from lower version to higher version?
If you want to upgrade a SQL Server instance from SQL Server 2008 R2 to SQL Server 2012, below are the different ways you can do this migration.
1. In-Place Upgrade – In this method, existing instance of SQL Server will be upgraded to higher version, thus we end up with one instance of SQL Server with higher version i.e., SQL Server 2012. Here the instance name remains same, so application connection string remains the same, only change that may be required is to have latest connectivity drivers installed.
2. Side-By-Side Upgrade – In this method a new instance of SQL Server 2012 is installed on same server or a different server and them all User databases, Logins, Jobs, configuration settings need to be configured or created on the new SQL Server instance.
45. What are the differences between In-Place Upgrade and Side-By-Side Upgrade in SQL Server 2008 R2?
– In In-Place Upgrade, instance name does not change, so no need to change the connection string, but in side-by-side upgrade, instance name will be different if new instance is installed on same server, if installed on other server, then the server name will change and will result in requirement to change to the connection string.
– In-Place upgrade has risk or additional down time in case the upgrade fails which ends up with cleanup and reinstalling everything clean and during this entire process, there will be huge amount of downtime required. In side-by-side upgrade, we are installing a new instance or even on a new server, so any failures will not affect the existing SQL instance, which will continue to server the clients.
– Side-by-side migration has lot of addition tasks like backup and restore of user databases on new instance, create logins, fix orphan users, configure SQL Server settings appropriately, Create all the required jobs, etc. In-Place upgrade does not require much changes as everything will be migrated and readily available to use.
– Rollback of SQL Server instance in in-place method is not possible, but is fully possible in side-by-side upgrade.
– Amount of downtime is more with in-place upgrade compared to side-by-side upgrade when planned properly.
46. What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?
On SQL Server 2005, installing SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to install separately on all the nodes. 2 times if it is a 2 node cluster or 3 times in a 3 node cluster and so on…
60.What are database backups?
A Database backup is a copy of SQL Server data that can be used to restore and recover the data in case of any failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. There is another option to take Transaction Log backups when the database recovery model of a database is Full.
60.Types of Database backups?
We have below type of backup available in SQL Server 2012.
· Full Backup
· Differential Backup
· Transaction Log Backup
· Copy-Only Backup
· File or Filegroup Backup
· Split Backups
· Mirrored backup
· Tail log backup
62.What is Full Database backup?
A full backup is a backup of the entire database that contains all the data and log file records needed to recover the database to the point in time when backup completed. Full backup should be a part of backup strategy for all the business-critical databases.
Full database backup contains the complete set of data needed to restore and recover a database to a consistent state. It serves as a baseline for all other backups.
–Back up the AdventureWorks as full backup
BACKUP DATABASE AdventureWorks TO DISK = N’D:\AdventureWorks.bak’
63.What is Differential Backup?
Differential backup backups up only the data that has changed since the last full backup. A differential backup is not a stand-alone backup it needs a full backup to act as a baseline. For larger databases differential backups is common in order to save space and reduce the backup time.
In addition to being smaller and faster than full backup, a differential backup makes the restore process simpler. When you restore using differentials you must first restore the full backup followed by the most recent differential backup that was taken.
–Back up the AdventureWorks as differential backup
BACKUP DATABASE AdventureWorks TO DISK = N’c:\AdventureWorksDiff.bak’ WITH DIFFERENTIAL
64.What is Transaction Log Backup?
Log backups can be taken only if the recovery model of the database is Full recovery or Bulk-logged recovery. Simple recovery model does not allow transaction log backup because the log file is truncated automatically upon database checkpoints.
Log backups are taken between full backups to allow point-in-time recovery with the exception of log backups containing bulk-logged records. Without Transaction log backups you can restore data only till the time when the full or differential backup was taken.
–Back up the AdventureWorks transaction log
BACKUP LOG AdventureWorks TO DISK = N’c:\AdventureWorksLog.trn’
65.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. Another potential benefit of having filegroup backups is that if the disk on which a particular file resides fails and is replaced, just the file can be restored instead of the entire database.
BACKUP DATABASE AdventureWorks FILEGROUP=’PRIMARY’, FILEGROUP =’Secondary’
TO DISK =’D:\AdventureWorks_FileGroup.bak’
66.What is COPY ONLY Backup?
Copy-only backups are introduced in SQL Server 2005 and are used to create a full database or transaction log backup without breaking the log chain. A copy-only full backup can’t be used as a basis for a differential backup, nor can you create a differential copy only backup.
–Back up the AdventureWorks database as copy only
BACKUP DATABASE AdventureWorks TO DISK = N’c:\AdventureWorks.bak’ WITH COPY_ONLY
–Back up the AdventureWorks transaction log as copy only
BACKUP LOG AdventureWorks TO DISK = N’c:\AdventureWorksLog.trn’ WITH COPY_ONLY
67.What are Split Backups?
SQL Server have one more feature to database backups can split to multiple files. Using this way SQL Server run the multiple thread of database backups for each files and can be completed faster comparatively with less time and IO.
BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\AdventureWorks_1.bak’
DISK = ‘D:\AdventureWorks_2.bak’,
DISK = ‘E:\AdventureWorks_3.bak’
GO
68.What is Mirrored backup?
Mirrored database backups can be used to create multiple copies of the database backups on different locations.
BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\AdventureWorks.bak’
MIRROR TO DISK = ‘D:\AdventureWorks_mirror.bak’
GO
69.What is Tail log backup?
A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.
Tail log backup is taken in below ways:
If the database is online follow below syntax:
BACKUP LOG [database name] TO [backup device] WITH NORECOVERY
If the database is offline (example a corrupted database which does not start]
BACKUP LOG [database name] TO [backup device] WITH CONTINUE_AFTER_ERROR
70.What is Native Backup Compression?
Database backup compression helps in creating a database backup in a compressed format (Supported SQL Server 2008 onwards based on the Edition). Enabling database backups to use compression can be done in one of two ways. You can either configure all backups for the instance to be compressed (it is disabled by default), or you can issue the WITH COMPRESSION command for a specific database backup.
71.How can you enable Database compression on all the native backups?
Backup compression can be enabled at the SQL Server instance level as below.
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
72. Is it possible to add password to a backup file in SQL Server 2012 version?
WITH password option is not available any more with SQL Server 2012 onwards.
73.In which recovery model, Transaction Log backups are possible?
Transaction Log backups are possible in Full and Bulk Logged recovery model.
74.What all operations are minimally logged when the database is in Bulk Logged Recovery mode?
· Bulk import operations (bcp, BULK INSERT, and INSERT… SELECT). For more information about when bulk import into a table is minimally logged.
· SELECT INTO operations.
· Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data. Note that minimal logging is not used when existing values are updated.
· WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns. Note that minimal logging is not used when existing values are updated.
· CREATE INDEX operations (including indexed views).
· ALTER INDEX REBUILD or DBCC DBREINDEX operations.
· DROP INDEX new heap rebuild (if applicable).
75.How do you know if your database backups are restorable ?
We can use RESTORE VERIFY ONLY command to make sure that the Database backups are restorable.
76. What is the database that has the backup and restores system tables?
msdb database contains information about the backup restore.
77.What are the backup and restore system tables? What do each of the tables do?
Here are the backup and restore system tables and their purpose:
· backupfile – contains one row for each data file or log file backed up
· backupmediafamily – contains one row for each media family
· backupmediaset – contains one row for each backup media set
· backupset – contains one row for each backup set
· restorefile – contains one row for each restored file
· restorefilegroup – contains one row for each restored filegroup
· restorehistory – contains one row for each restore operation
78.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.
79.In a situation with full, differential and transaction log backups being issued for a database, how can an out of sequence full backup be issued without interrupting the LSN’s?
Backup with COPY ONLY option can be used in such a situation.
80.How can I verify that backups are occurring on a daily basis?
We can verify the backup history of the database that backups are happening or not.
backupset table in msdb
81.What is the meaning of the values in Type column in backupset table.
This column tells us about the backup type.
Backup type. Can be:
· D = Database
· I = Differential database
· L = Log
· F = File or filegroup
· G =Differential file
· P = Partial
· Q = Differential partial
82.What are the permissions required to perform backup?
The user must be a member of either of the below roles
Backup:
· sysadmin – fixed server role
· db_owner – fixed database role
· db_backupoperator – fixed database role
83.Is there any option to prevent All successful SQL Server backup entries from writing to the SQL Server Error Log?
Yes – We can enable the trace flag 3226.
84.Assume that we have to take a backup of the database with a backup size of 90 GB. There is no space available in a single disk drive instead there are 4 different drives where we have 25 GB free space on each drive. How can you perform the backup to three different drives?
We can take backup in split backups.
BACKUP DATABASE AdventureWorks
TO DISK = ‘D:\Backup\AdventureWorks1.bak’,
DISK = ‘E:\Backup\AdventureWorks2.bak’,
DISK = ‘F:\Backup\AdventureWorks3.bak’,
DISK = ‘G:\Backup\AdventureWorks4.bak’
26) Explain the below Backup script?
USE master
GO
BACKUP DATABASE [Test] TO
DISK = N’D:\ Backups\ test_full_native_1.bak’
WITH FORMAT, INIT,
NAME = N’test- Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
· FORMAT – This 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 (NOFORMAT will preserve it).
· INIT – By default, when scripting a backup generated by the Backup wizard, this parameter will be set to NOINIT, which lets SQL Server know not to initialize a media set when taking the backup and instead append any new backup data to the existing backup set. However, since we adopt the rule of one backup per backup set, it’s useful to use INIT instead, to make sure that, if a command gets run twice, we overwrite the existing set and still end up with only one backup in the set.
· NAME – The NAME parameter is simply used to identify the backup set. If it is not supplied, the set will not record a name.
· SKIP – 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.
· NOREWIND – This parameter will cause SQL Server to keep a tape device open and ready for use when the backup operation is complete. This is a performance boost to users of tape drives since the tape is already at the next writing point instead of having to search for the correct position. This is obviously a tape-only option.
· NOUNLOAD – When backing up to a tape drive, this parameter instructs SQL Server not to unload the tape from the drive when the backup operation is completed.
85. Can we have multiple copies of the database backup is a single file?
Yes we can save multiple copies of database backup in a single file.
86. Name any 2-3 Third party SQL Server database backup tools?
There are many tools available in the market for SQL server backups like
· SQL Litespeed (Dell)
· SQL Backup Pro (Redgate)
· SQL Safe Backup (Idera)
87. 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.
88. What are the common issues you faced in Database backup?
There could be multiple reasons like:
· Permissions issues if the backups are configured to be taken on a share location
· Backup file used by the tape backups due to which backup process is not able to overwrite the backup file.
· Full backup is not taken before initiating a Diff. of Transaction log backup
· Not enough space available on the target location
89. What is 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.
90.What is 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…
91. Whether Full or Differential backups clear the Transaction Log or not?
No, Full or Differential backup do not clear Transaction logs.
92. 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.
93.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.
94.What are the options to deal with Over Growing transaction log file?
We have below options to deal with the over growing transaction log file:
Freeing disk space so that the log can automatically grow.
· Backing up the log.
· Adding a log file on a separate disk drive.
· Increasing the size of a log file
· killing a long-running transaction
95.How does the database recovery model impact database backups?
Database recovery model deals with the retention of the transaction log entries. Database recovery model decides if transaction log backups need to be triggered on a regular basis in order to keep the transaction log small or the Transaction logs will be truncated automatically.
· Simple – Committed transactions are removed from the log when the check point process occurs.
· Bulk Logged – Committed transactions are only removed when the transaction log backup process occurs.
· Full – Committed transactions are only removed when the transaction log backup process occurs.
96.Suppose I have a Database maintenance plan which runs every 15 minutes to take the Transaction Logs backup of all user defined databases. One of the members of DBA team created a new database in the morning at 09:10 AM and the DB maintenance job started failing. What could be the reason?
This job is failing because we did not take a full database backup of the newly created database. We need to a full backup of a database to initiate the log chain.
97.What is the below error related to Differential backup?
Msg 3035, Level 16, State 1, Line 1Cannot perform a differential backup for database “backup_test”, because a current database backup does not exist.
Differential Backup is failing because we did not take a full backup of the database after creation of the database or switching the Recovery model of the database.
98. How will check the content of a backup file?
RESTORE HEADERONLY
FROM DISK = N’C:\AdventureWorks-FullBackup.bak’
GO
99.What is the Backup strategy in your environment?
It’s very open ended Question:
As per my understanding, normally Organizations follow below standards
Prod/QA Environment:
For Small size databases (<200 GB)
· Full Backup – Daily
· Differential Backups – NA
· Transaction Log backups – Every 10-15 minutes depending upon the RPO
For Large size databases (>=200 GB)
· Full Backup – Weekly
· Differential Backups – Daily
· Transaction Log backups – Every 10-15 minutes depending upon the RPO
Again it all depends upon the criticality of the database e.g. for Data warehousing databases it may be possible that there is no requirement of Transaction log backups.
100.From a best practices perspective, what is your backup retention policy?
Again it all varies on organization’s policies. As a general practice it’s always better to keep the database backups on the shared location (In the Data Center) at least for 2-3 days. Daily backups should be written to tape backups and should be retained for a month or so. Also as a preventive measure there should be monthly or bi monthly backups with a retention policy of minimum one year.
102.What is the below error related to Permission issue for database backup?
Msg 3201, Level 16, State 1, line 1
Cannot open backup device ‘\\*****\*******\master.bak’.
Operating system error 53(The network path was not found.).
This error is due the insufficient permissions of Service Account on the Shared location.
103. What are the phases of Database restore process?
A restore is a multiphase process. The possible phases of a restore include the data copy, redo (roll forward), and undo (roll back) phases:
· The data copy phase involves copying all the data, log, and index pages from the backup media of a database to the database files.
· The redo phase applies the logged transactions to the data copied from the backup to roll forward that data to the recovery point. At this point, a database typically has uncommitted transactions and is in an unusable state. In that case, an undo phase is required as part of recovering the database.
· The undo phase, which is the first part of recovery, rolls back any uncommitted transactions and makes the database available to users. After the roll back phase, subsequent backups cannot be restored.
104.What is a simple Database restore command from Full Backup?
RESTORE DATABASE [Restore_test]
FROM DISK = N’H:\MSSQL\Backup\Restore_test.bak’
WITH FILE = 1,
MOVE N’Restore_test_data’ TO N’H:\MSSQL\Data\Restore_dat1.mdf’,
MOVE N’Restore_test_log’ TO N’H:\MSSQL\Log\Restore_log1.ldf’,
REPLACE, STATS = 10
GO
105.What is a simple Database restore command from Full & Differential Backup?
RESTORE DATABASE [Restore_test]
FROM DISK = N’H:\MSSQL\Backup\Restore_test.bak’
WITH FILE = 1,
MOVE N’Restore_test_data’ TO N’H:\MSSQL\Data\Restore_dat1.mdf’,
MOVE N’Restore_test_log’ TO N’H:\MSSQL\Log\Restore_log1.ldf’,
REPLACE, STATS = 10, NORECOVERY
GO
RESTORE DATABASE [Restore_test]
FROM DISK = N’H:\MSSQL\Backup\Restore_test_Differential.bak’
WITH FILE = 1,
MOVE N’Restore_test_data’ TO N’H:\MSSQL\Data\Restore_dat1.mdf’,
MOVE N’Restore_test_log’ TO N’H:\MSSQL\Log\Restore_log1.ldf’,
REPLACE, STATS = 10, RECOVERY
GO
106.What is the Difference between WITH RECOVERY and WITH NORECOVERY parameter?
· RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Select this option if you are restoring all of the necessary backups now.
· RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database cannot be used until it is recovered.
107.What is WITH STANDBY Mode?
This option leaves the database in read-only mode. It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.
108.Why there is a requirement to restore the database?
Requirement of Database restore:
· In case of Database corruption, data issue or Disaster recovery
· In case of Database refresh from production to QA and Dev environment
· In case of upgrade from lower version to upper version in side by side upgrade
109.What are the permissions required to perform Database Restore?
The user must be a member of either of the below roles
· Sysadmin – fixed server role
· Dbcreator – fixed server role
· db_owner – fixed database role
110.Is it possible to restore a Database backup of SQL Server 2012 to SQL Server 2008 /2008 R2?
No it’s not possible to restore the upper version database backup to lower version.
111. What is RESTORE FILELISTONLY option?
This SQL returns a result set containing a list of the database and log files contained in the backup set in SQL Server.
RESTORE FILELISTONLY FROM AdventureWorksBackups
WITH FILE=1;
GO
112.What is Point in Time recovery?
Point in Time Recovery option gives us the ability to restore a database prior to an event that occurred that was detrimental to your database. In order for this option to work, the database needs to be either in the FULL or Bulk-Logged recovery model and you need to be doing transaction log backups.
High level steps to perform a point in time recovery.
Take a tail log backup of the database which needs to be restored
Restore the most recent full backup with the NORECOVERY clause
Restore the most recent differential backup with the NORECOVERY clause
Restore all of the subsequent transaction log backups with the NORECOVERY clause except the last tail log backup
Restore the last tail log backup with the RECOVERY clause and a STOPAT statement if the entire transaction log does not need to be applied
113.What is piecemeal Restore?
A piecemeal restore sequence restores and recovers a database in stages at the filegroup level, beginning with the primary and all read-write, secondary filegroups. Piecemeal restore process allows us to restore the primary filegroup first and the database can be online and the remaining filegroups can be restored while the recovery the transactions are running on primary. Mostly suitable for data warehousing databases.
114.What are Partial Backups in SQL Server?
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. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup.
115.What is the command to take a Partial backup?
BACKUP DATABASE DBPartial READ_WRITE_FILEGROUPS
TO DISK = N’C:\SQLBackups\DBPartial_PARTIAL_Full.bak’
GO
Backup scenario:
116.What are the options to restore the database till Point 8 i.e. P8?
· Option 1: F1 > D2 > T5
· Option 2: F1 > D1 > T3 > T4 > T5
· Option 3: F1 > T1 > T2 > T3 > T4 > T5
117.What are the options to restore the database till Point 10 i.e. P10?
· Option 1: F2 > T6
· Option 2: F1 > D2 > T5 > T6
· Option 3: F1 > D1 > T3 > T4 > T5 > T6
· Option 4: F1 > T1 > T2 > T3 > T4 > T5 > T6
118.What are the options to do a point in time recovery P13?
· Option 1: F2 > D3 >T8 with STOPAT Time stamp of P13
· Option 2: F2 > T6 > T7 > T8 with STOPAT Time stamp of P13
· Option 3: F1 > D2 > T5 > T6 > T7 > T8 with STOPAT Time stamp of P13
· Option 4: F1 > D1 > T3 > T4 > T5 > T6 > T7 > T8 with STOPAT Time stamp of P13
· Option 5: F1 > T1 > T2 > T3 > T4 > T5 > T6 > T7 > T8 with STOPAT Time stamp of P13
119.How can we kill or take an exclusive access of the database?
ALTER DATABASE [adb] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [adb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Or Write a cursor or loop to kill the DB connections
DECLARE @cmdKill VARCHAR(50)
DECLARE killCursor CURSOR FOR
SELECT ‘KILL ‘ + Convert(VARCHAR(5), p.spid)
FROM master.dbo.sysprocesses AS p
WHERE p.dbid = db_id(‘MyDB’)
OPEN killCursor
FETCH killCursor INTO @cmdKill
WHILE 0 = @@fetch_status
BEGIN
EXECUTE (@cmdKill)
FETCH killCursor INTO @cmdKill
END
CLOSE killCursor
DEALLOCATE killCursor
120.Why Database restores from upper version to lower version is not allowed?
Database servers get changed with service packs and new releases. New object types get added and the lower versions cannot understand these object types.
In order to avoid such conflicts and problems – Higher end database restorations cannot be performed directly on lower end database servers.
121.Is there any alternate method of restoring the database from Upper version to lower version?
There is no proper method of restore the database from upper version to lower version. However we can use below techniques to perform this task:
· Script out the database Objects and create these on the target database
· Use SQL Server Import Export Wizard to copy data from source server to destination server (only Data)
· Copy data to destination tables using BCP (only Data)
122.Is it possible to attach the Data and log files of upper version to lower version SQL Server instance?
No, It is not possible.
123. Is it possible to mark Primary File Group as Read only?
No it’s not possible to make Primary File Group read only.
124.How to make the File Group read only?
Filegroups can be marked as read-only. Any existing filegroup, except the primary filegroup, can be marked as read-only. A filegroup marked read-only cannot be modified in any way. Read-only filegroups can be compressed.
ALTER DATABASE ReadFilegroup MODIFY FILEGROUP Test1FG1 Read_Only;
125.Is it possible to perform a point in time recovery with Bulk Logged recovery model?
Yes, it is possible to perform a point in time recovery with Bulk logged recovery model till the time we don’t perform any minimal logged operation on the database.
126.How to recover a database that is in the “restoring” state?
RESTORE DATABASE AdventureWorks WITH RECOVERY
GO
127.How to change the database owner of the database?
EXEC sp_changedbowner ‘Myuser’;
128. What is High Availability in SQL Server?
A high availability solution masks the effects of a hardware or software failure and maintains the availability of applications so that the perceived downtime for users is minimized. SQL Server high availability solutions improve the availability of servers or databases.
129.What all High Availability/DR options are available in SQL Server?
At a high level, there are five main high availability options including a new feature set to be release with SQL Server 2012:
• Log Shipping
. Mirroring
• Replication
. Clustering
• Always ON
130.What is Log Shipping?
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all. The key feature of log shipping is that it will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers Databases in “synch”
131.What are the Benefits of Log Shipping?
. Once log shipping has been implemented, it is relatively easy to maintain.
. Assuming you have implemented log shipping correctly, it is very reliable.
. Log shipping doesn’t require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement.
. The manual failover process is generally very short, typically 15 minutes or less.
. Implementing log shipping is not technically difficult
. Load balancing
132.What the drawbacks of Log Shipping?
. Log shipping failover is not automatic. The DBA must still manually failover the server, which means the DBA must be present when the failover occurs
. The users will experience some downtime How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over
. Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable
. The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally.
. When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user’s applications to the new standby server. In some cases, neither of these options is practical
132.In which recovery model Log Shipping can be configured?
. Log Shipping works with Full and Bulk Logged recovery model.
133.Is it possible to configure Log Shipping from lower version to upper version and Vice-versa ?
. Yes it is possible to configure Log Shipping from lower to upper version. But it is not possible vice-versa
134.What is Log Shipping Monitor Settings?
. This setting enables us to setup a monitor on the Log shipping through which we can monitor the log. shipping process.
135.What all jobs are created after configuring the Log Shipping?
Internally when Log Shipping is configured, there are 4 jobs created between Primary Server and Secondary Server, they are Backup Job, Copy Job, Restore Job and Alert Job
. Backup job. This job is created on Primary Server, this job takes the transaction log backup of the Database on a scheduled time)
. Copy Job: This job is created on Secondary Server, this job Copies the transaction log Backup from Primary Server to the Standby/Secondary Server.
. Restore Job This job is created on Secondary Server, this job restored the copied transaction log backup on the Secondary Server.
136. What permissions are required for shared folders on Primary and secondary for the service accounts?
. For the backup job, read/write permissions to the backup directory are required to the following
. For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of this copy job. By default, this is the SQL Server Agent account on the secondary server instance.
. For the restore job, read/write permission to the copy directory are required by the following: The SQL Server service account on the secondary server instance. The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance
137.What is the copy Job in Log shipping and on which instance it’s created?
. Created in secondary server for every log shipping configuration. Copy the backup files from backup folder into copy folder.
. It deletes old files and old history from copy folder.
. On backup folder we have to grant read permission to secondary server account and read . write permissions on copy folder.
138.What is Monitor server?
. An optional instance of SQL Server that tracks all of the details of log shipping, including, When the transaction log on the primary database was last backed up
. When the secondary servers last copied and restored the backup files.
. Information about any backup failure alerts.
139.What are Log Shipping System Tables and in which database these are stored?
. SQL Server Log Shipping System Tables
. log shipping monitor primary-Stores one monitor record per primary database in each log shipping configuration. I
. log shipping monitor secondary-Stores one monitor record per secondary database in a log shipping configuration.
. log shipping primary databases – Stores one record for the primary database in a log shipping configuration
. log shipping secondary- Stores one record per secondary ID
140. What are Log Shipping System Store Procedures?
. SQL Server will issue a series of steps to synchronize the information between primary/secondary server and the monitor server. This can be implemented by running the below undocumented log. shipping stored procedures.
sp_processlogshippingmonitorprimary
Sp_processlogshippingmonitorsecondary
141. is it possible load balance in Log Shipping?
. Yes, it’s possible in log shipping, while configuring log shipping we have the option to choose standby or no recovery mode, there we select STANDBY option to make the secondary database readonly
142.What is STANDBY Mode on the secondary database?
. I We can reduce the load on our primary database by using a secondary server for read-only query processing To do this, the secondary database must be in STANDBY model
143.What’s the difference between the secondary being in “Restoring” vs. “Standby”?
. There are two options for configuration when we place the secondary database in standby mode time the log shipping restore job attempts to restore a transaction log to the secondary database. Disconnection will happen on the schedule you set for the restore job.
. We can choose not to disconnect users. In this case, the restore job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database.
144.What is TUF file?
. TUF stands for Transaction Undo file.
145.What will happen to Log Shipping if TUF file is corrupted or lost?
. The log shipping will not work. We have to setup the Log Shipping again
146.If you create a Job on the Primary database server, will it automatically be created on the secondary server or not?
. No, it will not be created on the secondary server
147.If you create a user on the Primary database, will it automatically be created on the secondary or not?
. Yes, it will be created automatically on the secondary database.
148.If you add a file on the Primary database in the same location which exists on the target, will it automatically be created on the secondary or not?
. Yes, it will be created automatically on the Secondary database if the file is added to the Primary database
149. If you add a file on the Primary database in a location which does not exist on the target, will it automatically be created on the secondary or not?
. No, Log Shipping will hang We have to manually restore the Log backup with MOVE option on the secondary database to rectify the issue
150.Is it possible to configure Log shipping on the database server with different collation?
No.
151.Can we configure Log Shipping between the different domains?
Yes, we can configure Log Shipping on the server residing in different domains.
152.What are the store procedures to monitor Log Shipping?
. We can execute the below Log Shipping System Stored Procedure to monitor log shipping and get detailed information about log shipping.
sp_help_log_shipping_monitor – This is the how SQL Server generates the Log Shipping Status report by executing
sys. sp_help_log_shipping_monitor – This procedure returns the log shipping status (whether it is healthy or not) as well as metadata such as primary and secondary database names, time since last backup, last backup file, last restore file, etc
sp_help_log_shipping_ monitor_primary. returns all columns from the log_shipping_monitor_ primary table for the specified primary log shipping database. It returns server name, database name, time of last backup, backup threshold, threshold alert and history retention period.
sp_help_log_shipping_monitor_ secondary-returns all columns from log_shipping_monitor_secondary table for the specified secondary log shipping database. It will return database name, server name, restore threshold, last copied file, time of last copy/ restore and history retention period.
153.Can we setup multiple secondary databases in Log Shipping?
. Yes, we can setup multiple secondary databases in Log Shipping.
154.Can we shrink log shipped database log file?
. Yes, we can shrink the log file, but we shouldn’t use WITH TRUNCATE option. If we use this option obviously log shipping will be disturbed.
155. Can we take full backup of the log shipped database in primary server?
. Yes it’s possible. We can take full backup of log shipped database and this won’t affect the log shipping.
156.What editions of SQL Server is log shipping available in?
. 2012-Enterprise, Business Intelligence, Standard, and Web
• 2008R2 – Datacenter, Enterprise, Standard, Web, and Workgroup • 2008-Enterprise, Standard, Web, and Workgroup
. 2005-Enterprise, Standard, and Workgroup
157. Can we take full backup of the log shipped database in secondary server?
. No, we won’t be able to execute BACKUP command against a log shipped database in secondary server
158.What is Database Mirroring?
Database mirroring was introduced with Microsoft SQL Server 2005 technology that can be used to design high-availability and high-performance solutions for database redundancy.
In database mirroring, transaction log records are sent directly from the principal database to the mirror database. This helps to keep the mirror database up to date with the principal database, with no loss of committed data. If the principal server fails, the mirror server automatically becomes the new principal server and recovers the principal database using a witness server under high-availability mode.
159.How does Database Mirroring works?
In database mirroring, the transaction log records for a database are directly transferred from one server to another, thereby maintaining a hot standby server. As the principal server writes the database’s log buffer to disk, it simultaneously sends that block of log records to the mirror instance. The mirror server continuously applies the log records to its copy of the database. Mirroring is implemented on a per-database basis, and the scope of protection that it provides is restricted to a single-user database. Database mirroring works only with databases that use the full recovery model.
160.What are the benefits of that Database Mirroring?
· Database mirroring architecture is more robust and efficient than Database Log Shipping. It can be configured to replicate the changes synchronously to minimized data loss.
· It has automatic server failover mechanism.
· Configuration is simpler than log shipping and replication, and has built-in network encryption support (AES algorithm).
· Because propagation can be done asynchronously, it requires less bandwidth than synchronous method (e.g. host-based replication, clustering) and is not limited by geographical distance with current technology.
· Database mirroring supports full-text catalogs.
· Does not require special hardware (such as shared storage, heart-beat connection) and cluster ware, thus potentially has lower infrastructure cost
162.What are the Disadvantages of Database Mirroring?
· Potential data lost is possible in asynchronous operation mode.
· It only works at database level and not at server level. It only propagates changes at database level, no server level objects, such as logins and fixed server role membership, can be propagated.
· Automatic server failover may not be suitable for application using multiple databases.
163.What are the minimum requirements for Database Mirroring?
· Database base recovery model should be full
· Database name should be same on both SQL Server instances
· Server should be in the same domain name
· Mirror database should be initialized with principle server
164.What are the Restrictions for Database Mirroring?
· A mirrored database cannot be renamed during a database mirroring session.
· Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb, or model databases.
· On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance.
165.What is a Principal server?
Principal server is the server which serves the databases requests to the Application.
166.What is a Mirror?
This is the Hot standby server which has a copy of the database.
167.What is a Witness Server?
This is an optional server. If the Principal server goes down then Witness server controls the fail over process.
168.What is Synchronous and Asynchronous mode of Database Mirroring?
In synchronous mode, committed transactions are guaranteed to be recorded on the mirror server. Should a failure occur on the primary server, no committed transactions are lost when the mirror server takes over. Using synchronous mode provides transaction safety because the operational servers are in a synchronized state, and changes sent to the mirror must be acknowledged before the primary can proceed
In asynchronous mode, committed transactions are not guaranteed to be recorded on the mirror server. In this mode, the primary server sends transaction log pages to the mirror when a transaction is committed. It does not wait for an acknowledgement from the mirror before replying to the application that the COMMIT has completed. Should a failure occur on the primary server, it is possible that some committed transactions may be lost when the mirror server takes over.
169.What are the operating modes of Database Mirroring?
SQL Server provides 3 operating modes for database mirroring.
· High Availability Mode ( High safety with automatic failover)
· High Protection Mode ( High Safety without automatic failover)
· High Performance Mode
170.What is High Availability operating mode?
It consist of the Principal, Witness and Mirror in synchronous communication. In this mode SQL server ensures that each transaction that is committed on the Principal is also committed in the Mirror prior to continuing with next transactional operation in the principal. The cost of this configuration is high as Witness is required. If the network does not have the bandwidth, a bottleneck could form causing performance issue in the Principal. If Principal is lost Mirror can automatically take over.
171.What is High Protection operating mode?
It is pretty similar to High Availability mode except that Witness is not available, as a result failover is manual. It also has transactional safety FULL i.e. synchronous communication between principal and mirror. Even in this mode if the network is poor it might cause performance bottleneck.
172.What is High Performance operating mode?
It consists of only the Principal and the Mirror in asynchronous communication. Since the safety is OFF, automatic failover is not possible, because of possible data loss; therefore, a witness server is not recommended to be configured for this scenario. Manual failover is not enabled for the High Performance mode. The only type of failover allowed is forced service failover, which is also a manual operation.
173.What are Recovery models support Database Mirroring?
Database Mirroring is supported with Full Recovery model.
174.What are End Points and its usages?
An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network.
175.How can we create an end point using SQL script?
CREATE ENDPOINT Endpoint1
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
176.What is the default of end points (port numbers) of principal, mirror and witness servers?
The default port numbers of principal, mirror and Witness servers are 5022, 5023 and 5024
177.What is Log Hardening?
Log hardening is the process of writing the log buffer to the transaction log on disk, a process called.
178.Is it possible to perform read only operation at mirrored database in mirror server?
Yes, using database snapshots.
179.What is Role-switching?
Inter changing of roles like principal and mirror are called role switching.
180.How to Set a Witness Server to Database Mirroring?
ALTER DATABASE AdventureWorks SET WITNESS = ‘TCP://SQLWITN.local:5024’
181.How to Remove a Witness Server from Database Mirroring?
ALTER DATABASE AdventureWorks SET WITNESS OFF
182.What are the Database Mirroring states?
· SYNCHRONIZING
· SYNCHRONIZED
· SUSPENDED
· PENDING_FAILOVER
· DISCONNECTED
183.What does SYNCHRONIZING state means in Database Mirroring?
The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward. At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.
184.What does SYNCHRONIZED state means in Database Mirroring?
When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.
If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.
If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.
185.What does SUSPENDED state means in Database Mirroring?
The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover. A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session. SUSPENDED is a persistent state that survives partner shutdowns and startups.
186.What does PENDING_FAILOVER state means in Database Mirroring?
This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.
When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
187.What does DISCONNECTED state means in Database Mirroring?
The partner has lost communication with the other partner
188.Why we get the below error message while configuring database mirroring?
Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring
We 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.
189.Can we configure a single database to be mirrored to more than one server. i.e) One source & many destination like log shipping?
No, It is not possible.
190. How do you monitor resource usages?
· Resource usages can be monitored using Task Manager, Perfmon and using sys.dm_exec_query_stats and sys.dm_exec_sql_text
· Check SQL Server error logs and event logs for any errors.
191.How do you check SQL Server error logs and Event Logs
· SQL Server logs can be viewed using xp_readerrorlogs. SQL Server management studio also provides Log file viewer tool.
· Check for any blocking or heavy locking or high number of suspended sessions.
192.How do you check blocking, locking or suspended connections?
· By running SP_WHO2, SP_WHO, select * from sys.sysprocesses and Activity Monitor can be used to view blocking.
· Check waits stats to see the top waits.
193.how do you check Wait stats?
· Select * from sys.dm_os_wait_stats gives information about wait stats.
194.How can you check stats information?
· DBCC SHOW_STATISTICS (‘Person.Address’, AK_Address_rowguid) can be used to view Stats information about individual index on a table.
195.How can you update stats on all tables in one go?
Use MyDatabase
Go
Exec sp_MSForEachtable ‘UPDATE STATISTICS ? WITH FULLSCAN’
GO
196.What is difference between Reorganizing and Rebuilding index?
· Index Rebuild: This process drops the existing Index and Recreates the index.
· Index Reorganize: This process physically reorganizes the leaf nodes of the index.
197.What is the key parameter for decision making about Rebuilding or Reorganizing index?
· Its Fragmentation level
198.How do you check the fragmentation level?
· DMF sys.dm_db_index_physical_stats can be used to view fragmentation level of an index.
199.What are missing indexes and how can they be identified?
· When you run a SQL query, SQL Server determines what indexes it would like to use, if these are not available, it makes a note of them. You can see details of these missing indexes by using DMVs.
200.What are unused indexes and how can they be identified?
· Unused indexes are those indexes that exist, but are not being used and therefore can be dropped to improve performance and decrease storage requirements.
201. How can you check last patching activity status of SQL Server or Operating System?
· Open Windows Update by clicking the Start button. In the search box, type Update, and then, in the list of results, click Windows Update. In the left pane, click View update history. This shows the latest patch that is applied with other information like dates and KB Number.
202. If major bulk data deletion/insertion activity happened last night, how will this activity hit performance?
· All DML operations (INSERT, UPDATE, and DELETE) can cause index fragmentation.
203.How do you troubleshoot slowness with a specific Stored Procedure or a Query?
· First, get more details like, how much time on an average this query was taking previously (baseline)?
· Were there any changes to the stored procedure or query recently?
· How often this query does runs?
· Does the problem only happen on this server or other servers too, meaning can this be reproduced on Test or Dev servers?
· Check if this query is being blocked by other sessions.
· Check if this query is waiting some any resource using wait stats DMV’s.
· Check if statistics are up to date for the tables and indexes used in the Stored procedure or the query.
· Check fragmentation of the objects in the stored procedure or the query.
· Collect execution plan of the Stored Procedure and the statements inside the SP or the query. Also collect Read/Write and Time execution statistics of the query.
· Check for any missing indexes based on the execution plan, based on table or clustered index scans.
· Try and suggest to developers if the query needs to be rewritten in a better way like replacing temp variables with tempdb tables or usage of functions in where clause.
204.What is Isolation level in SQL Server?
Isolation level defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions.
205.How many types of Isolation levels are available in SQL Server?
· READ COMMITTED
· READ UNCOMMITTED
· REPEATABLE READ
· SERIALIZABLE
· SNAPSHOT
206.What is READ COMMITTED Isolation Level?
A query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. However, data can still be modified by other transactions between issuing statements within the current transaction, so nonrepeatable reads and phantom reads are still possible. The isolation level uses shared locking or row versioning to prevent dirty reads, depending on whether theREAD_COMMITTED_SNAPSHOT database option is enabled. Read Committed is the default isolation level for all SQL Server databases.
207.What is READ UNCOMMITTED Isolation Level?
A query in the current transaction can read data modified within another transaction but not yet committed. The database engine does not issue shared locks when Read Uncommitted is specified, making this the least restrictive of the isolation levels. As a result, it’s possible that a statement will read rows that have been inserted, updated or deleted, but never committed to the database, a condition known as dirty reads. It’s also possible for data to be modified by another transaction between issuing statements within the current transaction, resulting in nonrepeatable reads orphantom reads.
208.What is REPEATABLE READ Isolation Level?
A query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. In addition, no other transactions can modify data being read by the current transaction until it completes, eliminating non repeatable reads. However, if another transaction inserts new rows that match the search condition in the current transaction, in between the current transaction accessing the same data twice, phantom rows can appear in the second read.
209.What is SERIALZABLE Isolation Level?
A query in the current transaction cannot read data modified by another transaction that has not yet committed. No other transaction can modify data being read by the current transaction until it completes, and no other transaction can insert new rows that would match the search condition in the current transaction until it completes. As a result, the Serializable isolation level prevents dirty reads, non repeatable reads, and phantom reads. However, it can have the biggest impact on performance, compared to the other isolation levels.
210.What is SNAPSHOT Isolation Level?
A statement can use data only if it will be in a consistent state throughout the transaction. If another transaction modifies data after the start of the current transaction, the data is not visible to the current transaction. The current transaction works with a snapshot of the data as it existed at the beginning of that transaction. Snapshot transactions do not request locks when reading data, nor do they block other transactions from writing data. In addition, other transactions writing data do not block the current transaction for reading data. As with the Serializable isolation level, the Snapshot level prevents dirty reads, nonrepeatable reads and phantom reads.
212.What is the default Isolation level of SQL Server databases?
Read committed (Database Engine default level)
213. How can we change the Isolation level of a database?
USE AdventureWorks2008R2;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
214. What is a lock?
SQL Server uses locks as a mechanism to bring consistency. At the same time build the concurrency for the system. Locks are inevitable in any database world and those are also fundamental building blocks.
215. What are the different types of lock modes in SQL Serve?
· Shared locks (S)
· Exclusive locks (X)
· Intent lock
· Schema lock
· Bulk update locks (BU)
· KEY RANGE Lock
216. What is SHARED lock?
Shared locks (S): Used when performing read-only operations against database. Resources locked with a shared lock are available for SELECT, but not for modification.
217. What is EXCLUSIVE lock?
Exclusive locks (X): Used for operations that modifies data. INSERT, UPDATE, and DELETE require exclusive locks. No more than one transaction can have an exclusive lock on a resource. If an exclusive lock exists on a resource, no other transaction can access that resource.
218. What is INTENT lock?
Establishes a locking hierarchy. For example, if a transaction has an exclusive lock on a row, SQL Server places an intent lock on the table. When another transaction requests a lock on a row in the table, SQL Server knows to check the rows to see if they have locks. If a table does not have intent lock, it can issue the requested lock without checking each row for a lock.
219. What is SCHEMA lock?
Used to prevent a table or index that is being used by another session from being dropped or its schema being modified. When a resource is locked with a schema lock, the object cannot be accessed.
220. What is BULK UPDATE lock?
Bulk update locks (BU): Used to prevent other processes from accessing a table while a bulk load procedure is being processed. It will, however, allow processing of concurrent bulk load processes, which allows you to run parallel loads. A bulk load procedure is one performed by using bulk copy program (bcp) or BULK INSERT.
221. What is KEY RANGE lock?
Locks taken in serializable isolation level for locking ranges of data. These kind of locks can be taken only on keys.
222. What is lock escalation?
Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.
Applies to: SQL Server 2008 R2 and higher versions.
223. Is it possible to disable Lock escalation in SQL Server?
Yes, It is possible to disable lock escalation.
224. What is the least restrictive type of lock?
The least restrictive type of lock is a shared lock.
225. What is the most restrictive lock?
The most restrictive type of lock is a schema-modification.
226. What are the lockable resources in SQL Server?
The following table shows the resources that the Database Engine can lock
Resource | Description |
RID | A row identifier used to lock a single row within a heap. |
KEY | A row lock within an index used to protect key ranges in serializable transactions. |
PAGE | An 8-kilobyte (KB) page in a database, such as data or index pages. |
EXTENT | A contiguous group of eight pages, such as data or index pages. |
HoBT | A heap or B-tree. A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index. |
TABLE | The entire table, including all data and indexes. |
FILE | A database file. |
APPLICATION | An application-specified resource. |
METADATA | Metadata locks. |
ALLOCATION_UNIT | An allocation unit. |
DATABASE | The entire database. |
227. What are statistics in SQL Server?
Statistics refers to the statistical information about the distribution of values in one or more columns of a table or an index. The SQL Server Query Optimizer uses this statistical information to estimate the cardinality, or number of rows, in the query result to be returned, which enables the SQL Server Query Optimizer to create a high-quality query execution plan. For example, based on these statistical information SQL Server Query Optimizer might decide whether to use the index seek operator or a more resource-intensive index scan operator in order to provide optimal query performance.
228. What is the impact of stale statistics over database performance?
Since SQL Server optimizer use the statistics to create execution plans for the SQL Server queries. It is very important to update the statistics time to time to make sure that the SQL Server uses the best optimized plan to execute the SQL Server queries.
230. How to check when the statistics were updated for a table.
We can use stats_date() function to check the statistics updated date.
231. How can we update the statistics on a table?
To update statistics for a particular table we can use below command.
UPDATE STATISTICS Sales.SalesOrderDetail WITH FULLSCAN
232.What are the agents in replication?
Snapshot Agent: Copy Schema+Data to snapshot folder on distributer. Used in all types of replication.
Log reader Agent: Sends transactions from Publisher to Distributor. Used in transactional replication
Distribution Agent: Applies Snapshots / Transactions to all subscribers’ runs at distributer in PUSH and Runs at Subscriber in PULL. Used in transactional and transactional with updatable subscriptions.
Queue reader Agent: Runs at distributer send back transactions from subscriber to publisher. Used in Transnational With updatable subscriptions.
Merge Agent: Applies initial snapshot to subscribers, from the next time synchronize by resolving the conflicts.
232. Can we add an article to the existing publication without generating a snapshot with all articles?
Ans:
Yes! We can do that. Follow the below steps to publish a new article to the existing publication.
There are two parameters that we need to change to “False”. 1. Immediate Sync and 2. Allow_Ananymous.
Both the fields were set to ON by default. If the Immediate_sync is enabled every time you add a new article it will cause the entire snapshot to be applied and not the one for the particular article alone.
Steps:
1. Change the values to “True” for publication properties “Immediate_Sync” and “Allow_Anonymous” using SP_CHANGEPUBLICATION
2. Add a new article to the publication using SP_AddArticle. While executing this procedure along with the required parameters also specify the parameter “@force_invalidate_snapshot=1”.
Command:
EXEC sp_changepublication
@publication = ‘your publication name’,
@property = ‘ALLOW_ANONYMOUS’ ,
@value = ‘False’
GO
EXEC sp_changepublication
@publication = ‘your publication name’,
@property = ‘IMMEDIATE_SYNC’ ,
@value = ‘False’
GO
3. Add the subscriptions to the publication for the single table/article uisng “SP_ADDSUBSCRIPTION”. While executing this proc specify the parameter “@Reserved = Internal”. Generate a new snapshot which only includes newly added article.
3. Replication Commands:
Publisher.SP_ReplTrans: Checks the pending transactions at publication
Distributor.MSReplCommands and MSReplTransactions: Gives the transactions and commands details. Actual T_SQL data is in binary format. From the entry time we can estimate the latency.
Distributor.SP_BrowseReplCmds: It shows the eaxct_seqno along with the corresponding T-SQL command
sp_replmonitorsubscriptionpendingcmds: It shows the total number of pending commands to be applied at subscriber along with the estimated time.
232. Why is primary key needed in Transnational replication?
Primary keys are used to maintain uniqueness of records and to maintain referential integrity between tables, and that is why it is recommended for every article to have a primary key
233. What are prerequisites of transnational replication?
Primary Key:
This is a basic rule that every article should have a Primary Key to be a candidate table for Transnational Replication. Primary keys are used to maintain uniqueness of records and to maintain referential integrity between tables, and that is why it is recommended for every article to have a primary key.
234. Can we add or drop a single article from a publication. If so, How?
It is not necessary to stop activity on the publication or subscription databases in order to add a table (or another object). Add a table to a publication through the Publication Properties – <Publication> dialog box or the stored procedures sp_addarticle andsp_addmergearticle.
Remove a table from the publication using sp_droparticle, sp_dropmergearticle, or the Publication Properties – <Publication> dialog box. You cannot drop articles from snapshot or transactional publications after subscriptions have been added; you must drop the subscriptions first.
235.Define sp_replcounters?
Returns replication statistics about latency, throughput, and transaction count for each published database. This stored procedure is executed at the Publisher on any database.
236.What permissions are needed to a user to monitor replication.
The replmonitor database role in the distribution database. These users can monitor replication, but cannot change any replication properties.
237.Name some commonly used Replication DMVs and their use.
There are four replication related DMV’s in SQL Server.
sys.dm_repl_articles
sys.dm_repl_schemas
sys.dm_repl_tranhash
sys.dm_repl_traninfo
238. Can we rename a database used in Publication or subscription.
No. we would need to drop the publications, rename the database and re-configure replication all over again. So there is no easy way to do this.
239.Are logins and passwords replicated?
No. You could create a DTS, SSIS or revlogin package to transfer logins and passwords from a Publisher to one or more Subscribers
240.Are tables locked during snapshot generation?
The length of time that the locks are taken depends on the type of replication used:
· For merge publications, the Snapshot Agent does not take any locks.
· For transactional publications, by default the Snapshot Agent takes locks only during the initial phase of snapshot generation.
· For snapshot publications the Snapshot Agent takes locks during the entire snapshot generation process.
Because locks prevent other users from updating the tables, the Snapshot Agent should be scheduled to execute during periods of lower activity on the database, especially for snapshot publications.
241.What recovery model is required on a replicated database?
Replication is not dependent on any particular recovery model. A database can participate in replication whether it is in simple, bulk-logged, or full. However how data is tracked for replication depends on the type of replication used.
242.Can multiple publications use the same distribution database?
Yes. There are no restrictions on the number or types of publications that can use the same distribution database. All publications from a given Publisher must use the same Distributor and distribution database.
243. Does replication encrypt data?
No. Replication does not encrypt data that is stored in the database or transferred over the network.
244.What is the effect of running a bulk insert command on a replicated database?
For transactional replication, bulk inserts are tracked and replicated like other inserts. For merge replication, you must ensure that change tracking metadata is updated properly.
245.Why can’t I run TRUNCATE TABLE on a published table?
TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.
246. How do I move or rename files for databases involved in replication?
In versions of SQL Server prior to SQL Server 2005, moving or renaming database files required detaching and reattaching the database. Because a replicated database cannot be detached, replication had to be removed from these databases first. Beginning with SQL Server 2005, you can move or rename files without detaching and re-attaching the database, with no effect on Replication
247.If I create a publication with one table as an article, and then change the schema of the published table (for example, by adding a column to the table), will the new schema ever be applied at the Subscribers?
Yes. Schema changes to tables must be made by using Transact-SQL or SQL Server Management Objects (SMO). When schema changes are made in SQL Server Management Studio.
248.How will you monitor replication activity and performance?
The easiest way to monitor replication activity and performance is to use replication monitor, but you can also use the below tools to monitor replication performance:
· T-SQL commands.
· Microsoft SQL Server Management studio.
249.Is there a need to stop activity on a database when it is published?
No. Activity can continue on a database while a publication is being created. Be aware that producing a snapshot can be resource-intensive, so it is best to generate snapshots during periods of lower activity on the database (by default a snapshot is generated when you complete the New Publication Wizard).
250.In Transnational replication, If a table in database ‘A’(Publisher) is dropped, will the table get dropped in ‘B’(Subscriber)?
You cannot drop a table that is replicated. You have to first drop the article.
251. In Transnational Replication, If we drop a column in a table in database ‘A’, what will happen to the column in the same table in database ‘B’?
Issuing ALTER TABLE … DROP COLUMN at the Publisher, will result in the command being replicated to the Subscriber.
252. Is it possible to run multiple publications and different type of publications from the same distribution database?
Yes it can be done and there are no restrictions on the number or types of publications that can use the same distribution database. One thing to note though is that all publications from a Publisher must use the same Distributor and distribution database.
253. How do I rebuild indexes or tables in replicated databases?
There are a variety of mechanisms for rebuilding indexes. They can all be used with no special considerations for replication, with the following exception: primary keys are required on tables in transactional publications, so you cannot drop and recreate primary keys on these tables.
254. How do I add or change indexes on publication and subscription databases?
Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.
255.How do I determine if the data at Subscribers is synchronized with data at the Publisher?
Use validation. Validation reports on whether a given Subscriber is synchronized with the Publisher
256.How do I add a table to an existing publication?
It is not necessary to stop activity on the publication or subscription databases in order to add a table (or another object). Add a table to a publication through the Publication Properties – <Publication> dialog box or the stored procedures sp_addarticle and sp_addmergearticle
257. How do I remove a table from a publication?
Remove a table from the publication using sp_droparticle, sp_dropmergearticle, or the Publication Properties – <Publication> dialog box. You cannot drop articles from snapshot or transactional publications after subscriptions have been added; you must drop the subscriptions first.
258. How do I determine whether there are transactions or rows to be replicated?
For transactional replication, use stored procedures or the Undistributed Commands tab in Replication Monitor. For more information, seeView Replicated Commands and Other Information in the Distribution Database (Replication Transact-SQL Programming) and View Information and Perform Tasks for the Agents Associated With a Subscription (Replication Monitor).
For merge replication, use the stored procedure sp_showpendingchanges
259. Does replication work in conjunction with log shipping and database mirroring?
Yes..
260.Does replication work in conjunction with clustering?
Yes. No special considerations are required because all data is stored on one set of disks on the cluster.