T-SQL Tuesday 170 – Steering Future Projects with Past Experiences

Thank you Reitse for hosting January month of T-SQL Tuesday year 2024. This is my first blog post in the year 2024. Reitse asked us to write about any lessons learned from the old projects. Please find the original invite here.

I had to work on a project years ago where there was a requirement to migrate the old version of the database (SQL 2000) to SQL 2012/2016. One of the requirements was to use the replication to replicate the data over to another server. This is an OLTP database where there were heavy transactions per second. As a part of the planning, I recommended using the Always on Availability groups instead of the replication. My manager at the time (from my previous company) was a person who trusted and supported male DBAs more than female DBAs regardless of their expertise. I had to respect the decision to replicate the data over using the availability groups for read-only reporting purposes. I didn’t even have the chance to test the scenarios, compare the results from the two technologies, and show the results in the test environment.

Once the project is completed and in production. There comes the problems with replication. There were intermittent network failures, issues with the settings in the files generated by the snapshots, and other typical issues with the replication- there was a heavy lag in transferring the data. If you worked on replication before, you surely know fixing replication issues was no joke. We had to later remove the replication for good and replace it with the always-on availability groups. This has not only improved the performance overall but also required less maintenance.

Though the management understood later that they must have taken a different approach, the cost of it was the time and resources spent on the project.

One of the other projects was about the tools we wanted to purchase for data governance. Depending on one tool is not a good case when you are still in the process of deciding which tool to purchase for your needs. We had to spend a lot of time on one tool for the Proof of concept for months only to decide it didn’t serve our needs. This took a lot of DBA and the other team resources for months. I believe understanding the requirements from the company’s perspective should be the first step in the process. Then compare the tools from the wider perspective of whether they can serve all the requirements we are looking for. Filtering this way can give us the list of tools we can try and test to decide further.

These are the two experiences I would like to share in this post. I am looking to learn more on this topic from others as well.

Thanks for reading!