Configure Backup on Secondary Replica. AlwaysOn Hish Availability Groups provides Backup on Secondary database to overcome load on Primary Database. As Secondary database is Read-Only replica so you can’t execute any code directly, you need to create Maintenance Plan for backup. AlwaysOn High Availability Groups only Provides “Copy-Only Backup” at Secondary replica, it means differential [...]
AlwaysOn High Availability Manually Failover In my previous blog i have written on AlwaysOn High Availability Automatic Failover (From Server1 to Server2) and now in this blog i will show you AlwaysOn High Availability Manually Failover (Back from Server2 to Server1) Step1 : After Automatic Failover Server2 is working as Primary Replica and Server1 is Secondary [...]
AlwaysOn High Availability Automatic Failover Always on High Availability provides automatic failover up to 2 secondary server while you configure Availability Group and Server2 is selected as Automatic Failover Replica without any data loss. Note: If you are frequently restart your server or SQL Server service then this option is not advisable. See the current scenario. [...]
Configure Availability Group Listener. Availability Group Listener is a Virtual Network Name which provides client connectivity for Availability Group database on primary or secondary available replica. In our scenario Server1 is PrimaryServer, Server2 SecondaryServer with automatic failover. Here i am going to create Availability Group listener with name “AGSample” which points to Server1, in any [...]
Configure AlwaysOn High Availability This configuration has three servers Server1 (Primary Replica), Server2 (Secondary Replica Synchronous), Server3 (Secondary Replica Asynchronous) Step1: Enable AlwaysOn High Availability Group on SQL Serer Instance on which you want to configure. Open SQL Server configuration right click on your SQL Server Instance and select “AlwaysOn High Availabilty” tab. this tab [...]
Create tables schema to another database using SMO. I have created a package which will create table script to your physical path and also executes same thing on another database to create table schema.
Script to Shrink Database. You need to use Database Console Commands of SQL Server to get back the free space. DBCC ShrinkDatabase : Shrink all files from database. It will shrink 90% free space and remains 10% free for database files. It will shrink data and log files to the last allocated extent, while you use [...]
SQL Server Mirroring with Certificate. Step1 : Take a backup of Database and then backup of log as well, of the database which you want mirrored. On Principal Server Step2 : Copy this both database and log backup from principal server to mirroring server and restore it in specific drive. On Mirroring Server Step3 : [...]
Identity reseed in SQL Server 2012 In SQL Server 2012, there is one issue regarding Identity column. I have faced identity value is jumping by 1000 when i have restarted my SQL Server Service. You can find gaps in identity while you restart your sql server service, restart of server or failover. Please review below code to [...]
Configure Service Broker in SQL Server 2012. Initiator Script Step1 : Create Database, Master Key and Initiator Certificate Step2 : Create Target Certificate, Login and Endpoint Step3 : Create User, Master Key, Initiator and Target Certificate Step4 : Create Message Type, Contract, Queue, Service, Route and Binding Target Script Step1 : Create Database, Master Key [...]