Snippet Name: SQL_AAG_Version_Upgrade

Tags:

Created Date: 2025-06-16 13:12:49
Last Modified Date: 2025-05-31 10:32:11

Dependencies:

DBA Notes:

Sample Output:

Title: SQL AAG Version Upgrade
Author: user
with an existing SLQ2016 AAG pair of servers, can i add a new SQL2019 node to the AAG and fail over

Author: ChatGPT
No, you **cannot** directly add a SQL Server 2019 node to an existing SQL Server 2016 Always On Availability Group (AAG). SQL Server Always On Availability Groups require all replicas to be running the **same major version** of SQL Server.

### Why?
1. **Version Compatibility:** SQL Server AAG does not support mixed major versions within the same availability group. All nodes must be on the same version and edition to ensure data consistency and failover capability.
2. **Upgrade Process:** To upgrade to SQL Server 2019, you'll need to follow a specific upgrade path.

### Recommended Approach:
If you want to upgrade the AAG to SQL Server 2019, follow these steps:

#### 1. **Upgrade Strategy:**
   - **Method 1: Rolling Upgrade Approach (Minimal Downtime)**
     1. **Remove one secondary replica** (SQL Server 2016).
     2. Upgrade it to **SQL Server 2019**.
     3. Add it back to the AAG (it will synchronize with the SQL 2016 primary).
     4. Fail over to the upgraded SQL 2019 replica.
     5. Upgrade the remaining SQL 2016 nodes to SQL 2019.
   
   - **Method 2: Side-by-Side Upgrade (More Control)**
     1. Build a new SQL Server 2019 cluster.
     2. Configure log shipping or replication from SQL 2016 to 2019.
     3. Cutover applications to the new SQL 2019 AAG after thorough testing.
   
#### 2. **Prerequisites to Consider:**
   - Ensure the **compatibility level** of your databases supports SQL Server 2019.
   - Review deprecated features and breaking changes before upgrading.
   - Test all critical workloads in a non-production environment.

#### 3. **Failover Considerations:**
   - AAG failover is **not possible** from a higher version (SQL 2019) to a lower version (SQL 2016).
   - After failover to SQL Server 2019, you cannot revert to SQL Server 2016.

#### 4. **Backup Compatibility:**
   - Backups from SQL 2019 **cannot** be restored to SQL 2016.
   - Make sure you have a rollback plan if needed.

Would you like detailed steps on a rolling upgrade or side-by-side migration?

Author: user
changing the subject, in sql server's extended_properties, can i create an extended preoprt about a specific login

Author: ChatGPT
No, you **cannot** directly add an extended property to a SQL Server **login**, because **extended properties** in SQL Server are only supported for the following objects:

1. **Databases**  
2. **Schemas**  
3. **Tables**  
4. **Columns**  
5. **Indexes**  
6. **Stored Procedures**  
7. **Views**  
8. **Functions**  
9. **Triggers**  
10. **Assemblies**  

SQL Server logins are server-level objects and do **not** support extended properties directly. Extended properties can only be added to database-level objects.

### Workarounds:
If you need to store metadata related to SQL Server logins, consider these alternatives:

#### 1. **Create a dedicated database table**
   Store additional login information in a custom table within a user database or `master` database.

   ```sql
   CREATE TABLE dbo.LoginMetadata (
       LoginName NVARCHAR(100) PRIMARY KEY,
       PropertyName NVARCHAR(100),
       PropertyValue NVARCHAR(255)
   );

   INSERT INTO dbo.LoginMetadata (LoginName, PropertyName, PropertyValue)
   VALUES ('myLogin', 'Department', 'Finance');
   ```

#### 2. **Use system tables/comments**
   Store notes in the `sys.server_principals` system table by joining it with a custom metadata table.

   ```sql
   SELECT sp.name, sp.type_desc, lm.PropertyValue
   FROM sys.server_principals sp
   LEFT JOIN dbo.LoginMetadata lm ON sp.name = lm.LoginName
   WHERE sp.type = 'S'; -- 'S' for SQL logins, 'U' for Windows logins
   ```

#### 3. **Using `sp_addextendedproperty` on the database level**
   While you cannot attach extended properties to logins, you can document them at the **database level**, referencing login-related data in the description.

   ```sql
   EXEC sp_addextendedproperty 
       @name = 'LoginMetadata_JohnDoe',
       @value = 'JohnDoe - Administrator Account for reporting.',
       @level0type = 'DATABASE', 
       @level0name = 'YourDatabase';
   ```

#### 4. **Utilizing SQL Server Audit or Notes in Documentation**
   Consider maintaining login-related notes in external documentation or using SQL Server Auditing to track login-related changes.

---

Would you like more details on implementing one of these solutions?