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.

Always On in SQL Server

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

  1. Synchronous
  2. Asynchronous

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

  1. Availability group
  2. Availability replica
  3. Primary replica
  4. Secondary replica

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.

  1. Once we enabled Always on Availability Groups, we can create availability groups that contains all databases which we want to failover.
  2. 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.
  3. The wizard automatically creates end points to synchronize data and takes backup of primary replicas and restore them on secondary replicas.
  4. If we don’t want, we can do backup and restore manually.

Architecture of Always On in SQL Server

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

Advantages and Disadvantages of Always On in SQL Server

Advantages

  1. We can maintain multiple secondary databases.
  2. Secondary database used for read-only access, we can use for reporting purpose and backups.
  3. Protects data against disk failure.
  4. Automatic failover and group of databases failover as single unit.

Disadvantages

  1. Requires more disk space to store secondary replica data.
  2. Requires special setup configuration.
  3. Expensive to implement.

Leave a Comment

Your email address will not be published.

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

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

How clustering works
A single SQL server 2012 instance can run on only a single at a time.  In clustering the server on which SQL services running called as active node other server on which the service is not running called as passive node. If active node fails passive node becomes active and continues user requests of the server.  Active node access the database on a shared disk and sends Heartbeat signals over private network and passive node receive them. If active node stops passive node notices that Heartbeat stopped passive node assumes that the active node has failed and initiates a failover. Passive node now takes over the control of shared disk.

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.

Advantages

  • 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

Disadvantages

  • Clustering requires expensive hardware and software
  • Geographical dispersed clusters SAN hardware and software for data transfer

Architecture of Clustering

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

1.Active-Active cluster

2.active-passive cluster


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.

 Active-passive cluster

 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

  • virtual server.
  • nodes
  • shared disk
  • quorum
  • 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

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

Shared Disk:

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

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

Example

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.

  • node majority
  • node/disk majority
  • file share
  • no majority

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

MSDTC

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

Other Disaster Recovery option

Log shipping

Leave a Comment

Your email address will not be published.

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

High availability

We can maintain 2 servers, both databases are completely synchronized, whatever changes are being done on the primary server goes to the secondary server.

Scalability

Load distribution is possible in replication as both primary and secondary servers are available to users. We can split users among the 2 servers.

Migration

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 in SQL Server

Replication requires 3 servers and 3 databases along with it require articles, publications, and agents.

Publisher

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

Subscriber is a destination server whatever data coming it stores in subscription database

Distributor

Entire replication process carried in distribution server. It establish connection with publisher identifies data ready for replication copies data to subscriber.

  1. Local distributor

Publisher and distributor on same machine

2.Remote distributor

Publisher and distributor on different machines.

Articles

Articles are the database objects that being replicated.

Publication

Publications are collection of articles.

Agents

Agents are executables (jobs) used to perform the replication operations.

Replication process

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

  1. Replication model
  2. Data flow
  3. Latency

Replication models

  1. Publisher, subscriber, and local distributor
  2. Publisher, subscriber, and remote distributor
  3. Central publisher and multiple subscribers
  4. Multiple publisher and central subscriber
  5. Hybrid (combination of any 2 above models)

Data flow

We need get the details whether flow is one way or two way

One way

Data flows from publisher to subscriber

Two way

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

Latency is time interval, schedule the data movement either continuous or hourly or weekly.

Types of replication

  1. Snapshot replication
  2. Transaction replication
  3. Peer to peer replication
  4. Merge replication

Snapshot replication

Snapshot is going to make exact copy of an existing database; it is one time replication. 2 agents are created in snapshot replication.

  1. Snapshot agent

It perform snapshot generation

  • Distribution agent

Snapshot generation process

  1. Snapshot agent establishes connection between publisher and distributor.
  2. Snapshot agent identifies what are objects participated in replication process applies schema locks on those objects while copying structure.
  3. 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

Snapshot replication

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

  1. When source database has constant data
  2. When huge latency is accepted
  3. When we do not want changes in subscriber regularly.

Transactional Replication

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.

transactional replication

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.

peer to peer replication

Merge Replication

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.

Merge Replication

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

Leave a Comment

Your email address will not be published.

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.

SQL Server

Every RDBMS has two parts.

  1. Development
  2. Administration

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.

  1. SQL
  2. TSQL

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.

client server architecture in SQL Server

SQL languages used to interact with database servers.

  1. DDL (Data Definition Language): Create, Alter, Drop, truncate

If we want to create tables, alter tables, alter tables, drop tables and other objects we use DDL commands

2. DML (Data manipulation Language): Insert, Update, Delete

By using this we can insert, update, and delete actual data.

  • 3. DRL (Data Retrieval Language): Select

We fetch the data by using select.

  • 4. DCL (Data Control Language): Grant, Revoke, Deny

We can give access to the database objects.

  • 5. TCL (Transaction Control Language): Commit, Rollback, Savepoint.

Data types in SQL Server:

The type of the data that we store in memory is called data type.

  • Integer: tiny int, small int, int, big int.
  • Decimal: Allows numbers contain decimal
  • Currency: small money, money.
  • Data and time: small date time, date time, date, time.
  • String: char, varchar, nchar, nvarchar.
  • Binary: allows pictures up to 800 bytes.

Timestamp: A column declared with time stamp data type changes automatically whenever record is updated.

Unique identifier: Globally uniquely identify rows.

XML: used to store data in XML format.

Integrity Constraints:

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

  • Not null
  • Unique
  • Primary key
  • Check
  • Foreign key
  • Default

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.

  • Inner join
  • Outer join
  • Self join
  • Non-equi join
  • Cross join

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

  1. Simple view
  2. Complex view

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:

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.

Synonyms:

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.

DBA Responsibilities:

As a DBA we must perform these

  1. Maintaining the availability of databases by minimizing the down time.
  2. Data recovery, we must minimize the data loss in case of failures by implementing high availabilities.
  3. Provide high security in accessing the databases externally.
  4. Need to monitor the performance of Server, implement various techniques to increase the performance.
  5. Regularly Monitor database growth, disk space, SQL Server logs, Event viewer logs to avoid issues and to identify any bottlenecks.

Installation:

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

  1. Core based licensing
  2. CAL license (Total Server license + each client license)

Backups in SQL Server:

Backup means exact copy of database.

Situations where backup can use

  1. Hardware or software failure
  2. Natural disaster
  3. Internal misuse
Backup in SQL Server

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.

Backup objectives:

  1. To recover lost data.
  2. To minimize the data loss.
  3. To minimize the down time.

Backup types:

  1. Full backup
  2. Differential backup
  3. Transactional backup (T log)
  4. Tail log backup
  5. Mirrored backup
  6. Stripped backup
  7. File/file group backup
  8. Copy only backup

Restoration:

Restoration is a process of using backups to recover the exist database or to create a new database in another server.

Situations for restoration:

  1. Planned restoration
  2. 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.

  1. Server level
  2. Database level
  3. Object level

For providing security we have 3 processes:

  1. Authentication: Validating credentials on server.
  2. Authorization: Permissions to perform their operations.
  3. Encryption

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:

  1. Jobs
  2. Maintenance plans

Migration in SQL Server:

Migration means moving one or set of databases from one server to another server.

Scenarios for migration:

  1. 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.

Log Shipping in SQL Server:

Log Shipping:

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.

log shipping in SQL Server

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.

Leave a Comment

Your email address will not be published.

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.

How to Check the Recovery model of Database

If your database is not in full recovery model use below script:

use master
go
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

Enabling source database as a primary database

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

How to configure log shipping in SQL Server?

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

backup job schedule

click on the schedule button -> Now select when should this backup job has to run -> if you want you can change the backup name

backup schedule

click on ok. Next add secondary instance database

Secondary Instance
Click on Add button
Adding Secondary Instance
Adding Secondary instance

Add Secondary instance database then fill all the details like Initialize secondary database, copy files and restore setting tabs.

what is log shipping in sql server?

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

  1. Local share
  2. Remote or Network share
  1. In local share, backup copies into primary server share whenever primary goes down data loss is more.
  2. 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.

Prerequisites:

  1. Make sure the recovery model should be full.
  2. Maintain the same hardware between both servers to avoid performance issues.
  3. Service accounts running on both servers must have access to the network share where log backups are stored.
  4. Keep secondary server always in no recovery.
  5. Make sure the number of drives and name of drives should be the same on both servers.

How to configure log shipping?

  1. Check the recovery model of the database.
  2. Take full and transactional log backup of the primary server database and restore it to a standby server with standby or no recovery.
  3. Configure jobs.
  4. Configure alerts.
  5. Configure linked servers.
How to configure log shipping?

Configuring log shipping :

  1. Go to SSMS (SQL Server Management Studio) and take 3 instances
  2. Note down the service account names
  3. Create the folder in monitor server D Drive BakcupShare and grant read/write permissions to the service account.
  4. In the primary server create one test database for instance bank and create one table
  5. Take full backup
  6. 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.
  7. 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

Ok

Select the secondary database.

Ok.

Advantages and Disadvantages of log shipping:

Advantages:

  1. Only log shipping allows for keeping the secondary database in standby mode.
  2. We can maintain multiple secondary databases.
  3. No additional hardware or software is required.

Disadvantages:

  1. More downtime is possible, we can not bring a secondary server immediately.
  2. Data loss is possible, as jobs run every 15 minutes.
  3. We must perform manual failover.
  4. Need to perform several steps to bring a secondary server online which we call warm stand by.
Advantages and disadvantages of log shipping in sql server

Interview questions on log shipping in SQL Server:

  1. 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.

failover

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.

Leave a Comment

Your email address will not be published.

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

  1. To implement complex business rules and validations
  2. To provide security
  3. To do auditing

Business rules are of 2 types

  1. Declarative rules – Implemented with the help of Integrity constraints. for instance minimum salary of employee should not greater than 10,000.
  2. 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

Triggers in SQL Server

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:

  1. Validations (Pre processing)
  2. Post processing work (jobs)

Need :

Pre Processing ( Validations):

  1. Complex Business Rules
  2. Rules involving columns from more than one table
  3. Rules involving old and new data in the same table.

Post Processing(Job):

DML (Insert, Update, Delete) operations in same table or different table.

Types of Triggers in SQL Server:

DDL Triggers

DML Trigger

1. After or For

2. Instead of

types of triggers in SQL Server

Question) When does a trigger fire?

Answer) During DML operations (Insert, Update, Delete)

Can I call a trigger explicitly?

No

Note :- Trigger can not be created on a column, it can be created on table.

Truncate does not invoke trigger.

Syntax to create a trigger:

create trigger <trigger name>
on <table name>
<type><insert, update, delete>
as
begin
code
end
go

Magic Tables:

Magic Table created during trigger creation

During trigger creation 2 tables are created

  1. Inserted table :- Temporary table created by SQL Server. The record, user is trying to insert is copied to inserted table
  2. Deleted table:- The record, user is trying to delete is copied to deleted table. The record, user is trying to update is copied to both the tables.

After trigger:

Can be created only on tables

Multiple insert, update, delete triggers.

Instead of Trigger:

Can be created on tables and views.

Only one instead of trigger of a given type ( insert, update, delete)

This trigger gets fired instead of transaction.

Difference between stored procedure and tiggers?

Stored Procedure:

  1. Procedures invoke explicitly
  2. Procedure can be created without a table.
  3. Procedures accepts parameters
  4. Procedures can return values
  5. Procedures can be invoked from frond end
  6. create procedure for performing DML operations

Triggers:

  1. Triggers invoked implicitly.
  2. Triggers can not be created without a table.
  3. Triggers will not accept parameters
  4. Triggers can not return values
  5. Triggers can not be invoked from front end
  6. Create trigger to control DML operations.
Difference between stored procedure and trigger

Instead of triggers are created to update complex views.

Normally simple views are updatable, complex views are not updatable but with the help of instead of triggers we can update complex views.

Leave a Comment

Your email address will not be published.