Data File Movement in SQL Server Always On

Here NODE1 is primary replica and NODE2 is secondary

In the primary replica write the below commands to move the files logically. DON’T EXECUTE this step  for nowjust write it down
 USE master;
GO
ALTER DATABASE A
MODIFY FILE (NAME = A, FILENAME = 'C:\DataAndLog\A.mdf');
GO
ALTER DATABASE A
MODIFY FILE (NAME = A_Log, FILENAME = 'C:\DataAndLog\A_log.ldf');
GO

ALTER DATABASE B
MODIFY FILE (NAME = B, FILENAME = 'C:\DataAndLog\B.mdf');
GO
ALTER DATABASE B
MODIFY FILE (NAME = B_Log, FILENAME = 'C:\DataAndLog\B_log.ldf');
GO
ALTER DATABASE C
MODIFY FILE (NAME = C, FILENAME = 'C:\DataAndLog\C.mdf');
GO
ALTER DATABASE C
MODIFY FILE (NAME = C_Log, FILENAME = 'C:\DataAndLog\C_log.ldf');
GO
ALTER DATABASE D
MODIFY FILE (NAME = D, FILENAME = 'C:\DataAndLog\D.mdf');
GO
ALTER DATABASE D
MODIFY FILE (NAME = D_Log, FILENAME = 'C:\DataAndLog\D_log.ldf');
GO


2)      In the primary replica Availability group databases initiate SUSPEND DATA MOVEMENT on an ALL REPLICA and ALL the databases.   First run the below query on primary replica


ALTER DATABASE A
SET HADR SUSPEND
ALTER DATABASE B
SET HADR SUSPEND
ALTER DATABASE C
SET HADR SUSPEND
ALTER DATABASE D
SET HADR SUSPEND

Observer  the behavior of databases in Primary(NODE1) and Secondary replica (NODE2)availability group databases









3) Now suspend data movement on Secondary replica(NODE2). And see the database status and behavior in primary and secondary replicas.






4) Logically move all the files in all the replicas, here we have two replicas NODE1 and NODE2 now you can run 1 st step(data file movement query) here in both replicas one after another. First, I ran that in the primary replica

5)     Before you are running the same query(1st step Query(datafile movement))Change the secondary replica properties to NO otherwise, you will get below error.

Msg 5004, Level 16, State 4, Line 19
To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.

6)   Run the 1 step queries here again in secondary replica and you can see the below success message

  The file "A" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "A_Log" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "B" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "B_Log" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "C" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "C_Log" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "D" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "D_Log" has been modified in the system catalog. The new path will be used the next time the database is started.

7)   Stop PRIMARY REPLICA(NODE1) SQL services and move the files physically. Do the same in secondary replica and move the files to respective drives

8)   After moving the files physically in both replicas initiate RESUME data movement in both the replicas and on all the databases.

ALTER DATABASE A

SET HADR RESUME
ALTER DATABASE B
SET HADR RESUME
ALTER DATABASE C
SET HADR RESUME
ALTER DATABASE D
SET HADR RESUME




x

Comments

Post a Comment

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more

Pre login Handshake or Connection Timeout Period

Image
Service was unable to open new database connection when requested. SqlException: Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=16225; handshake=14840; Connection string - Data Source=sqlserverintancenamehere;Initial Catalog=CatalogNamehere;Integrated Security=True;Persist Security Info=False;Max Pool Size=1000;Connect Timeout=20;Load Balance Timeout=120;Packet Size=4096;Application Name=Applicationname hereBusinessLayerHost.exe;Workstation ID=HostnameHere setspn -a MSSQLSvc/hostname.doaminname.net:1433 ServiceAccountNameHere need to run the above command in a command prompt, most of the time you would NOT have permissions to run this, please check with Active Directory Team or Wi...
Read more

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server

Image
/*  There is only one master key, you can not create  multiple */ use master go CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPasswordHere' GO /*  Creating certificate in the master database, after creating  master key */ use master GO CREATE CERTIFICATE MyFirstCert WITH SUBJECT = 'TDECertificate' ; GO /* Enable databse encryption key but you can not see under this specific user database. */ USE G GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyFirstCert GO ALTER DATABASE G SET ENCRYPTION ON;  /*  Backing up certificate */ use master go BACKUP CERTIFICATE MyFirstCert TO FILE = 'C:\Certificates\MyFirstCert' WITH PRIVATE KEY ( FILE = 'C:\Certificates\MyFirstCertKey' , ENCRYPTION ...
Read more