Migrating an SQL Server database to AWS RDS Aurora PostgreSQL

 Migrating an SQL Server database to AWS RDS Aurora PostgreSQL 

Step 1: Planning

1. Assess the Migration: Evaluate the source SQL Server database and identify any potential issues. Consider schema differences, data types, and compatibility issues.
2. Backup Strategy: Plan for a backup strategy to ensure you have a point-in-time restore option.
3. Tools and Resources: Familiarize yourself with AWS Database Migration Service (DMS) and AWS Schema Conversion Tool (SCT).

Step 2: Set Up AWS Environment

1. Create an AWS Account: If you don’t already have one, create an AWS account.
2. Set Up IAM Roles and Policies: Ensure you have the necessary IAM roles and policies to manage AWS services securely.
3. Launch Aurora PostgreSQL Instance:
   • Go to the RDS console.
   • Select "Create Database".
   • Choose "Amazon Aurora".
   • Select "PostgreSQL-compatible".
   • Configure the instance size, storage, and other settings.
   • Launch the instance.

Step 3: Schema Conversion

1. Install AWS SCT:
   • Download and install the AWS Schema Conversion Tool from the AWS website.
2. Connect to Source SQL Server:
   • Open AWS SCT.
   • Connect to your SQL Server database by providing the connection details.
3. Connect to Target Aurora PostgreSQL:
   • Connect to your Aurora PostgreSQL instance.
4. Convert the Schema:
   • Use AWS SCT to convert the SQL Server schema to PostgreSQL-compatible schema.
   • Review and apply any necessary modifications manually.
   • Apply the converted schema to the Aurora PostgreSQL instance.

Step 4: Data Migration

1. Install AWS DMS:
   • Go to the AWS DMS console.
   • Create a replication instance.
   • Ensure the replication instance can connect to both the source SQL Server and target Aurora PostgreSQL.
2. Create Endpoints:
   • Create source endpoint for SQL Server.
   • Create target endpoint for Aurora PostgreSQL.
3. Create a Migration Task:
   • Define a migration task in AWS DMS.
   • Choose the type of migration (full load, full load + CDC, or CDC only).
4. Run the Migration Task:
   • Start the migration task.
   • Monitor the migration process using the DMS console.
   • Validate data after the migration task completes.

Step 5: Post-Migration

1. Data Validation:
   • Compare the data in the source SQL Server and target Aurora PostgreSQL to ensure completeness and accuracy.
2. Application Testing:
   • Test your applications with the new Aurora PostgreSQL database to ensure they work as expected.
3. Performance Tuning:
   • Optimize your PostgreSQL database settings for better performance.
   • Apply necessary indexing and query optimizations.

Step 6: Cutover

1. Plan for Downtime:
   • Schedule a maintenance window for the cutover to minimize impact.
2. Final Data Sync:
   • Perform a final data sync if using CDC (Change Data Capture) to ensure no data is missed.
3. Switch Applications:
   • Update your application configurations to point to the new Aurora PostgreSQL database.
4. Monitor:
   • Monitor the applications and database closely after cutover to quickly address any issues.

Step 7: Decommission

1. Decommission Old SQL Server:
   • Once confirmed that the new system is working perfectly, decommission the old SQL Server database.
2. Cleanup:
   • Remove any unused resources in AWS to avoid unnecessary costs.

Conclusion

Migrating from SQL Server to AWS RDS Aurora PostgreSQL requires careful planning and execution. Using tools like AWS SCT and AWS DMS can simplify the process, but manual intervention and thorough testing are crucial to ensure a smooth transition.

Overview of the Azure SQL Architecture:

Azure SQL Database is a fully managed relational database service provided by Microsoft Azure. Its architecture is designed to provide scalability, high availability, and security for hosting relational databases in the cloud.

Azure Data Centers: Azure SQL Database runs on Microsoft's global network of data centers, which are distributed across multiple regions worldwide. These data centers provide the physical infrastructure for hosting Azure SQL Database instances and ensure high availability and fault tolerance.

SQL Database Engine: At the core of Azure SQL Database is the SQL Database Engine, which is based on the same SQL Server database engine used in on-premises deployments. The engine manages database operations, query processing, data storage, and security functions.

Managed Service Layer: Azure SQL Database is a fully managed service, which means that Microsoft handles infrastructure management, maintenance, and monitoring tasks. The managed service layer includes features such as automated backups, patching, high availability, and disaster recovery to ensure optimal performance and reliability.

Elastic Scaling: Azure SQL Database offers elastic scaling capabilities, allowing databases to scale compute and storage resources dynamically based on workload demand. Users can adjust the performance tier (e.g., Basic, Standard, Premium, Serverless) and storage size of their databases without downtime to accommodate changing requirements.

Security Features: Azure SQL Database includes built-in security features to protect data from unauthorized access, such as Transparent Data Encryption (TDE), Always Encrypted, Dynamic Data Masking (DDM), Row-Level Security (RLS), and Azure Active Directory (Azure AD) authentication. These features help organizations enforce data privacy and compliance with regulatory requirements.

High Availability and Disaster Recovery: Azure SQL Database ensures high availability and disaster recovery through features such as automatic backups, geo-replication, and automatic failover. Automated backups allow users to restore databases to any point in time within the retention period, while geo-replication replicates databases asynchronously to secondary regions for disaster recovery purposes.

Integration with Azure Services: Azure SQL Database integrates with other Azure services such as Azure Key Vault, Azure Monitor, Azure Resource Manager, and Azure Active Directory for enhanced security, monitoring, management, and automation capabilities.

Compatibility with SQL Server: Azure SQL Database offers near-complete compatibility with SQL Server, including support for T-SQL, stored procedures, triggers, views, and data types. This enables seamless migration of existing SQL Server workloads to Azure SQL Database with minimal changes.

Side-by-side migration in the context of SQL Server using Backup and restore

 Side-by-side migration in the context of SQL Server using Backup and restore 

                                                                                                            Download

Capture all SQL and Stored Proc calls using Extended Events in SQL Server

-- Capture all SQL and Stored Proc calls

-- Part of the SQL Server DBA Toolbox at https://github.com/DavidSchanzer/Sql-Server-DBA-Toolbox

-- This script creates an Extended Events session called "CaptureAllSQLAndStoredProcCalls" that includes the following events:

-- error_reported (for severity 20 and above, as well as certain error numbers)

-- existing_connection

-- login

-- logout

-- rpc_completed

-- sql_batch_completed

CREATE EVENT SESSION [CaptureAllSQLAndStoredProcCalls]

ON SERVER

    ADD EVENT sqlserver.error_reported

    (ACTION

     (

         package0.callstack,

         sqlserver.client_app_name,

         sqlserver.client_hostname,

         sqlserver.database_name,

         sqlserver.nt_username,

         sqlserver.session_id,

         sqlserver.session_nt_username,

         sqlserver.sql_text,

         sqlserver.tsql_stack,

         sqlserver.username

     )

     WHERE (

               [severity] >= (20)

               OR

               (

                   [error_number] = (17803)

                   OR [error_number] = (701)

                   OR [error_number] = (802)

                   OR [error_number] = (8645)

                   OR [error_number] = (8651)

                   OR [error_number] = (8657)

                   OR [error_number] = (8902)

                   OR [error_number] = (41354)

                   OR [error_number] = (41355)

                   OR [error_number] = (41367)

                   OR [error_number] = (41384)

                   OR [error_number] = (41336)

                   OR [error_number] = (41309)

                   OR [error_number] = (41312)

                   OR [error_number] = (41313)

               )

           )

    ),

    ADD EVENT sqlserver.existing_connection

    (ACTION

     (

         package0.event_sequence,

         sqlserver.client_hostname,

         sqlserver.session_id

     )

    ),

    ADD EVENT sqlserver.login

    (SET collect_options_text = (1)

     ACTION

     (

         package0.event_sequence,

         sqlserver.client_hostname,

         sqlserver.session_id

     )

    ),

    ADD EVENT sqlserver.logout

    (ACTION

     (

         package0.event_sequence,

         sqlserver.session_id

     )

    ),

    ADD EVENT sqlserver.rpc_completed

    (SET collect_statement = (1)

     ACTION

     (

         package0.event_sequence,

         sqlserver.client_app_name,

         sqlserver.client_hostname,

         sqlserver.database_name,

         sqlserver.nt_username,

         sqlserver.session_id,

         sqlserver.session_nt_username,

         sqlserver.sql_text,

         sqlserver.tsql_stack,

         sqlserver.username

     )

     WHERE ([package0].[equal_boolean]([sqlserver].[is_system], (0)))

    ),

    ADD EVENT sqlserver.sql_batch_completed

    (ACTION

     (

         package0.event_sequence,

         sqlserver.client_app_name,

         sqlserver.client_hostname,

         sqlserver.database_name,

         sqlserver.nt_username,

         sqlserver.session_id,

         sqlserver.session_nt_username,

         sqlserver.sql_text,

         sqlserver.tsql_stack,

         sqlserver.username

     )

     WHERE ([package0].[equal_boolean]([sqlserver].[is_system], (0)))

    )

    ADD TARGET package0.event_file

    (SET filename = N'c:\temp\CaptureAllSQLAndStoredProcCalls.xel')

WITH

(

    MAX_MEMORY = 16384KB,

    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,

    MAX_DISPATCH_LATENCY = 5 SECONDS,

    MAX_EVENT_SIZE = 0KB,

    MEMORY_PARTITION_MODE = PER_CPU,

    TRACK_CAUSALITY = ON,

    STARTUP_STATE = OFF

);

GO 

Here are key points about the buffer cache in SQL Server:

    The buffer cache in SQL Server is a part of the SQL Server memory architecture that is responsible for caching database pages in memory. When SQL Server reads data from a disk, it stores a copy of that data in the buffer cache. Subsequent queries that need the same data can then be served from the in-memory buffer rather than reading from disk, which is significantly faster.

Here are key points about the buffer cache in SQL Server:

Buffer Pool:

The buffer cache is often referred to as the "buffer pool" or "data cache." It is a region in the SQL Server memory space dedicated to storing data pages.

Pages and Extents:

SQL Server divides its storage into fixed-size pages (usually 8 KB). These pages are grouped into larger structures called extents. The buffer cache holds these pages in memory.

Data Access:

When a query needs data, SQL Server first checks if the required pages are already in the buffer cache. If the data is present, it's called a "cache hit," and the data can be retrieved quickly from memory. If not, it's a "cache miss," and the data must be read from disk.

Read-Ahead Mechanism:

SQL Server uses a read-ahead mechanism to anticipate and pre-fetch pages into the buffer cache before they are needed. This helps to minimize the impact of physical I/O on query performance.

LRU (Least Recently Used) Algorithm:

The buffer cache uses an LRU algorithm to manage the contents of the cache. When the cache becomes full, pages that haven't been used recently are candidates for removal to make room for new pages.

Dirty Pages and Checkpoints:

When modifications are made to data in the buffer cache, the modified pages become "dirty." SQL Server periodically writes these dirty pages back to the data files during a process called a checkpoint. This ensures that changes are persisted to disk.

Monitoring Buffer Cache:

Performance monitoring tools and DMVs (Dynamic Management Views) can be used to monitor the state of the buffer cache. For example, the sys.dm_os_buffer_descriptors view provides information about the pages currently in the buffer cache.

SELECT * FROM sys.dm_os_buffer_descriptors;

Configuring Buffer Cache:

SQL Server provides configuration options for the size and behavior of the buffer cache. The "max server memory" configuration option limits the amount of memory that SQL Server can use for the buffer cache.

sp_configure 'max server memory', <value>;

The buffer cache plays a crucial role in optimizing SQL Server performance by reducing the need to perform expensive disk I/O operations. Properly configuring and monitoring the buffer cache is important for maintaining optimal database performance.