SQL SERVER DBA HIGH AVAILABILTY(Interview Questions)

SQL SERVER DBA – HIGH AVAILABILTY

Logshipping

1.What is Log shipping and purpose of Log shipping?

To achieve high availability and high protection, Log shipping copies transactions from a ‘primary server’ to one or more ‘secondary servers’. In Log shipping, T.Log backups are sent to one or more secondary servers and then restored to the destination servers individually. If the Primary database becomes unavailable, any of the secondary database can brought into online manually. The Secondary server acts as a Backup server and provides read-only query processing to reduce the load on the Primary server. (For query processing, secondary servers should be configure in stand-by mode).

2.What is Primary Server, Secondary Server & Monitor Server?

Primary Server: - Primary Server is a Production server which holds the original copy of the database. Log shipping configuration and administrating will be done from Primary Server.

Secondary Server: - Secondary servers hold the standby copy of the database. We must initialize the DB on a secondary server by restoring a backup from the Primary server using either NORECOVERY option or the STANDBY option. By using STANDBY option Users can have read-only access to it.

Monitor Server: - An optional Server is called as Monitor Server that records history and status of backup, copy and restore operations and raises alerts if any operations fail. The Monitor Sever should be on separate server to avoid losing critical information. Single Monitor Server monitors multiple Log shipping configurations.

3.What are the Jobs running for Log shipping and explain them?

Log shipping having four operations which are handled by SQL Server Agent Job.

i.Backup Job: - Backup job is created on Primary Server instance and it performs backup operation. It logs history on the local server and monitor severs and deletes old backup files and history information.

ii.Copy Job: - Copy Job is created on Secondary server instance and it performs copies the backup files from primary sever to secondary server. It logs history on the secondary server and monitor server.

iii.Restore Job: - Restore Job is created on the Secondary server instance and it performs restore operation. It logs history on the local server and monitor server and deletes old files and history information.

iv.Alert Job: - If a Monitor Server is used, the Alert Jobs is created on the Monitor server instance and it raises Alerts if any operations have not completed successfully.

4.Requirements for Log shipping?

i.SQL Server 2005 Standard Edition, Workgroup Edition, or Enterprise Edition must be installed on all server instances involved in log shipping.

ii.All servers should have the same case sensitivity settings.

iii.The databases must use the full recovery model or bulk-logged recovery model.

5.How to configure Log shipping?

i.Choose Primary Server, Secondary Servers, and optional Monitor server.

ii.Create a File share to keep Transaction log backups (Best to place on a separate computer)

iii.Create a folder for each Secondary server into which transaction log backup copies.

iv.Choose Backup Schedule for Primary Database

v.Choose Copy and Restore Schedules for Secondary Database

vi.Choose Alert Job schedule for Monitor Server if configured

6.What are permissions required for Log shipping?

We must have sysadmin on each server instance to configure Log shipping.

7.In Logshipping which Recovery Models can we used?

We can use either full or bulk logged recovery model for log shipping.

8.Where you monitoring Log shipping and how?

We can monitoring the Log shipping in the following ways.

i.)Monitor server (History Tables):- Monitor Server tracks all statistics, status and errors that could be happen during Log shipping.

Log_shipping_monitor_primary:- Stores primary server status

Log_shipping_monitor_secondary:- Stores secondary servers status

     Log_shipping_monitor_history_detail:- Contains history details for logshipping agents.

     Log_shipping_monitor_error_detail:- Stores error details for log shipping jobs.

     Log_shipping_monitor_alert:- Stores Alert Job ID

ii.)System Stored Procedures (MSDB):- System Stored procedures gives the history information about the specified server that are configured in Log shipping.

    sp_help_log_shipping_monitor (Run at Monitor Server)

    sp_help_log_shipping_monitor_primary @Primary_Database = ‘DBName’ (Run at MS)

    sp_help_log_shipping_monitor_secondary @ Secondary_Database = ‘DBName’ (Run at MS)

   sp_help_log_shipping_alert_job (Run at Mon Server)

   sp_help_log_shipping_primary_database @ Database = ‘DBName’ (Run at Primary Server)

   sp_help_log_shipping_secondary_database @ Database = ‘DBName’ (Run at Sec Server)

iii.)Transaction Log shipping Status report (Summary Reports):-  This report shows the status of log shipping configurations for which this server instance is a primary, secondary or monitor.

 iv.)SQL Server Agent Job History

 v.)Checking the SQL Server Log

9.How Secondary Server will brought into Online, if the Primary Server becomes Unavailable?

If the Primary Server will become un-available, do the following steps.

a)            Take the Tail of Log from Primary server

b)            Restore Tail of log into all Secondary Database

c)            Remove Log-shipping configuration from Primary Server

d)            Select any one of Secondary server and bring into online with

           Alter Database  DBName set Online

e)            Right click on Primary Database and Generate script for Users and Logins.

f)            Then move the script to Secondary server to create Users and Logins

g)            Re-configure log shipping from New Server (Secondary server)

10.What are errors occurred in Log shipping?

There are two errors are occurred during Log shipping

1)            14420:- This error occurs when the Backup job fails

2)            14421:- This error occurs when the Restoring job fails

11.What is the use of .wrk and .TUF file in LOGSHIPPING?

TUF File: Its Transaction Undo File. It Generated only when you Have Configured Log Shipping With Stand By Option. Since In Stand by Log Shipping Secondary Database is Available to User. So TUF Keep Pending Transaction like uncommitted which are in Log File Came From Primary So That when Next Log Backup Will Come From Primary They Can Be Synchronized At Secondary.

 .WRK File:  This Extension Is Given To A File Which is  Being Copied From Primary

  Backup location to Secondary and once Copy Process has been completed these

 file are renamed with .trn file.

12) How to find out the trasactions latency primary to secondary in logshipping?

Tracio Tokency property in distributer.

13) How to find out the trasactions not coming from primary to secondary in logshipping?

If ms_repl commands are not executed then we are facing, so we can execute the ms_repl commands.

Mirroring

1)   What is Database Mirroring? What are the benefits of that?

“Database mirroring is a method of increasing database availability”, which supports automatic failover with no loss of data.

Benefits:-

h)  Increases data protection.

i)   Increases availability of a database.

j)   Improves the availability of the production database during upgrades.

2)   What are the prerequisites for Database Mirroring?

a)            Both Servers are requires same edition either Standard Edition or Enterprise Edition.

b)            If Witness server configured, the server should be installed Standard Edition, Enterprise Edition, Workgroup Edition, or Express Edition.

c)            All Servers should use the same master code page and collation.

d)            Mirror Server has same database name and use only With NoRecovery option.

e)            Mirroring uses the full recovery model. (Simple and bulk-logged not supported)

f)            All logins for connecting Principal Database should be reside on Mirror database

3)   What are the Restrictions for Database Mirroring?

a)  Maximum 10 databases per instance can support on a 32-bit system.

b)  Database mirroring is not supported with either cross-database transactions or distributed transactions.

4)   Explain about Principal, Mirror and Witness Servers?

1)            Principal Server:-  One Server serves the database to client is called Principal server and it having original data. Can have only one Principal Server and it has to be on a separate server.

2)            Mirror Server:- Other server instance acts as a hot or warm standby server is called Mirror server and it having copy of database.

3)            Witness Server:- The witness server is an optional server and it controls automatic failover to the mirror if the principal becomes unavailable. To support automatic failover, a database mirroring session must be configured in high-availability.

5)   In which Operations are running the Operating Modes?

Asynchronous:- Under asynchronous operation, the Principal server does not wait for a response from the mirror server after sending the log buffer.

Synchronous:- Under synchronous operation, the Principal server sends the log buffer to the mirror server, and then waits for a response from the mirror server.

6)   What are the Operating Modes and explain them?

a.            High Availability:- High-availability mode, runs synchronously. Requires a Witness Server instance. The Principal server sends the log buffer to the mirror server, and then waits for a response from the mirror server.

b.            High Protection:- High-protection mode, runs synchronously. Always commit changes at both the Principal and Mirror.

c.            High Performance:- High-performance mode, runs asynchronously and the transaction safety set to off. The Principal server does not wait for a response from the mirror server after sending the log buffer. The principal server running nice and fast, but could lose data on the mirror server.

7)   What is the default of Port numbers of Principal and Mirror servers? How to find the Port numbers?

The default port numbers of principal and mirror servers are 5022 and 5023.

To Find Port Number:- SELECT name, port FROM sys.tcp_endpoints

8)   What is End Point? How u create end point?

An endpoint is a network protocol which is used to communicate over the network.

Creation of an end point:

Create endpoint <endpoint name> State=started/stopped/disabled

          as tcp (listener port=5022/5023) for database_mirroring (role=partner/witness)

9)   Which Trace flag is used in Mirroring?

Trace flags are used to temporarily set specific server characteristics or to switch off/on a particular behavior. 1400 Trace flag is used in mirroring.

To set trace flag for Database mirroring:- Configuration Manager > Right click on server instance > Properties > Advanced tab > Startup parameters > -t1400 (add)

10)                In which Recovery model we can use in Mirroring?

In mirroring the principal and mirror databases are used only full recovery model

11)                What is Role-switching?

Inter changing of roles like principal and mirror are called role switching.

12)                What is the Syntax to stop the Database Mirroring?

Alter database <database name> set partner off

Failover: Alter database <database name> set failover

alter database database_name set partner force_service_allow_data_loss

13)                How to configure Mirroring?

a)  Choose Principal Server, Mirror Server, and optional Witness server.

b)  The principal and mirror server instances must be running the same edition either Standard Edition or Enterprise Edition

c)  The Witness server instance can run on SQL Server Standard Edition, Enterprise Edition, Workgroup Edition, or Express Edition

d)  Mirror database requires restoring a recent backup and one or more T.log backups of the principal database (with Norecovery)

14)                How to monitoring Mirroring?

There are six methods are available for monitoring the Database Mirroring

a)  SQL Server Management Studio:- A green arrow on the mirror server is indicates running well. A red arrow indicates problems that need to investigate.

b)  SQL Server Log:- It provides information of Mirroring establishment and status. If any errors occurs it will be logged to SQL Server log and Windows event log.

c)  Performance Monitor:- It can provides real-time information about Database mirroring. We can use performance counters to get status of the database mirroring such as Bytes received/sec, Bytes sent/sec, Transaction delay etc.

d)  Profiler:- Profiler many events are providing the status of the Database mirroring

e)  Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds.

To open DM Monitor:- Right click on Principal Database > Tasks > Select Launch Database Mirroring Monitor.

f)  System Stored Procedures:-

1.  sp_dbmmonitoraddmonitoring

2.  sp_dbmmonitorchangemonitoring

3.  sp_dbmmonitorhelpmonitoring

4.  sp_dbmmonitordropmonitoring

15)                What is Hardening?

As quickly as possible, the log buffer is written to the transaction log on disk, a process called hardening.

16)                What is Log buffer?

A log buffer is a special location in memory (RAM). SQL Server stores the changes in the database’s log buffer.

17)                How to Set a Witness Server to Database Mirroring?

SSMS:- Right Click on Principal Database > Tasks > Mirror > Click on Configure Security > Provide the End point for Witness server > Click oK

T-SQL:- ALTER DATABASE AdventureWorks SET WITNESS = 'TCP://prasad.local:5024' (Do this from the Principal Server)

18)                How to Remove a Witness Server from Database Mirroring?

SSMS:- Right Click on Principal Database > Tasks > Mirror > Remove TCP address from the Witness > Click oK

T-SQL:- ALTER DATABASE AdventureWorks SET WITNESS OFF

19)                How to Setup Fully Qualified Names for Database Mirroring?

I. FQDN Error

One or more of the server network addresses lacks a fully qualified domain name (FQDN).  Specify the FQDN for each server, and click Start Mirroring again.

The syntax for a fully-qualified TCP address is:

TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>

 

II. RECTIFYING FULLY QUALIFYED NAMES

1)                            To View Endpoints:-SELECT * FROM sys.database_mirroring_endpoints;

2)                            Remove existing all Endpoints from Principal, Mirror and Witness servers :- DROP ENDPOINT [ENDPOINT_NAME]

3)                           Adding "local" as the primary DNS suffix as follows:-

a)  Right-click My Computer, and then click Properties. The System Properties dialog box will appear.

b)  Click the Computer Name tab.

c)  Click Change. The Computer Name Changes dialog box will appear.

d)  Click More. The DNS Suffix and NetBIOS Computer Name dialog box will appear.

e)  Enter the appropriate DNS suffix for the domain.

f)  Select the Change primary DNS suffix when domain membership changes check box.

g)  Click OK to save the changes, and then click OK to exit the Computer Name Changes dialog box.

h)  Click OK to close the System Properties dialog box, and then restart the computer for the change to take effect.

4)                            Reconfigure the Database mirroring either GUI or T-SQL

20)                What are the Database Mirroring states?

1) SYNCHRONIZING:-

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.

2)  SYNCHRONIZED:-

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.

3) SUSPENDED:-

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.

4) PENDING_FAILOVER:-

          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.

5) DISCONNECTED:-

          The partner has lost communication with the other partner

Replication

1) What is Replication?

“Replication is the process of copying and distributing data between databases to different servers throughout the enterprise”.

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

2) What are the uses of Replication?

a)  Server to Server Replication:-

1.  Improving scalability and availability

2.  Data warehousing and reporting

3.  Integrating data from multiple sites

4.  Integrating heterogeneous data

5.  Offloading batch processing

b)  Server to Client Replication:-

1.  Exchanging data with mobile users

2.  Retail point of sale (POS) applications

3.  Integrating data from multiple sites

3) Types of Replication and explain each?

a)  Snapshot replication:- Snapshot replication takes a picture or a snapshot of the database and propagated to the subscribers. It reduces the overhead on the Publishers and Subscribers because it does not monitor data updates. Snapshot replication is very useful, when the source data is changes occasionally (Reporting).

b)  Transactional replication:- Transactional Replication starts with a snapshot of the publisher database. With Transactional Replication, any changes made to the articles are captured from the transactional log and propagated to the distributors continuously and automatically. Using Transactional Replication we can keep the publisher and subscriber in almost exactly the same state.

c)  Merge replication:- Merge Replication starts with a snapshot of the publisher database. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

4) Explain about Publisher, Subscriber and Distributer?

a)  Publisher:- The Publisher is a database that makes data available for replication. The Publisher can have one or more publications.

b)  Distributor:- The distributor is the intermediary between the publisher and subscriber. It receives published transactions or snapshots and then stores and forwards these publications to the subscribers.

c)  Subscribers:-  Subscribers are database servers that store the replicated data and receive updates. A subscriber can receive data from multiple publishers. Based on the replication type, the Subscriber can also pass data changes back to the Publisher or republish the data to other Subscribers.

5) Explain about Article, Publication, Subscription?

a)  Article:- An Article is the data, transactions, or stored procedures that are stored within a publication. This is the actual information that is going to be replicated.

b)  Publication:- The publication is the storage container for different articles. A subscriber can subscribe to an individual article or an entire publication.

c)  Subscription:-Subscription is a request by the subscriber to receive the publication.

6) Which recovery models are used for Replication?

Full and Bulk-logged Recovery models

7) How to monitor the Replication?

a)  Replication Monitor:- Replication Monitor is a GUI tool provides detailed information on the status and performance of publications and subscriptions.

By using replication monitor we can find out

(1) which subscriptions are slow

         (2) Why is an agent not running

         (3) Time taken to transaction commit

         (4) Why merge replication is slow

         (5) How far behind subscription etc.

To launch Replication Monitor :- Connect Instance > Right Click on Replication folder >

 Select launch Replication monitor

b)  Management Studio:- By using management Studio we can see View Snapshot Agent Status, View Log Reader Agent Status, View Synchronization Status etc.

Through SSMS:- Connect Instance > Right Click on Publication > Select the option

c)  System Monitor:- Provides information on the performance of various processes of Replication.

8)           What are the Agents available for Replication and explain each?

SQL Server Agent hosts and schedules the agents used in replication and also controls and monitors operations outside of replication.

b)  Snapshot Agent:-The Snapshot Agent is typically used for all types of replication.

It is responsible for copying the schema and data from the publisher to the subscribers, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent runs at the Distributor.

c)  Log Reader Agent:- The Log Reader Agent is used for transactional replication. It moves information from the transaction log on the publisher to the distribution database.

d)  Distribution Agent:- The Distribution Agent is used for snapshot replication and transactional replication. It is responsible for moving the stored transactions from the distributor to the subscribers.

e)  Merge Agent:- The Merge Agent is used for merge replication. It is responsible for converging records from multiple sites and then redistributing the converged records back to the subscribers.

f)  Queue Reader Agent:- The Queue Reader Agent is used for transactional replication with the queued updating option. It runs on the Distributor and is responsible for reading messages from the queue on the subscribers and applying them to the appropriate publication.

9)           What are the Agents used for Transactional Replication?

a)            Snapshot Agent

b)            Log Reader Agent

c)            Distribution Agent

10)        What are the Agents used for Merge Replication?

a)    Snapshot Agent

b)    Merge Agent

11)        What is the Process of Transactional Replication?

Three Agents are doing the Process for Transactional Replication

CLUSTERING

1) What is Windows Cluster?

Clustering can be best described as a technology that automatically allows one physical server to take over the tasks and responsibilities of another physical server that has failed. The obvious goal behind this, given that all computer hardware and software will eventually fail, is to ensure that users running mission-critical applications will have very less downtime when such a failure occurs.

2) What is a Cluster Node?

A cluster node is a server within the cluster, and it has Windows Server and the Cluster service installed.

3) What is called a Resource in Windows cluster?

A resource is a physical or logical entity, which has below properties:

• Can be brought online and taken offline
• Can be managed in the failover cluster
• Can be owned by only one node at a time

To manage resources, Cluster service communicates with a resource DLL through Resource Monitor.

4) What are the different states of a Resource in Windows cluster?

All resources can have following states

• Offline
• Offline_Pending
• Online
• Online_Pending
• Failed

5) What is the difference between Active\Passive and Active\Active cluster?

·         Active\Passive :  An Active – Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time. The other node, called as Passive node is always online but in an idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.

·         Active\Active : An Active – Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point of time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up (after fixing the issue that caused the node failure). The instance is then failed over back to its designated node.

ACTIVE_PASSIVE: When two nodes cluster without msdtc

        We have 6 IPs which

• 2 Windows nodes – Public
• 2 Private IP Addresses – Private
• 1 Windows Virtual Cluster Name
• 1 MSDTC
• 1 SQL Server Virtual Network Name

ACTIVE_ACTIVE: When two nodes cluster without msdtc

We have 7 IPs which is

• 2 Windows nodes – Public
• 2 Private IP Addresses – Private
• 1 Windows Virtual Cluster Name
• 1 MSDTC
• 1 SQL Server Virtual Network Name

If Analysis services:

• 1 SQL Server Analysis Services

Quorum has participate nodes information.

1)   Difference between SQLSERVER 2005 and SQLSERVER 2008 Cluster Installation?

In SQL2005 we have the option of installing SQL in remaining nodes from the primary node,

But in SQL2008 we need to go separately (Login to the both nodes) for installing sql cluster.

2)   What is MSDTC in SQL Server clustering?

MSDTC stands for Microsoft Distributed Transaction Coordinator.

The Microsoft Distributed Transaction Coordinator service (MSDTC) tracks all parts of the transactions process, even over multiple resource managers on multiple computers. This helps ensure that the transaction is committed, if every part of the transaction succeeds, or is rolled back, if any part of the transaction process fails.

3)   What is Quorum in SQL Server clustering?

This is a logical drive assigned on the shared disk array specifically for Windows Clustering. Clustering services write constantly on this drive about the state of the cluster. Corruption or failure of this drive can fail the entire cluster setup. It also acts as a voter in the fail over process in case of odd number of nodes.

Different types of Quorum Models supported in windows Server 2008?

• Node Majority – Used when Odd number of nodes are in cluster.
• Node and Disk Majority – Even number of nodes (but not a multi-site cluster)
• Node and File Share Majority – Even number of nodes, multi-site cluster
• No Majority: Disk Only – This is the traditional MSCS quorum model, where a shared quorum disk must be online and nodes must be able to communicate with that disk.

  http://dbanaga.blogspot.in