Always On in SQL Server is new high availability disaster recovery feature in SQL Server 2012. Initially it named as HADRON. HADRON means High availability Disaster Recovery Always On. Always On uses clustering technology for failover and mirroring in keeping multiple mirrored copies of the database.
In Always On we can failover multiple databases at the same time using availability groups.
Always On provides ability to have multiple replicas which is not available in Database mirroring. We can have one primary and up to 4 secondary replicas with Always On.
The Secondary replicas configured for read access. We can use for reporting purpose, backups etc.
Always On in SQL Server also have 2 availability modes
Synchronous mode provides Zero data loss.
Asynchronous mode provides better performance, chance of data loss.
For each replica we can configure separate mode.
Components of Always On
Availability Group is a container used to group databases. Always On availability groups contains multiple databases all of which can be automatically failover dover as single unit.
Availability Replica is a server that contains the availability group which can use for failover.
Primary Replica is a server the holds read-write copy of the databases contained within availability group.
Secondary Replica is secondary server that holds copy of primary replica databases, only read option is available here in an availability group.
Always On in SQL Server Installation
Always on does not require cluster installation. We can go through normal SQL Server installation. Here entire SQL instance will not be clustered only availability which participated in Always On will be clustered. We need to maintain same drive letters and paths for databases on replicas.
After installing SQL Server go to configuration manager, MS SQL Server, Right click and go to SQL Server properties next click Always on high availability. We will get windows failover cluster name as part of windows server failover cluster installation. Under that enable always on Availability Groups.
Once we enabled Always on Availability Groups, we can create availability groups that contains all databases which we want to failover.
Go to SSMS Right click on Availability folder, it opens wizard, select the databases which you want to keep in Always on in primary replica as well as replicas that will host on secondary replica here.
The wizard automatically creates end points to synchronize data and takes backup of primary replicas and restore them on secondary replicas.
If we don’t want, we can do backup and restore manually.
Architecture of Always On in SQL Server
Always on availability group is superior to SQL Server clustering.
Configuration, deployment, installation is relatively simple comparing to normal clustering.
All replicas (Nodes) will have a copy of databases, there is no shared storage hence we can avoid single point of failover.
We have readable secondary replica hence we can distribute read-only load to secondary node and read-write load to primary hence better utilization of both servers. Whereas we must keep in passive state in clustering. There is no concept of readable databases as we keep everything in shared disk.
Always on superior to database mirroring
We have up to 4 mirrored instances replicas here.
We can use combination of synchronous commit mode for some databases and asynchronous commit mode for other databases at the same time, not possible in mirroring.
Synchronous mode for high availability (Automatic failover) and asynchronous mode for disaster recovery purpose.
We can combine 2 or more databases and failover them as a unit, we can perform this database mirroring.
Databases are always in recovery mode in mirroring, here secondary replica databases are readable, hence we can take load from primary replica.
Backup operations can be performed on secondary replica databases.
How Always On in SQL Server works
Always on feature based on windows cluster services only. Availability groups are a grouping database with a virtual name and IP Address that act as a single unit for users to access. If a server fails with in an availability group, the entire group is failover to the secondary replica of secondary server.
To create databases, we need SQL Sever to be installed on a server under windows clustered. SQL Server clustering does not need in Always On. Primary replica sends Transaction log records of each primary database to every secondary database. The secondary replica applies changes to the secondary database. Data Synchronization occurs between primary and secondary databases. Windows failover clusters monitors this resource group to evaluate health of primary replica.
The quorum for Always on Availability group is resides on all nodes of cluster which helps in failover. Here is o witness role in always on availability groups.
Types of failovers are like mirroring.
Synchronous commits support planned and manual failover. Automatic process and no data loss.
Asynchronous commits support only forced failover. Chance of data loss.
Advantages and Disadvantages of Always On in SQL Server
We can maintain multiple secondary databases.
Secondary database used for read-only access, we can use for reporting purpose and backups.
Protects data against disk failure.
Automatic failover and group of databases failover as single unit.
Requires more disk space to store secondary replica data.
Requires special setup configuration.
Expensive to implement.
Clustering in SQL Server is a Windows level high availability option to increase the availability of physical server hardware, operating system, and SQL server instances. If any of these three fails on one server, The Other server in cluster automatically takes task of failed SQL server instance
Clustering in SQL Server is Service level high availability feature. If in case one node down the SQL Server services are moved to node2. In this feature data loss is zero.
A Windows failover cluster can support up to 16 nodes in SQL server 2012. Cluster reduce downtime to minimum. Most clustering deployment is between two servers only. The cluster service called as nodes
How Clustering in SQL Server works
SQL server cluster assigns own virtual name and virtual IP address. These details shared by both nodes users connects to cluster using this virtual name, if a fail over occurs, the cluster will still retain on same virtual name and IP address although a new node will be responding to user requests.
cluster reduces downtime in upgrading hardware or install a new service pack on SQL server cluster
provides high availability to Physical server hardware, operating system and SQL server
Clustering requires expensive hardware and software
Geographical dispersed clusters SAN hardware and software for data transfer
Architecture of Clustering
virtual name is shared by both Servers, Active node connect to storage disk and access SQL databases, MSDTC and the quorum transactions goes to which ever server is active, when Heartbeat signal between NIC fails automatic fails happens using internal Heartbeat mechanism
Types of Clustering
Active/Active cluster is to make better use of available hardware. Instead of keeping passive node ideal there are two instances, one on each physical node of cluster to save hardware costs so we can use active-active cluster. When a failover occurs two instances will run on single server. Each instance needs it own logical shared disk. The disks of 2 SQL Server instances will be different, disks cannot be shared among SQL server instances.
when two instances run on single server the performance may affect. We need to configure both servers with high configuration to meet active-active cluster requirements.
The node SQL server instance running is called as active node and the other server SQL server not running referred as passive node, it is in a state of readiness. If a failover occurs passive node automatically becomes active and starts serving user requests, so formally active node becomes a passive node. Here both nodes connected to external storage disk, only active nodes access the SQL databases MSDTC and quorum
Prerequisites to configure clustering
public and private network MSDTC
virtual name, both servers is using virtual name of cluster. User connects to SQL cluster using this virtual name and IP address. Primary node will respond to user requests. If a failover occurs to secondary node still cluster will retain the same virtual name and IP address. Users may not notice that the failover happens inside the cluster
Nodes referred as physical servers in clustering. These nodes should have same access to shared storage. Each node talks to one another via a network. If one node does not communicate to other node in cluster the secondary node, will it take ownership of user transactions.
Automatic Failover: one server stops another node becomes owner of cluster.
Manual Failover: DBA intentionally failover for maintenance
Clustered instances store data on shared disk. Both nodes physically connect to disk, but only active node can access the instance databases. This shared disk has at least 2 logical partitions. One partition is used for storing the clustered instances SQL Server databases and another is used for quorum.
Quorum is a log file similar T-log file, records any change made on active node, when passive node becomes active it reads quorum log file and applies changes on newly active node. This file quorum.log resides in the quorum disk. Quorum is main interpreter between nodes. It stores latest cluster configuration and helps other nodes to take ownership when one node fails.
Voting Mechanism Quorum is designed to handle split brain scenario when nodes unable to communicate each other, each node assumes and bring resources online. When same SQL resources brought online on more nodes at same time will lead to data corruption. This scenario is called split brain
we have 4 nodes, one instance of SQL running on each node. Node 1 and 2 lost communications with node 3 and 4. In this scenario nodes 1 and 2 try to bring SQL instance resources owned by Nodes 3 and 4, in the same way nodes 3 and 4 will try to bring SQL resource owned by node 1 and 2, which will lead to disk corruption and other issues. Quorum is designed to handle the scenario. The cluster will force the cluster service to stop in one of the nodes to ensure only one owner for a particular SQL server resource group
Voting Process in Quorum
Quorum is based on voting algorithm, where more than half (above 50%) must be online to communicate each other. Cluster knows how many nodes available. Each node will have vote if number of votes drop below majority cluster service will stop.
Cluster requires more than half of total votes to achieve quorum. This is to avoid tie between nodes. In 3 node cluster 2 voters must be online to have quorum.
To achieve majority votes, we have 4 quorum models.
Public and Private Network
Each node of a cluster must have at least 2 network cards. One network card will be connected to public network and other to a private network.
Public network is the network where users are connected and communicate to clustered SQL Server instance.
Private network is used for communications between the nodes of cluster. It is used mainly for what is called the heartbeat signal. 2 forms of communications are executed.
LookAlive: Verifies whether SQL Server service runs on the online every 5 seconds by default.
Isalive: Verifies that SQL Server accepts connections by executing SP_Server_Disgnostics command
Microsoft distributed transaction coordinator is used for distributed transactions between clustered nodes and other remote servers If a transaction in a query have distributed transactions, we need MSDTC as cluster resource
Replication in SQL Server is a object level high availability feature. Normally in other high availability technologies we replicating entire database but in this we can replicate database objects like tables, stored procedures, triggers, functions etc.
Do you want to replicate your data from one server to another server? Or do you want to migrate data from old version to newer version in your database?
Then SQL Server replication is the perfect solution for your problem.
You can replicate database objects like tables, stored procedures, triggers, cursors etc. from one server to another server.
Replication is mainly used as a data migration technology although it is one of the high availability options. The concept of replication is to migrate data and database objects from one server to another server.
Why is replication a powerful migration technology?
1. Replication can be used for multiple purposes
We can maintain 2 servers, both databases are completely synchronized, whatever changes are being done on the primary server goes to the secondary server.
Load distribution is possible in replication as both primary and secondary servers are available to users. We can split users among the 2 servers.
After initial synchronization between 2 servers, database replication tracks the changes, and these changes migrate from source server to subscriber server instead of the entire database.
2. Replication has a filter mechanism, while moving data from one server to another server we can use a filter or condition like dept equal to sales or emp salary greater than 100, while migrating data replication checks whether data meeting condition or not. If met data will migrate, if not it ignores the data. This is completely lack in other migration technologies like backup and restore, import and export
3. Replication can be done on object level, we can select tables, stored procedures, functions as well as along with data.
4. Replication can be configured between heterogeneous databases. We can migrate data from SQL Server to Non-SQL Servers like oracle, DB2, Teradata etc.
5. Replication has scheduling options; we can define schedules with high latency or low latency.
How Replication works
Replication requires 3 servers and 3 databases along with it require articles, publications, and agents.
Publisher is a source server containing primary database called publication database here we can select entire database or few tables, stored procedures of database.
Subscriber is a destination server whatever data coming it stores in subscription database
Entire replication process carried in distribution server. It establish connection with publisher identifies data ready for replication copies data to subscriber.
Publisher and distributor on same machine
Publisher and distributor on different machines.
Articles are the database objects that being replicated.
Publications are collection of articles.
Agents are executables (jobs) used to perform the replication operations.
Replication uses news paper terminology, publisher (News printing person) has publication database in that articles means tables, stored procedures will be available for migration.
Distributor (Delivery person) collect the articles from publication database and forward the same data to subscriber using agents and distribution database
Subscriber (Receiver) receives all the data and keeps in subscription database
When we configure replication, we need to get details
Publisher, subscriber, and local distributor
Publisher, subscriber, and remote distributor
Central publisher and multiple subscribers
Multiple publisher and central subscriber
Hybrid (combination of any 2 above models)
We need get the details whether flow is one way or two way
Data flows from publisher to subscriber
If both servers in use need to configure replication. By way as the data moves from publisher to subscriber and get data from subscriber to publisher
Latency is time interval, schedule the data movement either continuous or hourly or weekly.
Types of replication
Peer to peer replication
Snapshot is going to make exact copy of an existing database; it is one time replication. 2 agents are created in snapshot replication.
It perform snapshot generation
Snapshot generation process
Snapshot agent establishes connection between publisher and distributor.
Snapshot agent identifies what are objects participated in replication process applies schema locks on those objects while copying structure.
Snapshot generates script of tables and output will be copies as ‘tablename.sch’. Snapshot folder is a storage location
Data will be copied with .bcp
Triggers will be copied with .trg
Indexes will be copies with .idx
Other constraints copy with .pre
If there are 5 tables participated in replication, for each table 5 files will be generated total 25 files will be generated and these files will copy from publisher database to snapshot folder
Once snapshot generated automatically locks will be removed on objects and breaks connection between publisher and distributor.
Distribution agent works from here. It goes to snapshot folder identifies tables and creates same files in subscriber. Once files copy done to subscriber the relation between distributor and subscriber also breaks.
When we go for snapshot
When source database has constant data
When huge latency is accepted
When we do not want changes in subscriber regularly.
Transaction Replication is one way replication. Initial synchronization will be done using snapshot replication. Snapshot generation process is same in all types of replications. Ongoing changes should be tracked and copy those changes to subscriber continuously using log reader agent.
In transaction replication changes will be tracked through log files. While copying modifications locks will be applied once schema is copies locks will be released, log read will read ongoing changes by scanning log file of the database. From here distribution agent will copy data to subscriber. This we call as concurrent snapshot.
Peer to peer replication
Peer to peer replication is introduced in SQL Server 2005 and enhanced in SQL Server 2008, Each server will act as a publisher and a subscriber. Data will move to all the servers
We have conflicts when one server fails in this replication. In SQL Server 2008 we can add or remove servers without interrupting the other servers. Conflicts handling introduced in SQL Server 2008.
Merge replication allows publisher and subscriber to make changes to data independently and then merge the results. We have conflicts with merge replication. It uses merge agent.
What are the types of replication in SQL Server?
1. Snapshot Replication 2. Transactional Replication 3. Peer to Peer Replication 4. Merge Replication 5. Transactional Replication with updatable subscription (this feature was deprecated from SQL Server 2012)
How Replication works?
Replication requires 3 servers publisher, distributor and subscriber along with it requires articles, publications and agents. Publisher is a source server containing primary database called publication database here we can select entire database or few tables, stored procedures of database
What are the benefits of replication in SQL Server?
High availability, Scalability, Migration, we have filtering mechanism like while migrating it checks the whether data is meeting the condition or not. Can be configured between heterogenous databases like oracle, DB2, Teradata etc
What is the difference between log shipping and replication?
Log shipping is database level disaster recovery option while replication is object level high availability feature
SQL Server is one of the RDBMS products in the market. Every application requires one database server like Oracle, SQL Server, MySQL, HANA, DB2 etc. SQL Server is second most used database server in the world because of its features and functionalities. It is easy to navigate graphical user interface application, even layman can learn it easily.
Do you want to learn SQL Server?
Many topmost banks, ecommerce websites, corporate websites and many more websites are using the SQL Server as their database server because of license cost, security, backup and restore and high availability features.
If you learn SQL Server, there are plenty of opportunities in the market as a database developer and database administrator.
Every RDBMS has two parts.
Need of SQL Server:
If we want to perform day to day transactions, we need database server. Day to day transactions means insert, update, delete, select and these are called CRUD operations
Operating System is a software; it manages the hardware.
DBMS software manage database
Using DBMS software users can create and maintain databases.
Development of SQL Server consists of two parts.
Inside the Server we have to install software like SQL Server, in this we have to create objects like databases, tables, stored procedures, functions, triggers and cursors etc.
SQL Server follows the client-server architecture.
SQL languages used to interact with database servers.
Purpose of integrity constraints prevents users from entering invalid data.
Constraint means rule and integrity means data correctness. Rules to maintain data correctness or data integrity.
According to RDBMS integrity constraints are 3 types
Entity integrity (Unique, primary)
Domain integrity (Not Null, check, default)
Referential integrity (Foreign key)
According to SQL Server integrity constraints are
Built in functions in SQL Server:
A function is a block of code that accepts some input, perform some task and return a value.
SQL Server functions are categorized into 2 types
Single Row functions (scalar functions): These functions process single row and return one value per row.
Multi Row functions (Group functions)
Joins in SQL Server:
Join is a data retrieval operation. If we want to retrieve data from more than one table, then we use joins. In OLTP database tables are normalized, related data spread across into multiple tables, together data table need to be joined.
Sub Queries in SQL Server:
A query embedded in another query is called sub query or nested query.
One query is called outer/parent/main query.
Another query is called inner/child/sub query.
First Inner query is executed then result of inner query is input for outer query.
Outer query can be insert, update, delete, select.
Inner query must be always select.
Types of Sub Queries:
Single row sub query
Multi row sub query
Co-related sub query
Derived Tables: Sub queries in “From” clause are called derived tables.
Use Derived tables:
To access column aliases in where clause.
To use the result of one process in another process.
Views in SQL Server:
A view is a subset of a table, it is like virtual table, stored query.
A view includes specific rows and specific columns from table.
Views are created for security, Reduce complexity.
Views are 2 types
If view based on single table, then it is called simple view
Purpose of creating view is we want only specific data for the user not total data.
If we based on multiple tables or query contains group by clause, having clause, distinct, aggregate function then view is said to be complex view.
What is the difference between simple view and complex view?
Simple views are updatable (Allow DML operations), complex views are not updatable
Indexes are created to improve the performance of data access or data retrieval. Index improves performance of “SELECT” operation.
Parsing means checking syntax and semantic errors. Optimization means prepare different plans to execute query and select the best plan.
A synonym is another name or alternate name for a table.
Synonyms are created if table name is lengthy.
Difference between table alias and synonym?
The scope of the alias is up to the query only can not be accessed outside the query.
Synonyms can be accessed in any query.
Alias is not stored in database but synonym I stored in database.
Cursors in SQL Server:
Cursors are used to process multiple records, use cursors to do row-by-row processing.
Cursor is a name given to the context area. When user submits select statement to SQL Server, Server executes the select statement and returns data, data returned select statement is loaded into temporary memory are called context are.
Using cursor, we can give name to the context area. Using cursor, we can fetch one by one record from context area and process the record.
Stored Procedures in SQL Server:
A stored procedure is a named TSQL block, that accepts some input, performs some tasks and may or may not returns a value.
Stored procedures are created to perform one or more DML operations over Database.
Any real-life transaction implemented by using stored procedure. One stored procedure for one transaction.
Functions in SQL Server:
A function is a named TSQL block that accepts some input, performs some task and must return a value.
Functions are created to get values from database and to do calculations.
Triggers in SQL Server:
A trigger is a named TSQL block executed implicitly. Triggers are created to implement complex business rules and validations, to provide security and to do auditing.
SQL Server Administration:
Database administrator is a person responsible for the database design, implementation, maintenance, and repair of the database.
The main goal of DBA is to keep the database server always up and make it available to users. In case of any failures DBA should minimize data loss by implementing powerful backup and restoring techniques.
As a DBA we must perform these
Maintaining the availability of databases by minimizing the down time.
Data recovery, we must minimize the data loss in case of failures by implementing high availabilities.
Provide high security in accessing the databases externally.
Need to monitor the performance of Server, implement various techniques to increase the performance.
Regularly Monitor database growth, disk space, SQL Server logs, Event viewer logs to avoid issues and to identify any bottlenecks.
The primary responsibility of DBA is installation of software, depending on the requirement we have install the SQL Server instance in virtual machine or bare machine. There are two licensing models in SQL Server
Core based licensing
CAL license (Total Server license + each client license)
Backups in SQL Server:
Backup means exact copy of database.
Situations where backup can use
Hardware or software failure
Hardware failure includes operating system, CPU, Memory, network card failures
Software failure includes operating system failures, database server failures. We have one more failure called storage failure.
Natural disaster includes natural calamities like cyclones, fire attacks.
Internal misuse means some one in the organization intentionally or by mistake delete some data.
To recover lost data.
To minimize the data loss.
To minimize the down time.
Transactional backup (T log)
Tail log backup
File/file group backup
Copy only backup
Restoration is a process of using backups to recover the exist database or to create a new database in another server.
Situations for restoration:
Unplanned or accidental restoration.
Security in SQL Server:
Security is the most important tasks for a DBA. We need control the access to SQL Server as well as provide security to the data.
There are mainly 3 levels of security.
For providing security we have 3 processes:
Authentication: Validating credentials on server.
Authorization: Permissions to perform their operations.
Automation in SQL Server:
Automation refers to scheduling the regular maintenance tasks. Whenever schedule time comes automatically that task will be executed. DBA regular activities can be automated. We can get the status of the task in the form of alert and helpful while generating reports.
Automation can be done 2 ways:
Migration in SQL Server:
Migration means moving one or set of databases from one server to another server.
Scenarios for migration:
As a part of Launch, deployment, go live
Migrating databases from development to test and test to production, this is called as launch.
Whenever new builds released to production as a part of enhancement we call as deployment.
Whenever user can access the application online, we call as go-live.
Upgrading: Means Migrating SQL Server old version to new version.
Shipping transaction log files from primary server database to secondary server database to get both databases in continuous synchronization. It is a continuous process in the form of batches.
Database Mirroring in SQL Server:
Maintaining two synchronized copies of database is called mirroring. Secondary database will be in no recovery always. Only one copy of database is available to users at any time.
Replication in SQL Server:
Replication mainly used as a data migration technology although it is one of high availability option. The concept of replication is to migrate data an database objects from one server to another server.
Clustering in SQL Server:
Clustering is a windows level high availability option to increase the availability of physical server hardware, operating system, and SQL Server instances. If any of these 3 fails on one server, the other server in cluster automatically takes tasks of failed SQL Server instance
A windows failover cluster can support up to 16 nodes in SQL Server 2012. Cluster reduce down-time to minimum. Most clustering deployment is between 2 servers only. The clustered servers called as nodes.
Always ON in SQL Server:
Always ON is new high availability and disaster recovery feature in SQL Server 2012. Initially it named as HADRON (High Availability Disaster Recovery ON). It uses clustering technology for failover and mirroring in keeping multiple mirrored copies of the database. In Always ON we can failover multiple databased at the same time using Availability groups.
What is Log shipping in SQL Server?
Log shipping means Shipping transaction log files from the Primary Server database to one more secondary servers to get both databases in continuous synchronization.
SQL Server log shipping is a database level high availability feature, whenever primary database down due to any reason like natural calamity or software or hardware or human errors then we make the secondary database as primary database. In primary Server transaction log backups are taken and copied to network share from there these backups are copied to secondary server local share from there the backups are copied within specified time by the user. These all steps are being done by the backup job, copy job, restore job and alert job.
Transaction log contains every action is done by the users, this file is more important when ever any disaster happen, by using this only we can recover the database.
In SQL Server log shipping, Primary server database backup are being taken by backup job, in secondary server these backups are being copies and restored by copy job. Before configuring log shipping make sure the database is in full recovery or bulk recovery model.
How to check the Recovery Model:
Right click on Database -> go to properties -> select on options -> Here you can find recovery model of database.
If your database is not in full recovery model use below script:
alter database database-name set recovery full
Configuring SQL Server log shipping step by step:
First we need to enable the primary server database. Right click on database -> go to properties -> select transactional log shipping -> check the check box enable this as a primary database in log shipping configuration
Next click on Backup settings -> Mention where to take the backup either network share or local path, In my scenario I am using local share
In the below image delete files older than 72 hours means the older files will be deleted and if no backup occurs within 1 hour we will get the alert for not taking backup
click on the schedule button -> Now select when should this backup job has to run -> if you want you can change the backup name
click on ok. Next add secondary instance database
Add Secondary instance database then fill all the details like Initialize secondary database, copy files and restore setting tabs.
Active Directory: It maintains all servers, users information
Primary Server: Primary server contains a source database for sending the transaction logs.
Secondary Server: Secondary Server contains the destination database receiving the transaction logs.
Monitor Server: Monitor Server tracks information related to log shipping jobs and sends alerts in case of failure.
It depends mainly on 4 jobs:
Backup job: For Backup transaction logs on the primary server database, always run on the primary server.
Copy Job: It runs on the secondary server, and copies transaction logs from the primary server to the secondary server.
Restore job: Restore job run on secondary server to restore backups.
Alert job: It runs on the monitor server, if the monitor server is not configured then it stores in primary and secondary servers.
For Configuring log shipping, we require 4 servers and need backup share. There are 2 types
Remote or Network share
In local share, backup copies into primary server share whenever primary goes down data loss is more.
In network share, Backup copies to the network share, copy job of the secondary server goes to this share and copies backup to local share of the secondary server. We must grant proper permissions to service accounts to this network share folder on both servers.
Make sure the recovery model should be full.
Maintain the same hardware between both servers to avoid performance issues.
Service accounts running on both servers must have access to the network share where log backups are stored.
Keep secondary server always in no recovery.
Make sure the number of drives and name of drives should be the same on both servers.
How to configure log shipping?
Check the recovery model of the database.
Take full and transactional log backup of the primary server database and restore it to a standby server with standby or no recovery.
Configure linked servers.
Configuring log shipping :
Go to SSMS (SQL Server Management Studio) and take 3 instances
Note down the service account names
Create the folder in monitor server D Drive BakcupShare and grant read/write permissions to the service account.
In the primary server create one test database for instance bank and create one table
Take full backup
Go to the secondary server, create a local copy in some drive and grant read/write permissions to the service account and restore the database from the primary server to the secondary server with a standby option.
Configuring log shipping
Go to the primary server
Right-click on the bank database Tasks ship transaction log
Select checkbox “Enable this as primary database”
Click on backup settings and give the backup folder path Click on schedule, leave the default values
Only log shipping allows for keeping the secondary database in standby mode.
We can maintain multiple secondary databases.
No additional hardware or software is required.
More downtime is possible, we can not bring a secondary server immediately.
Data loss is possible, as jobs run every 15 minutes.
We must perform manual failover.
Need to perform several steps to bring a secondary server online which we call warm stand by.
Interview questions on log shipping in SQL Server:
What is a TUF file?
TUF stands for Transaction Undo file contains the modifications that were not committed on the primary database when transaction log backup was in progress and when a log was restored on the secondary database. When the next transaction restoring in the secondary server, SQL Server uses the TUF file and starts restoring incomplete transactions.
2. Can we take full backup?
A full backup can not truncate transactional log files so we can take a full backup.
3.What is the difference between log shipping and replication?
Log shipping is a database level high availability feature whereas replication is an object level high availability feature.
4.What is the difference between log shipping and mirroring?
There is no automatic failover in log shipping, but automatic failure is there in mirroring. There might be downtime and data loss is in log shipping but in mirroring synchronous commit mode no downtime and no data loss.
How do you failover in log shipping?
Disable all log shipping jobs on the primary and secondary servers.
Check whether we can take a tail log backup or not, if log files are available then only, we can perform a tail log backup.
Manually copy last backup and tail log backup from network share to secondary server.
Restore all available log backups with no recovery and the last log backup with recovery
Now the secondary database will become online.
Sync all logins and jobs
Find orphaned users and fix them.
Monitoring Log Shipping in SQL Server:
Right-click on SQL Server in SSMS, go to reports, select standard reports, and transaction log shipping status.
Triggers in SQL Server are mainly used for implementing complex business rules. A trigger is a named T-SQL block executed implicitly.
Triggers in SQL Server are created
To implement complex business rules and validations
To provide security
To do auditing
Business rules are of 2 types
Declarative rules – Implemented with the help of Integrity constraints. for instance minimum salary of employee should not greater than 10,000.
Procedural rules – Implemented with the help of triggers. Example for this is transaction has to be happening in banks between 10 to 5 pm from Monday to Friday, Saturday from 10am to 1pm and on Sunday transactions should not happen.
How to find Triggers in SQL Server?
Open the SSMS -> Expand the databases -> choose your database -> logship -> expand it -> under programmability -> database triggers
Purpose of Triggers in SQL Server:
Check Constraints and Triggers both will validate the data during DML operations.
Use Check constraint when the requirement is simple else use triggers.
Triggers do not support programming constructs like if else, loop etc.
Need for the Triggers in SQL Server:
Validations (Pre processing)
Post processing work (jobs)
Pre Processing ( Validations):
Complex Business Rules
Rules involving columns from more than one table
Rules involving old and new data in the same table.
DML (Insert, Update, Delete) operations in same table or different table.