Forem: Kapil Uthra

AWS Migration Series: Database Migration Service Overview

Kapil Uthra — Sat, 11 Sep 2021 04:44:06 +0000

AWS Database Migration Service (AWS DMS) is a cloud service that makes it easy to migrate relational databases, data warehouses, NoSQL databases, and other types of data stores. You can use AWS DMS to migrate your data into the AWS Cloud or between combinations of cloud and on-premises setups.

AWS DMS is a server in the AWS Cloud that runs replication software. You create a source and target connection to tell AWS DMS where to extract from and load to. Then you schedule a task that runs on this server to move your data.

How AWS DMS Works?

AWS Database Migration Service (AWS DMS) is a web service that you can use to migrate data from a source data store to a target data store. These two data stores are called endpoints. You can migrate between source and target endpoints that use the same database engine, such as from an Oracle database to an Oracle database. You can also migrate between source and target endpoints that use different database engines, such as from an Oracle database to a PostgreSQL database. The only requirement to use AWS DMS is that one of your endpoints must be on an AWS service. You can't use AWS DMS to migrate from an on-premises database to another on-premises database.

At a high level, when using AWS DMS you do the following:

Create a replication server.
Create source and target endpoints that have connection information about your data stores.
Create one or more migration tasks to migrate data between the source and target data stores.

A task can consist of three major phases:

The full load of existing data
The application of cached changes
Ongoing replication

AWS DMS loads data from tables on the source data store to tables on the target data store. While the full load is in progress, any changes made to the tables being loaded are cached on the replication server; these are the cached changes.

When the full load for a given table is complete, AWS DMS immediately begins to apply the cached changes for that table. Once the table is loaded and the cached changes applied, AWS DMS begins to collect changes as transactions for the ongoing replication phase.

At the start of the ongoing replication phase, a backlog of transactions generally causes some lag between the source and target databases. The migration eventually reaches a steady state after working through this backlog of transactions. At this point, you can shut down your applications, allow any remaining transactions to be applied to the target, and bring your applications up, now pointing at the target database.

Limitations:

AWS DMS creates the target schema objects necessary to perform the migration. However, AWS DMS takes a minimalist approach and creates only those objects required to efficiently migrate the data. In other words, AWS DMS creates tables, primary keys, and in some cases unique indexes, but doesn't create any other objects that are not required to efficiently migrate the data from the source. For example, it doesn't create secondary indexes, nonprimary key constraints, or data defaults.

AWS DMS doesn't perform schema or code conversion. If you want to convert an existing schema to a different database engine, you can use AWS SCT. AWS SCT converts your source objects, table, indexes, views, triggers, and other system objects into the target data definition language (DDL) format. You can also use AWS SCT to convert most of your application code, like PL/SQL or TSQL, to the equivalent target language.

In the next part we will discuss about AWS SCT (Schema Conversion Tool) for heterogeneous migrations.

AWS Migration Series: DataSync Overview

Kapil Uthra — Sat, 28 Aug 2021 05:38:59 +0000

AWS DataSync is an online data transfer service that simplifies, automates, and accelerates moving data between on-premises storage systems and AWS storage services, and also between AWS storage services. DataSync can copy data between Network File System (NFS), Server Message Block (SMB) file servers, self-managed object storage, Amazon Simple Storage Service (Amazon S3) buckets, Amazon EFS file systems, and Amazon FSx for Windows File Server file systems.

Data transfer between self-managed storage and AWS

Data transfer between AWS storage services

How DataSync works?

To understand the science behind DataSync, we need to understand few key components.

Agent An agent is a VM that you own that is used to read or write data from self-managed storage systems. The agent can be deployed on VMware ESXi, KVM, Microsoft Hyper-V hypervisors, or it can be launched as an Amazon EC2 instance.
Location Any source or destination location that is used in the data transfer (for example, Amazon S3, Amazon EFS, Amazon FSx for Windows File Server, NFS, SMB, or self-managed object storage).
Task Consists of a source location and a destination location, and configuration that define how data is transferred. Configuration settings can include options such as how to treat metadata, deleted files, and permission.
Task execution An individual run of a task, which includes information such as start time, end time, bytes written, and status.

Workflow

In the LAUNCHING status, DataSync initializes the task execution.
In the PREPARING status, DataSync examines the source and destination file systems to determine which files to sync.
In the TRANSFERRING status, DataSync starts transferring files and metadata from the source file system to the destination.
In the VERIFYING status, DataSync verifies consistency between the source and destination file systems.

Source: AWS Documentation

Caching Overview

Kapil Uthra — Tue, 24 Aug 2021 09:15:04 +0000

In computing, a cache is a high-speed data storage layer which stores a subset of data, typically transient in nature, so that future requests for that data are served up faster than is possible by accessing the data’s primary storage location. Caching allows you to efficiently reuse previously retrieved or computed data.

Areas where caching can exist:

Client-Side - HTTP Cache Headers, Browsers
DNS - DNS Servers
Web - HTTP Cache Headers, CDNs, Reverse Proxies, Web Accelerators, Key/Value Stores
App - Key/Value data stores, Local caches
Database - Database buffers, Key/Value data stores

Common thoughts while working with caching:

Is it safe to use a cached value? The same piece of data can have different consistency requirements in different contexts. For example, during online checkout, you need the authoritative price of an item, so caching might not be appropriate. On other pages, however, the price might be a few minutes out of date without a negative impact on users.
Is caching effective for that data? Some applications generate access patterns that are not suitable for caching—for example, sweeping through the key space of a large datasets that is changing frequently. In this case, keeping the cache up to date could offset any advantage caching could offer.
Is the data structured well for caching? Simply caching a database record can often be enough to offer significant performance advantages. However, other times, data is best cached in a format that combines multiple records together. Because caches are simple key-value stores, you might also need to cache a data record in multiple different formats, so you can access it by different attributes in the record.

Caching design patterns:

Lazy caching

Your app receives a query for data, for example the top 10 most recent news stories.
Your app checks the cache to see if the object is in cache.
If so (a cache hit), the cached object is returned, and the call flow ends.
If not (a cache miss), then the database is queried for the object. The cache is populated, and the object is returned.

Write-through

In a write-through cache, the cache is updated in real time when the database is updated. So, if a user updates his or her profile, the updated profile is also pushed into the cache. You can think of this as being proactive to avoid unnecessary cache misses, in the case that you have data that you absolutely know is going to be accessed. A good example is any type of aggregate the top 10 most popular news stories, or even recommendations. Because this data is typically updated by a specific piece of application or background job code, it's straightforward to update the cache as well.

Points to be considered

Always apply a time to live (TTL) to all of your cache keys, except those you are updating by write-through caching. You can use a long time, say hours or even days. This approach catches application bugs, where you forget to update or delete a given cache key when updating the underlying record. Eventually, the cache key will auto-expire and get refreshed.

For rapidly changing data rather than adding write-through caching or complex expiration logic, just set a short TTL of a few seconds. If you have a database query that is getting hammered in production, it's just a few lines of code to add a cache key with a 5 second TTL around the query. This code can be a wonderful Band-Aid to keep your application up and running while you evaluate more elegant solutions.

Evictions occur when memory is over filled or greater than max memory setting in the cache, resulting into the engine to select keys to evict in order to manage its memory. The keys that are chosen are based on the eviction policy that is selected.

allkeys-lfu: The cache evicts the least frequently used (LFU) keys regardless of TTL set
allkeys-lru: The cache evicts the least recently used (LRU) regardless of TTL set
volatile-lfu: The cache evicts the least frequently used (LFU) keys from those that have a TTL set
volatile-lru: The cache evicts the least recently used (LRU) from those that have a TTL set
volatile-ttl: The cache evicts the keys with shortest TTL set
volatile-random: The cache randomly evicts keys with a TTL set
allkeys-random: The cache randomly evicts keys regardless of TTL set
no-eviction: The cache doesn’t evict keys at all. This blocks future writes until memory frees up.

For basic caching use-cases, LRU-based rules are more prevalent, however depending on your objectives, you may choose to deploy a TTL or Random-based eviction policy if that better matches your needs. Higher Evictions usually implies that for cache cluster/node we might need a larger memory footprint.

Source: AWS Documentation

Migrate an on-premises Microsoft SQL Server database to Amazon RDS via Amazon S3

Kapil Uthra — Sun, 22 Aug 2021 03:36:45 +0000

This migration process includes making a backup and restoring the backup in an Amazon Simple Storage Service (Amazon S3) bucket and using SQL Server Management Studio (SSMS).

This process includes following major steps:

Making a backup of on-premises database via SSMS (SQL Server Management Studio)
Transferring that backup file in Amazon Simple Storage Service (Amazon S3)
Finally, restoring the backup file in Amazon RDS for SQL Server.

Create an Amazon RDS for Microsoft SQL Server DB instance

Select SQL Server as the database engine in Amazon RDS for SQL Server.
Choose the required SQL Server Edition

Create a backup file from the on-premises Microsoft SQL Server database

Connect to the on-premises SQL Server database through SSMS.
Create a backup of the database.

Upload the backup file to Amazon S3

Create a bucket in Amazon S3.
Upload the backup file to the S3 bucket.

Restore the database in Amazon RDS for SQL Server

Open the Amazon RDS console
Choose Option groups in the navigation pane.
Choose the Create group button.
Add the SQLSERVER_BACKUP_RESTORE option to the option group
Add the option group to Amazon RDS for SQL Server.
Connect to Amazon RDS for SQL Server through SSMS.
Call the rds_restore_database stored procedure to restore the database.

More details on Restoring a database

As mentioned above for restoring the database in RDS call the rds_restore_database stored procedure. Amazon RDS creates an initial snapshot of the database after the restore task is complete and the database is open.

exec msdb.dbo.rds_restore_database @restore_db_name='database_name', @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name.extension', @with_norecovery=0|1, [@kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id'], [@type='DIFFERENTIAL|FULL'];

Example of single file restore:

exec msdb.dbo.rds_restore_database @restore_db_name='mydatabase', @s3_arn_to_restore_from='arn:aws:s3:::mybucket/backup1.bak';

Example of multifile restore:

exec msdb.dbo.rds_restore_database @restore_db_name='mydatabase', @s3_arn_to_restore_from='arn:aws:s3:::mybucket/backup*';

Check restoring task status:

exec msdb.dbo.rds_task_status [@db_name='database_name'], [@task_id=ID_number];

Example:
exec msdb.dbo.rds_task_status @db_name='mydatabase';

Source: AWS Documentation