Yugabytedb

A database transformation and migration project takes solid planning and testing. I’ve found that three common changes required when transforming a SQL Server database to YugabyteDB YSQL are related to syntax, performance, and stored procedures. These will get you started on your transformation project.

Syntax

Transforming a schema from MS SQL to YugabyteDB requires some minor syntax changes. This is true for any cross-database transformation. The YugabyteDB YSQL API utilizes PostgreSQL syntax.

Postgres and YugabyteDB allow you to define partitions of parent tables. Partitions are useful in at least two ways:

1. You can take advantage of partition pruning. The database doesn’t need to look at partitions it knows won’t meet the parameters of the query.
2. You can easily archive data by disconnecting and/or dropping partitions instead of managing expensive delete queries.

Here’s one gotcha I ran into recently. What happens if you insert a row into a partitioned table, but there’s no partition for it? The insert fails with an error – see below for a reproduction of this scenario.

I’ve been pretty quiet recently, I know. My youngest has been going through the college application phase, which has taken a lot of time for both of us. I wouldn’t give up all the lovely college visits and overnights, but I might easily part ways with the paperwork.

I do have something fun to share from the experience. I didn’t like the limitations of common college search websites. In particular, we were looking for a college in a subset of surrounding states. Most college search forms allow you to enter only a region, and the Southeastern region was too broad for her search. I also didn’t like that signing up for the sites subjected you to a lot of marketing.

I recently put together a platform to demo a handful of scenarios related to YugabyteDB cross-cluster replication.

The code is here: https://github.com/dataindataout/xtest_ansible

This works for Mac (Apple M1) and should work on later versions of Mac and Linux. Unsure if it will work on Windows.

You will need a copy of YugabyteDB (2.16 or 2.17, depending on which branch of the demo code you use). Note that xcluster functionality improves greatly at 2.17, so test at that version or beyond if you can.