Choosing a High-Availability Solution You have been hired to set up the network and servers for a new company, using Windows Server 2012 R2. The company is investing in an application critical to the company’s business that all employees will use daily. This application uses a back-end database and is highly transaction oriented, so data is read and written frequently. To simplify maintenance of the front-end application, users will run it on remote desktop servers. The owner has explained that the back-end database must be highly available, and the remote desktop servers must be highly scalable to handle hundreds of simultaneous sessions. What high-availability technologies do you recommend for the back-end database server and the remote desktop servers? Explain your answer.

We have following high availability solutions available in sql server:-

Logshiping:It is the process of automating backup of database and transaction logfiles on a server and then restoring on standby server.Through diagram below,logshipping can be understood properly.

Primary database is the database which needs to be available.It is available on primary server as the name says.Secondary database is available on secondary server where the all the transaction logfile will be restored to make the database in sync.This ca be chosen for high availability solution in OLTP environment.

It is very easy to setup.Just by using GUI,it can be established.

We have norecovery mode and stand by mode.Database on secondary server is not available if chosen no recovery mode.Stand by mode is chosen to use database for reading purpose when it is not being restored.

We can have multiple secondary server.On one server database can be used for reporting purpose and can be used for other purpose on another server.

It requires low maintance.

Mirroring:It is also a high availability solution which makes database available on secondary server.Below is the diagram showing mirroring:-

It increases availability of a database.

It increases data protection

It increases availability of database on production server during upgrades.

We have two modes high performance and high safety.In hgh performance mode, database mirroring session operates asynchronously and uses only the principal server and mirror server. The only form of role switching is forced service (with possible data loss).

In high safety mode,the database mirroring session operates synchronously and, optionally, uses a witness, as well as the principal server and mirror server.

Replication:It is also a high availability solution.It can copy database from one server and transfer the data on another server and keep the database in sync.We have different type of replication like snapshot,transaction,merge,peer to peer.Below diagram can be used to understand replication:-

In replication,we can transfer various database objects like table,views,sp's etc.Even we can transfer database object from sql server to oracle.

Transaction replication is used when data changes very frequently and we want database to be transferred immediately. Snapshot is used when database changes less frequently and it requires to be replicated periodically.Merge is used when changes on publisher as well as subscriber can be replicated.

Other than that,we have alwaysOn and clustering.In alwaysOn, we have primary replica and secondary replicas.Clustering setup is required in alwaysOn but storage is not required on SAN.It is really good,if we want to restrict one server to take backup and other for reporting purpose.

In clustering,we have common storage which is shared by all nodes in cluster.So if a node fails,other nodes are available and there would not be much impact as database files are available on separate server.

Explanation:

See attached picture.