Who Will Use SQL Server on Linux?

This past weekend I was having a discussion on IRC with someone about the role of SQL Server on Linux, and who might actually use the database engine over many of the more traditional, open alternatives like PostreSQL and MySQL. The argument essentially boiled down to this: my opponent said that "nobody in their right mind who wants to use SQL Server would migrate from Windows to Linux", and I said that "SQL Server on Linux isn't for people who already have systems built to use SQL Server installations on Windows, but for people who haven't even started their projects yet". Both of us are likely correct to a certain degree, and both of us are likely over-generalising by using terms like "everyone", "most", and "developers".

I've been using Microsoft's SQL Server on an Ubuntu Server installation since it was first made available this past summer, and I've got to admit that it's a solid little engine. SQL Server has long been my favourite database, and I tend to use others primarily because my servers are all Linux-based. MySQL is the go-to, but does it have to be for smaller projects?

I don't think so.

So with this in mind, I plan on proving to my weekend opponent that SQL Server on Linux is a viable solution by migrating 10Centuries from MariaDB 10.1 to SQL Server 2017. This won't be a simple drop-in replacement, as the two systems do things differently enough that it makes zero sense to hammer a square peg into a round hole. Instead, I plan on taking the examining system and designing a proper replacement that'll play to SQL Server's strengths and finally move all of the data rules from the API layer to the database, where it actually makes sense.

10Centuries is very much a relational system, and it should be perfectly reliable on SQL Server Web despite all the licensing limitations that Microsoft has in place for that edition of the engine. If there's any change at all, it will likely be that 10C is faster after the database change. There will certainly be some performance reports after the fact. If I can drop API response time down by 500ms without spending a small fortune on more hardware (or software licenses), I'll be very, very happy.

Umm … Why?

A little over ten years ago I had an MCDBA certification from Microsoft that revolved around SQL Server 2005. Those skills are now well over a decade out of date, and I'd really like to get back into using this more powerful tool. By using SQL Server on Linux, I can gain experience with the system and also begin to understand the various things that the tool can and cannot do. I'm years away from becoming an expert, this should be a good first step down that road.

SQL Server can certainly be a great tool for people who prefer to use Linux and want to build or update their software. Just because it may not fit every use case doesn't mean it can't fill others.

Master of None

Over the last year or so I've been investing a great deal of time to improve my knowledge of both Microsoft SQL Server and MySQL. One is the greatest database engine that's ever existed1, and the other is certainly adequate for many tasks with some caveats. As with many things in life, the more we learn about a specific subject, the less we feel we know about that subject. The vast absence of knowledge stretching before me has me wondering whether I'll ever truly know either of these tools well enough to master them.

The chasm by Kevin Carden

In just 11 months I've read 8 SQL Server and 4 MySQL books that have really opened my eyes to how these tools have evolved since I was last working with databases in any meaningful way. Sure, I've used these tools a lot over the last decade, but I've never really had to deep-dive into the subjects in order to provide better support for the engines. With some recent issues at the day job, I feel there is no other option than to absorb as much knowledge in order to take on the role of DBA, as there hasn't been one inside the organization for quite some time. What I've come to learn through this activity is that a lot of what I've previously been taught as "best practice" is now anything but, and it has me second-guessing a lot of the software I've built over the years. This has me wondering if any of the other "best practices" that I've been following are outdated, outmoded, or just plain wrong.

For almost ten years I set aside software development and database work in order to earn a paycheck here in Japan, where my personality type is generally not welcome within a typical Japanese company. A great deal has changed in that time. The question I need to answer now is how to apply this newfound knowledge without essentially scrapping the work that's been done up to now on both personal and professional projects.


  1. in my opinion, anyways

I Need To Be Chris

Between 2002 and 2007, I worked at a medium-sized company in Canada that was best known for its calendars and other print materials. I started in the warehouse and, over the course of 3 years, moved into different roles that culminated in a position as a software developer and worked with a number of very smart people who taught me a lot about software development, and a lot about how to ask the right questions to find out what people want the software to do, rather than making the wrong assumptions and delivering something that isn't at all what they're looking for. The person I learned from the most, however, was a man named Chris1.

Chris had a rather wide range of knowledge on just about every technical subject, no matter how obscure the tools might have been. His knowledge on certain subjects would often run circles around others, even when it was their area of focus. And, while he most certainly did complain when he was called in to fix somebody else's problem, he tried to make education part of the solution. There really isn't any point being "the only person who knows X" in a company, because that doesn't benefit anybody in the long run2. The guy seemed to know everything he needed and then some, and was honest enough to say "I don't know" when he really didn't know right before investigating whatever needed to be learned so that he wouldn't answer the same question the same way later.

I learned a lot from him in the two years or so we worked together, and would be happy to work with him again if the opportunity arose.

The way Chris handled situations was often incredibly efficient, and it's something I really need to work on myself. The last few weeks at the day job have been incredibly stressful as I attempt to do four very different tasks simultaneously in order to deliver a project that should have started limited trials back in August. I've recently complained that I shouldn't be doing four very different tasks if bugs and enhancements are going to be resolved by arbitrary deadlines, but complaining about reality will rarely resolve the problems one faces.

I've been incredibly fortunate over the last two decades to have worked with a lot of very different technologies and worked in a lot of very different roles. This sort of make me a little like Chris, in that I can look at a problem from different angles, apply lots of experience to find a solution or — at the very least — know how to find a solution, and have the capacity to do it without necessarily asking for a great deal of help. What I need to learn is how to make common distractions from various groups into learning experiences rather than seeing them as work blockages. When people have questions about databases, I need to guide rather than brusquely answer. When people have questions about X over Y, or the alternatives to Z, I need to outline the gist and provide some basic links to sites with more in-depth answers. The people I work with are not fools. They genuinely want to do a good job and go home knowing they accomplished something, and this is the same goal I have at the end of every day. The question I have now, though, is how to do this without coming across as dismissive or as though I'm "mansplaining"3 something.

Having spent the better part of 8 years working in a classroom, you'd think this would be natural. That said, the teacher-student dynamic doesn't work with peers, nor do I want to have that dynamic with my colleagues. So how does one turn a work-stoppage into a learning opportunity while also meeting all of the arbitrary and constantly shifting deadlines that managers are all to happy to create?


  1. He had a last name, too, but I'll just use his first one here.

  2. Seriously. You don't want to be the person to receive a 3:00am phone call when things go bad … especially if it's with something that isn't technically your responsibility.

  3. I hate this pseudo-word like you wouldn't believe … but it seems to be part of the lexicon, now.

Setting Your TimeZone in CentOS

Just Like Setting a WatchYesterday I shared how to set up master-master replication in MySQL Server, and today I thought I'd quickly share something that is just as (if not more) important: configuring the timezone in CentOS.

I've been using Amazon's EC2 services quite a bit lately. They provide some excellent support and give people a remarkable amount of flexibility. But at the same time, there is an awful lot of setup that can go into a system before it's ready for something as mundane as unit testing.  Setting the proper time zone is one of them. Unfortunately, CentOS on EC2 is not as user-friendly as Windows, so here's the quick 2-command reference for anyone looking to change their settings.

Disclaimer: I am not responsible for what you do to your server(s). If you have a problem with any information I provide to you free of charge, feel free to leave a comment. I usually respond within a few hours unless sleeping or somewhere deep in the mountains of Japan.

Now, there is a list of timezones located in /usr/share/zoneinfo. Find the one that you'd like and record the location.  What we're going to do is make a link to that file.  In my case, I'm using /usr/share/zoneinfo/Asia/Tokyo.

Next, let's make a backup of the existing localtime file:

mv /etc/localtime /etc/localtime.old

Now we make the link:

ln -s /usr/share/zoneinfo/Asia/Tokyo /etc/localtime

Done! That's all there is to it.  Check that the time is correct with the "date" command and you're set.

MySQL Server & High Availability

MySQL LogoHere's a question I'm asked often: how can you set up MySQL to be High Availability in a cheap and effective way? In my experience, one of the best ways to accomplish this is with the use of a Master-Master Replication setup across two servers.  This way if one machine drops off the network or goes down, the other will take it's place without missing a beat.  Additionally, if the server returns, it will automatically catch up and work alongside the other live one.

There are several ways to accomplish this, but the following tutorial will show one of the simplest ways to accomplish this goal.

Disclaimer: As will any tutorial, I am not responsible for any actions you do on your own servers. If something breaks, feel free to leave a comment and I'll do what I can to help out. It's assumed you have MySQL Version 5.x on your Linux-based system and you're comfortable enough with vi and other basic elements of the system.

With that out of the way, let's set up some replication!  We're going to have two servers with the following details:

  • Server_A - 10.0.0.10
  • Server_B - 10.0.0.11

On Server_A, add the following lines to your /etc/my.cnf file:

server-id=10

log-bin

binlog-do-db={Database to Replicate}

binlog-ignore-db=mysql

Next, still on Server_A, we'll need to create a replication account in MySQL:

mysql> CREATE USER 'repl'@'10.0.0.11' IDENTIFIED BY 'slave';

mysql> GRANT ALL ON *.* TO 'repl'@'10.0.0.11' IDENTIFIED BY 'slave';

Restart MySQL on Server_A.

Next we'll need to edit the /etc/my.cnf file on Server_B. Add the following lines, changing it as you need:

server-id=11

master-host = 10.0.0.10

master-user = repl

master-password = slave

master-port = 3306

So far it's pretty easy, right? Notice that the server-id value is different in the two files. Make sure that this number is unique throughout your network.

Restart MySQL on Server_B and then enter the following commands into MySQL:

mysql> start slave;

mysql> show slave status \G;

You should see output similar to the following:

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.10

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqld-bin.000008

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000012

Relay_Log_Pos: 236

Relay_Master_Log_File: mysqld-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 98

Relay_Log_Space: 236

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

Two really important lines are Slave_IO_Running and Slave_SQL_Running. Notice how they both say "yes". If yours doesn't, then the replication isn't set up properly.  Double-check your settings and try again.

If you've ever configured replication on MySQL server you're probably saying to yourself "How is this different from normal replication?"  It's a valid question, because we now have a working Master/Slave configuration.  This next part is where the magic comes in.

Going from Master/Slave to Master/Master

On Server_B, add the following lines to the /etc/my.cnf file:

log-bin

binlog-do-db={Database to Replicate}

Create a replication slave on Server_B for Server_A:

mysql> CREATE USER 'repl'@'10.0.0.10' IDENTIFIED BY 'slave';

mysql> GRANT ALL ON *.* TO 'repl'@'10.0.0.10' IDENTIFIED BY 'slave';

I've used the same username and password for the sake of simplicity, but you can set them as whatever you'd like.

On Server_A, add the following lines to the /etc/my.cnf file:

master-host = 10.0.0.11

master-user = repl

master-password = slave

master-port = 3306

Looks easy, right? Oddly enough, it really is just this simple. Restart the MySQL servers on Server_A and Server_B. Now it's time to finish the configuration.

Server_A:

mysql> start slave;

Server_B:

mysql> show master status;

Server_A:

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.11

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqld-bin.000008

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000012

Relay_Log_Pos: 236

Relay_Master_Log_File: mysqld-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 98

Relay_Log_Space: 236

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

There we go! Check to make sure that Slave_IQ_Running and Slave_SQL_Running are both Yes (on both servers), and you're set to go. Now you can create your tables, populate them, and get some work done.

Master-Master ReplicationThe AUTO_INCREMENT Collision Conundrum

There is one little thing that might bite you with busy servers, and that's index collisions. If a both servers have data recorded to the same table(s) at exactly the same time, there is a chance that different data will exist for the same index value, causing a collision when the servers try to update each other.  There are two ways to get around this.

The first is to not use AUTO_INCREMENT for primary keys. Either use unique identifiers or have a single system parameter table ready to provide index numbers whenever they're required.  The other is to change the AUTO_INCREMENT behavior.  Personally, I prefer this second option because it's quite easy to do.

Once again, let's open up the my.cnf file on Server_A and Server_B and add the following lines:

Server_A

auto_increment_increment=5

auto_increment_offset=1

Server_B

auto_increment_increment=5

auto_increment_offset=2

The first line tells MySQL to auto-increment tables by 5 rather than 1, and the second is the start number.  In this example, Server_A would receive values 1, 6, 11, 16, and so on, while Server_B would receive 2, 7, 12, 17, and so on.  This is not a perfect solution, but it certainly beats some of the alternatives … especially if you're running servers that are being hammered with read/write requests.

Easy, right? Well … maybe after some practice. But that's all there is to it.  The nice thing about this setup is that it works really well with load balancing systems and can help resolve some serious bottlenecks if you have people doing heavy reporting on live data.  Is it the best solution? Maybe not. But if you're on a tight budget, this can save you quite a bit compared to some of the other options.