Side-by-side SQL Server Always-On cluster upgrade using Log Shipping

 Log Shipping 1st Run:

1. Transfer SQL Server logins from the old SQL Server 2012&2016 to the new SQL Server 2022 primary and secondary servers.
2. Migrate SQL Server Agent jobs from the old SQL Server 2012&2016 to the new SQL Server 2022 primary and secondary servers.
3. Transfer Linked Servers (if any) from the old SQL Server 2012$&2016 to the new SQL Server 2022 primary and secondary servers.
4. Disable existing Transaction-Log Backup jobs on the old SQL Server 2012&2016 to prevent interference with Log Shipping.
5. Establish a shared folder as the log shipping backup file destination path, ensuring it has read-write permissions for the SQL service account and is accessible by the new SQL Server 2022 servers.
6. Configure Log Shipping from the primary AG replica of the old SQL Server 2012&2016 to both the primary and secondary AG replicas of the new SQL Server 2022:
1. Perform a compressed full database backup on the primary AG replica of the old SQL Server 2012&2016.
2. Copy the backup file to both the primary and secondary AG replicas of the new SQL Server 2022.
3. Restore the backups on the new SQL Server 2022 servers using the WITH NORECOVERY option.
4. Enable Log Shipping on the databases in the primary AG replica of the old SQL Server 2012/&2016.
5. Configure the log shipping jobs, selecting the created shared folder as the backup path.
6. Add both the primary and secondary AG replicas of the new SQL Server 2022 as the Log Shipping destination servers.
7. Verify the Log Shipping status using SQL Server 2022 standard reports --> Transactional Log Shipping Status.
8. Repeat the above steps for each application database.

Bring SQL Server 2022 Database Online for Application Testing:

1. Disable Log Shipping on the old SQL Server 2012&2016.
2. Re-enable existing log backup jobs on the old SQL Server 2012&2016 that were disabled during the first run of Log Shipping.
3. Bring databases online in the primary replica of the new SQL Server 2022 (RESTORE LOG <db> WITH RECOVERY).
4. Configure Always-On Availability Group between the primary and secondary replicas of the new SQL Server 2022.

Log Shipping 2nd Run:

1. Disable existing Transaction-Log Backup jobs on the old SQL Server 2012&2016 to prevent interference with Log Shipping.
2. Remove the Always-On Availability Group in the new SQL 2022 servers.
3. Configure Log Shipping from the primary AG replica of the old SQL Server 2012&2016 to both the primary and secondary AG replicas of the new SQL Server 2022:
1. Perform a compressed full database backup on the primary AG replica of the old SQL Server 2012&2016.
2. Copy the backup file to both the primary and secondary AG replicas of the new SQL Server 2022.
3. Restore the backups on the new SQL Server 2022 servers using the WITH NORECOVERY option.
4. Enable Log Shipping on the databases in the primary AG replica of the old SQL Server 2012&2016.
5. Configure the log shipping jobs, selecting the created shared folder as the backup path.
6. Add both the primary and secondary AG replicas of the new SQL Server 2022 as the Log Shipping destination servers.
7. Verify the Log Shipping status using SQL Server 2022 standard reports --> Transactional Log Shipping Status.
8. Repeat the above steps for each application database.

Switch-over:

1. Terminate all client connections to the old SQL Server 2012&2016 by disabling SQL logins.
2. Execute LS-Backup jobs on the old SQL Server 2012&2016.
3. Execute LS-Copy and LS-Restore jobs on the new SQL Server 2022 servers.
4. Disable Log Shipping on the old SQL Server 2012&2016.
5. Bring databases online in the primary replica of the new SQL Server 2022 (RESTORE LOG <db> WITH RECOVERY).
6. Configure Always-On Availability Group between the primary and secondary replicas of the new SQL Server 2022.
7. Estimated switch-over time: 15 minutes.

Capturing T-SQL Anti-Pattern using Extended Event

SQL Server 2022 introduces the query_antipattern Extended Event (XEvent), identifying anti-patterns in SQL queries sent to the server. An anti-pattern here is code that the SQL Server optimizer struggles to optimize effectively but cannot fix automatically. The description for the event (from sys.dm_xe_objects dmv) states that it “occurs when a query antipattern is present and can potentially affect performance.” Queries with antipatterns can severely impact performance by increasing resource consumption.

Including this event in a XEvent session helps us identify potential application issues early in the SDLC (Software Development Life Cycle). If a query contains certain antipatterns, they are detected during optimization. When this happens, and the query_antipattern event is part of an active XEvent session, the output will capture relevant fields. This allows for quick identification of queries that need tuning.

Query optimization antipatterns captured by the query_antipattern event include:

• Column data type conversion prevents index seek (implicit type conversion)
• Non-optimal OR clauses
• Large IN clauses
• Many OR predicates

Let's have an example and see how it works.

1. Open SSMS > connect to a SQL Server 2022 > Management > Extended Events > right-click Sessions > New Session...

2. Enter a Session Name, you can also tick the checkboxes of Start the event session at server startup and Start the event session immediately after session creation.

3. Go to "Events" page, under Event library enter "antipattern", then click on the "query_antipattern" row to highlight it, you will see the description and the event fields of this event.

4. Click the right arrow ">" button to add the query_antipattern event to the Selected events list, click on it, then click the Configure button.


5. In the "Global fields (Actions)" tab, tick the checkboxes of "plan_handle" and "sql_text".


6. Go to "Data Storage" page, choose "event file" type, enter the file path and file name, set maximum file size and rollover. Click OK button to create the event session.


7. The session is created. Right-click the event file under the created session, click View Target Data...


8. You can see there are some events being captured. Click on an event to show the details. Under the Details, you can see the antipattern type (a "local" event field), plan_handle and sql_text (both are Global fields). 


9. Double-click on plan_handle to open a new window, click Copy All button to copy the plan handle.


10. Open a new query window, run the following query with the plan_handle value:

-- Get Execution plan by plan handle
USE master
GO
SELECT DB_NAME(dbid), * FROM sys.dm_exec_query_plan (<plan_handle value>);

11. Inside the Results tab, click on the query_plan XML to show the graphical execution plan.

Notes:

If you run the exact same query literal, it will not generate a new antipattern event. This is because an antipattern event is detected during query optimization rather than execution. 

Azure Data Studio Free Extension - Schema Compare

Before Azure Data Studio, SQL Server developers could only use third party SQL comparison tool such as Redgate SQL Compare to compare and synchronize SQL Server database schemas between two databases either in the same server instance or remote instances. Starting from Azure Data Studio (ADS), there is a FREE ADS extension Schema Compare provides an easy-to-use way to compare two database definitions and apply the differences from the source to the target. This includes active database connections, dacpac files, and SQL projects.

After this extension is enabled, right-click the source database under CONNECTIONS > SERVERS, then choose Schema Compare.

Click the [...] button besides Target to select the target database:

Enter the Target Server name and Database, then press OK.

Press the Compare button to start the schema comparison:

After the comparison is completed, the comparison result will be shown. Then you can press the "Generate script" button to generate the deployment script.