Voice of the DBA

Tag: T-SQL Tuesday

Looking Forward for T-SQL Tuesday #100

It’s T-SQL Tuesday #100. Amazing. I can’t believe this has been going for so many years. I’m proud that I had the chance to host the main archive site at tsqltuesday.com and host a few topics.

This month we return to the original founder, Adam Machanic, with the topic of looking forward 100 months. I wrote about predictions last week, so here’s a shot at a few more details.

July 2026

I’m approaching the big 60. It’s a year away, and I can remember joking with Allen White about how old he’s getting. In fact, it wasn’t too long ago that I had the chance to mourn that milestone with Grant Fritchey as well. Apparently my turn is coming, but that’s fine.

Life is good and I still enjoy working with SQL Server. It’s been 35 years now that I’ve been on the platform and after a slow few years of few versions, things have taken off. As I write this, I’m working on CTP of v23. Microsoft tried to do away with version numbers, but there are still many of us that need to run our own SQL Server instance. We expect SQL Server 2026 to be released in a few months, and it’s looking good so far. I’m especially pleased that Microsoft has continued to produce separate versions of SQL Server for different platforms.

Linux and Beyond

A few years back we got a version of SQL Server on Linux. I remember being both pleased and surprised by the first demo I saw back in 2016. As I learned more about the process, it was a neat engineering challenge for the team, but once they conquered the underlying API mapping, the rest of the code based was roughly the same. This work allowed Microsoft to continue to expand their platforms, and today we have a small version of SQL Server Express on ARM processors, which can automatically sync data with our larger systems.

Speaking of syncing…

Replication Works

I’m not sure how or why, but in 2021, Microsoft finally started to put some resources onto replication. I suspect they realized that many companies need more than just HA solutions for full databases. Customers need to move data around for other systems, to ensure MDM works, and gain compliance with the GDPR, UK, Japanese, and other legal frameworks. Customers also need to move subsets around for reporting, so replication got some love.

It’s more robust, requiring DNS to register the publishers, distributors, and subscribers. We have custom schema changes that can move data, and the entire process actually has tooling in SSMS to help monitor the system.

All that work with Linux helped, with better network discovery and reliability across all SQL Server versions. Parts of the system are built on the old Data Sync architecture, and more people are willing to use it since we don’t have to drop and rebuild replication for schema changes.

Multiple Query Engines

A little over a 100 months ago, we got the graph query engine in SQL Server 2017. It wasn’t that popular, but people used it and now we can use both Gremlin and Cypher.

SQL Server also added MongoDB API support, as well as a streaming database option for the incredible levels of data ingest caused by the IoT device growth. These are add-ons, and at least with MongoDB, they’re free in all editions. Streaming requires enterprise, which makes sense. Smaller companies don’t really need that level of data ingest.

Cloud First

Almost everyone is thinking cloud first. Thankfully, Microsoft changed the pricing of Stretch Database, and allowed us to move data to the cloud or back again to meet our needs. This has been helpful as most companies use a hybrid system, and as they grow and stabilize their workloads, often want to bring some of their data back into their own data center.

We’ve gotten more choices, as well. There are third party vendors, like Rackspace, that can run an Azure SQL Database or a CosmosDB instance. Microsoft licenses the system and provides third level technical support, but this has made all the difference in the world. Companies know they aren’t stuck with Microsoft for Azure SQL Database, so they’re more willing to give it a try.

Closer to the End

It’s been a great SQL Server ride for me. At this point I’ve been working with SQL Server for 40 years, and I’m getting close to the end of my career. I really enjoy the platform, and I’m still keeping busy with SQLServerCentral, Redgate, and a few clients, but I’m also taking more and more time off.

Hopefully you’ve had a great career to this point, and you will continue to enjoy working with SQL Server, as I have.

March 13, 2018
Use the Proper User for Azure MySQL DB

I wrote about migrating the MySQL database from a third party provide to Azure MySQL DB recently. This was due to the provider raising the price substantially for me to host the site. However, after migrating the data, I ran into an issue.

I’d read a blog on moving WordPress data and followed the instructions, but after updating my connection string, I kept getting this at tsqltuesday.com

Error Making Database Connection

It appeared big and bold in the browser, with no additional information. There was a time I dug into MySQL, but it’s been a long time. In any case, I wasn’t sure what the issue was, so I started messing around.

In MySQL Workbench, I could add a new user with their GUI and grant privileges. I did this for a user called “tsql2sday”, with a password. However, when I updated the connection string in my Azure App Service, this didn’t work.

I tried a number of items, including manually creating my own user and assigning privileges, making this a DBA (super) user, and more. Nothing seemed to work. Some of you might have even seen the error messages as I tried to get this to work.

Eventually I checked something. In the original app service connection string, my old third party database just had the user name. This was the same for my connection in MySQL workbench. However, with my new Azure MySQL database, I needed user@dbname. For me, this was tsql2sday@tsqldb in MySQL Workbench.

Once I added the @ and host to the connection string in the App Service, I could connect. Whew, as I wasn’t looking forward to another hosting bill.

I think all the data is there, but if you find issues with tsqltuesday.com, let me know.

February 20, 2018
Door Number One for T-SQL Tuesday #99

This is a good month for T-SQL Tuesday. It’s the end of single digits for this blog party, which is amazing. I can’t believe we’re 99 months in. I’m sure Adam Machanic is proud of his efforts here.

Aaron Bertrand hosts this month, and he’s given us a choice: door #1 or door #2. I’m choosing door #1.

Daddy’s Girl

I grew up in Virginia Beach, going through high school and playing elementary school volleyball where you could only score when you served. I loved the game, and eventually started to play on the beach during the summers at college and after I graduated. The game faded for me as I got married, had kids, and got busy with my career.

When my daughter was 8, I signed her up for a recreational league. They needed help coaching, and I volunteered. With another parent, we coached the same group of 7 or 8 girls for 4 years, 8 seasons, and had a great time. A few of us parents also got involved in adult leagues, playing at various levels.

When my daughter turned 13, she moved to a competitive league, as I watched. Across the years, I’ve watched my daughter play and love the game. As she became more independent, I started to spend time helping other teams, learning the modern game with rally scoring, liberos, and more.

This year I’ve taken on coaching a team of 14 year olds. Two nights a week, some Saturdays, and as I’m doing less traveling for work, I’m spending my weekends coaching at tournaments, and having a great time. As my body ages, and I spend less time at my own activities, I’ve turned to coaching others, and I love it. It’s a great way for Coach Steve to spend some time away from work.

February 13, 2018
Migrating MySQL Databases

A little out of my area, but I need to move the tsqltuesday.com database to a new service. The company running the db decided that they don’t want to keep a lower level consumer type tier. Since their minimum plan is now $20/month, I decided to look elsewhere. My first move was to try Azure Database for MySQL. This is a quick look at moving the MySQL data.

I have MySQL Workbench, so I started that up. I saw a note that this contained a migration wizard. I first created a MySQL database in Azure, which is really simple. Then I created two connections from MySQL Workbench. One to the existing database and one to my new one.

The next step was to start the Migration Wizard. This is under the Database menu.

As with all wizards, this opens with a welcome page. I clicked Start Migration to get going.

The wizard is fairly simple. Once I had this working, I walked through the wizard, choosing my source and destination. This was a simple process and things connected up once I’d opened the firewall.

After verifying connections, I selected everything. Since this is a WordPress site, it’s a fairly small schema and set of objects.

I basically clicked next, next, and let everything transfer. This only took about 5 minutes.

Once this was done, I queried the database, and everything was working. I saw my data and it appeared complete. No errors, so I assumed things were correct. Row counts seemed to verify this.

This was pretty simple, perhaps even easier than using SSIS and the Copy Database Task since this was built in.

Now to figure out how to re-point the application.

February 12, 2018