Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #101–Tools

    tsqltuesdayThis month’s T-SQL Tuesday is brought to you by Jens Vestergaard. The invitation for #101 is for essential SQL Server tools. Since I work for a tool vendor, you can guess that I’m going to be shaded that way.

    This is the monthly blog party on the second Tuesday of the month. You can participate by writing your blog, linking to the invite and leaving a comment there (or a pingback). This is the creation of Adam Machanic, and if you want to host, drop him a note.

    My Essential SQL Server Tools

    There are really three main tools I’d say that are needed for SQL Server work. At least, these are the ones I need.

    1. SSMS – It’s free, and it’s the main tool for connecting to SQL Server. You might like VS Code or some other editor, especially on other platforms, but really SSMS is required for most people.
    2. SQL Prompt – It’s expensive for some, but after using this for years, I think the the native intellisense hurts more than helps. Just formatting alone make this critical for me. I’m moving through quicksand, mixed with molasses, when I don’t have Prompt installed.
    3. Git – Another free one, but an important one. As we write code, SQL Server will only keep the latest version. Much of what we do means that we need to understand where we came from with code. We do have integrations at Redgate for SQL code, but no matter what, just having a record of work is important, especially with multiple people. I love git, but if you want SVN or something else, use that. Just use something.

    These are the main ones. Making these the main ones doesn’t mean that others aren’t important, but it just means these are the top of the list. After these, you can add in others. Here are the ones that I think are important for getting things done in a modern, multi-environment world.

    SQL Search – From Redgate, but useful for finding things in a large environment. Way better than browsing.

    Plan Explorer – I think SQL Monitor has a nice plan tool, but for those of you writing code and wanting to test it, this is way better than the native plan viewer.

    DLM Dashboard – Free, and let’s you see and track those changes in production. Find out when code is being released and you aren’t aware of it.

    Sublime Text – I tend to do some work in other languages, and in text, and having a quick, easy to use editor is nice. I think Sublime is great and worth the small fee.

    Password Safe – Having long, secure passwords is important. I don’t try to remember them anymore, and I do want separate ones for every site. I like this open source tool, and have ports on my mobile devices.

    Dropbox – Critical for me, since I use files on multiple machines. I keep copies of my presentations, password safe files, and more in sync across devices with Dropbox. Plus I can scan in docs from my phone if I need to send and share them.

    dbatools – This is an amazing project. I hated working with the sqlps module and really avoided PoSh for many things before dbatools. Give this module a try and you may learn to love PowerShell.

    SQLServerCentral – I’m biased, for sure. However, everyone could use a few things that we provide. A community, where you can get inspired and learn things (daily articles and editorial). A way to ask questions and get answers (our forums), and a place to keep scripts that are helpful for you (Scripts and Briefcase). These are important to rounding out your career, beyond just your current job.

    There are plenty more choices, and certainly lots of tools within SQL Server. You should know about Extended Events, or Query Store (or Open Query Store) and more. Learn to use the resources you have, practice and test with them, and if you need a paid tool, don’t be afraid of asking. If the tool provides an ROI, it’s worth it. If it doesn’t, tell the vendor why.

  • Looking Forward for T-SQL Tuesday #100

    tsqltuesdayIt’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.

  • 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.

  • Door Number One for T-SQL Tuesday #99

    tsqltuesdayThis 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.