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
