Change the Default Collation of SQL server, Database 

I had installed a SQL server with default collation SQL_Latin1_General_CP1_CI_AS but later realized it should be SQL_Latin1_General_CP850_CI_AS. I followed the below steps. 

1. Change SQL Server (Instance) Collation

⚠️ You cannot change the server collation without rebuilding the system databases, which means reinstalling SQL Server components.

Steps:

• Backup all user databases and settings

• Use setup.exe from installation media with REBUILDDATABASE flag

Example Command:

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=domain\user /SAPWD=StrongPassword /SQLCOLLATION=New_Collation_Name

🔁 Example collation: SQL_Latin1_General_CP1_CI_AS

🧷 This will remove all system data, including logins and jobs. Use with caution.

INSTANCENAME= MSSQLSERVER for Default Instance

              ServerName\Instance for named instance

SQLSYSADMINACCOUNTS = Must provide windows account

SAPWD = Its going to be new password for 'SA' account.

SQLCOLLATION = New collation to be changed

2. Change Database Collation

You can change a database's default collation only if no tables exist, or you'll have to recreate all objects manually.

Example (for empty or new DB):

ALTER DATABASE YourDatabaseName COLLATE New_Collation_Name;


3. Change Column Collation
To change collation for specific columns:
ALTER TABLE YourTable
ALTER COLUMN YourColumn VARCHAR(100) COLLATE New_Collation_Name;
⚠️ This can fail if the column is part of an index or constraint.

4. Specify Collation in Queries
For joining or comparing across different collations:

SELECT * FROM TableA JOIN TableB ON TableA.Name 
COLLATE Latin1_General_CI_AS = TableB.Name COLLATE Latin1_General_CI_AS; 

Resolving Disconnected Secondary Replica or Database in Always On Availability Groups

It’s not uncommon to encounter situations where either the secondary database or the secondary replica enters a disconnected state in an Always On Availability Group (AG). This typically occurs during initial AG configurations or after events like OS/SQL patching, network issues, or failovers.

Recently, I faced a scenario where not only was the secondary database in a disconnected state, but the entire secondary replica was also marked as disconnected in the AG dashboard. As a result, the transaction log (T-log) started growing due to unsynchronized data movement.

Below are the steps I followed to troubleshoot and resolve the issue:

Steps to Resolve:

1. Connected to both Primary and Secondary replicas via SSMS.

2. On the Secondary node, navigated to:
   Always On High Availability > Availability Groups (AVG) > [Your AG Name]

3. Under Availability Databases, right-clicked the affected database, then:
   Suspend Data Movement > Resume Data Movement

   Note: This did not resolve the issue.

4. Restarted the HADR Endpoint on both replicas:

   
   SELECT * FROM sys.endpoints;
   
   ALTER ENDPOINT [Hadr_endpoint] STATE = STOPPED;
   ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
   

   This also did not resolve the issue initially.

5. Granted CONNECT permissions to the SQL Server service account (used by the secondary replica) on both nodes:

   
   GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\SQLServiceAccount];
   

6. After applying the endpoint permission, I re-executed Step 4 (restarting endpoints).
   This time, the secondary replica successfully reconnected, and data synchronization resumed.