TempDB Database On a Local Disk for an FCI

Link Below:

TempDB Database On a Local Disk for an FCI

This article will be helpful in configuring TempDB on a local Disk instead of shared storage in SQL Server 2012/2014. This will reduce the I/O requests from the shared storage and overall it can improve performance.

Backup and Restore Strategies in SQL Server : A Brief Introduction

Establishment of a successful business requires the proper maintenance of the data. SQL Server is one the popular platform for the same. In SQL Server, a proper strategy has been introduced for the management of data. The Main reason of introducing SQL Server backups and restore strategies in SQL Server is to enable the users to regain the database, which is corrupted. However, restoring and backing up of data must be done in particular environment and must work with the resources that are available. This is the reason users need a reliable strategy to backup and restore the database in SQL Server.

Webinar: Performance Troubleshooting Using Wait Statistics by Paul Randal

Hi Folks, 

Join Webinar on Webinar: Performance Troubleshooting Using Wait Statistics. Tue 3/07/2017 from 10:30 PM to 11:45 PM IST

Click [Here] to join

Step By Step Logshipping configuration

Log shipping provides a means to maintain a secondary server on an automated basis
using a chain of transaction log backups. This chapter explains the basic confi guration of
log shipping along with considerations for configuring the failover and failback within a log
shipping environment.

In this article, I will show you a step by step procedure to configure Log-shipping.

Primary Database
The primary database is accessible to applications and accepts transactions. Transaction
log backups are taken on a periodic basis and copied to the server hosting the secondary
database.

Secondary Database
The secondary database, also referred to as the standby, is normally inaccessible, and
transaction log backups from the primary database are restored on a continuous basis.
The secondary database can be in two different modes: Standby Mode or No Recovery
Mode. When the secondary database is in Standby Mode, users can connect to and issue
SELECT statements against the database. When the secondary database is in No Recovery
Mode, users cannot connect to the database. In either mode, you can restore transaction
logs to the secondary database. You cannot restore transaction logs when users are
connected to the database, so you should not use Standby Mode for high-availability
architectures.

Monitor Server
The monitor server, which is optional within a log shipping architecture, contains a set of jobs
that send alerts when the log shipping session is perceived to be out of sync.

How to enable Log-shipping between two SQL instances in DC and DR site.

Step 1:

Take full backup and log backup of LSDB database in primary instance (SQL1) and restore backups to secondary instance (SQL2) in standby mode.

Open Windows Explorer on the primary and create a share named LS_Primary
Grant Full Control permissions on this share to the SQL Server service account on
the primary as well as Read permissions to the SQL Server Agent service account on
the secondary





Step 2:

Open Windows Explorer on the secondary and create a share named LS_Secondary.
Grant Full Control permissions on this share to the SQL Server service account and the
SQL Server Agent service account on the secondary.

Step 3;

Verify that the database is configured for either the Full or Bulk-logged recovery model. If it is not, change the recovery model to Full. Start SSMS, connect to Primary instance (SQL1) within Object Explorer, right-click the database (LSDB) and choose Properties. Below Select A Page, select Transaction Log Shipping.

Step 4:

Select the Enable This As A Primary Database In A Log Shipping Configuration check
box and click Backup Settings. In the Network Path To Backup Folder text box, enter the Universal Naming Convention (UNC) path to the share you created in step 1. The Backup Folder Is Located On The Primary Server text box, enter the physical path to the directory in which your backups will be stored. Click OK to close the Transaction Log Backup Settings dialog box.

Select schedules

Delete files older than                                    72 hours

Alert if no backups occurs with in                  45 minutes



Step 5:

Click Add to add a new secondary. Click Connect and connect to SQL2; leave the name of the secondary database set to LSDB. You can select No, the secondary database is initialized as we have already restored full backup of database in standby mode in secondary instance.
selected.

Step 6:

Click the Copy Files tab. Set the destination folder that the transaction log backups will be copied to and change the copy interval as per your requirement

.

Step 7:

Click the Restore Transaction Log tab. Verify that the Standby mode is selected, set delay alert
as per your requirement. Click OK to close the Secondary Database Settings dialog box.
Click OK to generate the log shipping configuration.







Run back up job on primary SQL instance and copy, restore jobs in secondary instance. Create the log shipping jobs and alerts, and start log shipping. Verify that backups are going to the correct folder, are copied from LS_Primary to LS_Secondary correctly, and are restored to SQL2.

Step 8:

Right click Primary and Secondary instance, Select reports and click transaction log-shipping status.

This will show you Log-shipping sync status for database between two instances.

Everything on Always on Availability Groups series

Step By Step configuring Windows 2012 R2 fail over cluster How to setup windows cluster for Always on Availability Groups

AlwaysOn Availability Groups: Step by Step Setup Step by Step procedure to setup Always on Availability Groups

Configure Read only routing for an always on availability group

Always on troubleshooting: 

http://mssqllover.blogspot.in/2017/04/the-connection-to-primary-replica-is.html

Step By Step configring Windows 2012 R2 fail over cluster

One of my client requirement is to install Always on availability groups for SQL databases. You don't require shared storage to setup Always on availability. it can be configured in local disks.
AlwaysOn Availability Groups rely on the Windows Server Failover Cluster for failure detection and management of the Availability Group replicas.

Prerequisites required to install Windows 2012 R2 failover cluster feature.

• Get the operating system installed, patched and configured on all participating nodes
• See either Windows Update or an internal Windows Server Update Services (WSUS) server to get all of the required Windows Updates downloaded and installed
• All the participating servers should be added to active directory
• Domain user account must have administrator rights on the computers that are becoming part of the new cluster and the Create Computer Objects permission on the container where computer accounts are created in the domain.
• Ensure the password not temporary and there is complex password
• Both OS Editions, Versions should be at same level on all participating nodes
• You need to install 3.5.1 or greater on all participating nodes
• Make sure you have a separate NIC's for public and private communication
• Make sure you have free IP for windows cluster

How to configure Windows 2012 R2 Failover cluster

Step 1:

Failover Clustering feature has to be installed onto each cluster node
Open server manager and select Add Roles and Features. Next, Add Roles and Features Wizard and Select the Failover Clustering feature. click Next and install.

Step 2:

Validate your hardware configuration to make sure that it meets the requirements for building a failover cluster. Launch failover cluster manager console and click on the Validate Configuration option, found in the Actions pane. This will cause Windows to launch the Validate a Configuration Wizard.





Step 3:

Testing Options dialog box, make sure that the option Run only tests I select. Click Next.

Step 4:

Exclude add eligible storage cluster check box and click Next.




In the Confirmation dialog box, click Next.

click Next. This will run all the necessary validation tests.

In the Summary dialog box, click Finish to create the Windows Failover Cluster.

Step 5:

The length of time that the tests take to complete varies depending on the number of servers in your cluster and on your hardware's performance.

the test results may result few warnings. However, if you see any Error messages, you need to fix those first prior to creating the Windows Server Failover Cluster.




Step 6:

Launch the Create Cluster Wizard. Click Next and In the Select Servers dialog box, enter the host names of the nodes that you want to add as members of your cluster and click Next.

Step 7:

In the Access Point for Administering the Cluster dialog box, enter the virtual hostname and IP address that you will use to administer the cluster. Click Next




Step 8:

In the Confirmation dialog box, Exclude add eligible storage cluster check box and click Next. This will configure Failover Clustering on both nodes of the cluster.

There is no need to use shared storage to create the Windows Server Failover Cluster that we will use for our Availability Group.

Step 9:

In the Summary dialog box, verify that all the report returns successful.

In below screenshot, AONTESTWFCS01 cluster is successfully setup on SQL1 and SQL2 nodes.