Posts

Script to add database in existing availability group by Tsql.

This is the script to include database in existing Always on availability group by passing below parameters.

1. Primary server name, port number

2.Secodnary server name , port number

3. Database name

4.Availability group name

5.Shared path on primary server with read and write access for dba id required to take backup from primary server for HADR configuration.

6. This script to be executed in SQLCMD only.


--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. :Connect Primary-Server_Name,5008
USE [master]
GO
ALTER AVAILABILITY GROUP [Availability group NAME] ADD DATABASE [Database Name];
GO
:Connect Primary-Server_Name,Port_Number
BACKUP DATABASE [Database Name] TO  DISK = N'\\Primary-Server_Name\Always_on_Backup \Database Name.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO
:Connect Secondary-Server_Name,Port_Number
RESTORE DATABASE [Database Name] FROM  DISK = N'\ \Primary-Server_Name\Always_on_Backup\Database Name.bak' WIT…

Steps to apply patches in Always on availability group replica servers

How do apply patch in always on availability group replica servers?

1. Make sure to connect the instances using 'Multisubnetfailover=TRUE' as this required to perform failover.

2.perform the patch apply in the secondary server replica .

3.Reboot the secondary server replica and perform health check .

4.Ensure databases are in synchronising mode in the secondary server replica .

-- select * from sys. Dm_hadr_database_replica_states.

Just check the synchronisation_state_desc and synchronisation_health_desc  column value for health values.

5.If Always on is configured in Asynchronous mode , then make sure to change it to Synchronous mode to avoid data loss.( Revert back to asynchronous mode once all steps for patch completed in both servers).

Alter  availability group group_name
Modify replica on 'server name '

With

({ availability _mode =synchronous_commit|asynchronous_commit})

6.Failover the Always on group to the Secondary server replica.

Use master
Go
Alter availabil…

Resume data movement for availability group databases # Resume HADR

while I noticed that on the secondary replica there was a pause icon next to the Availability Databases. The primary had shown both were "green", but there was an option on the secondary to Resume Data Movement. I resumed the first database, and immediately the In Recovery status message was removed. 

A minute later it changed from Not Synchronizing to Synchronized, and everything worked as expected.
Note you can also use TSQL on the secondary to resume replication on multiple database at the same time:

ALTER DATABASE [Patch] SET HADR RESUME; ALTER DATABASE [test] SET HADR RESUME;
GO

Always on availability group -HADR Questions &Answers

What about a sync mode secondary replica of always on availability group goes offline for 3 hours and come back online it self?

Answer:

Once the secondary replica server goes offline ,data movement between replica would not happen and hence the transaction log on primary grows and keep on accumulating transactions.
This would cause log file to grow;So please make sure to you have enough space in place for the log file drive in the primary replica server.

Once secondary server comes online , always on group will synchronise itself to make sure primary and secondary data in SYNC Without data loss.

No need to resume manually Always on availability session .MANUAL RESUME is used in manual/ planned failover.

Take a Snapshot in the vSphere Client.#Using Snapshots To Manage Virtual Machines

Image
Here are the steps to make a snapshot.
Snapshots capture the entire state of the virtual machine at the time you take the snapshot. You can take a snapshot when a virtual machine is powered on, powered off, or suspended. If you are suspending a virtual machine, wait until the suspend operation finishes before you take a snapshot.
Enter Virtual server name or ip address, then enter the credentials to connect to Vsphere client.



In Virtual Machines, find virtual server name, right-click on it.  Go to Snapshot > Take Snapshot





Inventory>Virtual Machine>Snapshot>Take Snapshot.


When you click on Take Snapshot.  Fill out a name, and description, and UNCHECK “Snapshot the virtual machine’s memory”.


Adding a date and time or a description, for example, "Snapshot before applying XYZ patch," can help you determine which snapshot to restore or delete.
This will create a snapshot of the virtual machine.
After you take the snapshot, you can view its status in the Recent Tasks f…

Steps to apply Windows Updates on Windows Server 2003.#Windows Updates

Image
Steps to update Windows Patch in the windows server 2003 version:
             1)Take Remote Desktop Program to the windows Server.
             2)In Internet Explorer, go to Safety > Windows Update



             3)On the Windows Update page, click Custom              4)Install all High Priority and Software updates. And then click “Install Updates” button on the page.
           You will have to restart the server several times and run windows update again since new updates may depend on other updates.  So just keep rebooting and running Windows Update  until there are no more updates available.  Also some updates can only be installed alone.





SQL SERVER Upgrade: SQL2012 features

Image
Time to upgrade your old SQL server to SQl2012:
Why we consider the SQL Server to upgrade from old version?
SQL2012 consists of important features that are not available in the old versions.

Nice features of SQL Server 2012.
1. Contained databases help to avoid the database user’s creation in the destination server after migration.
2. Server Core support significantly reduces the patching.
3. Security-User defined server role helps to create custom defined server role in SQL 2012 server.
4. Column store index assists in storing of the data as like column instead of old row level storage to improve the data access performance.
5. Tabular model mode to help personal BI users to achieve their work in excel to SSAS.
6. Data Quality Service to help BI users to implement the standards and quality of data in SSAS.
7. Power View helps end users to drag and drop the required field for analysis and report purposes in BI.
8.AlwaysOn Availability Groups helps to failover the databases as a single entity, …