Voice of the DBA

Tag: sql server

  • Free SQL Server

    No, I don’t have a way to license a SQL Server instance for your application for free. Microsoft still charges for production workloads. However, there are several ways that you can do development or testing for free, or at a greatly reduced cost. Bob Ward spent a bit of time researching options and he’s written a post that outlines a number of your options. It’s very comprehensive and I know a lot of us are grateful to Bob for summarizing our options.

    As I work with developers in organizations, there are often questions about the licensing for dev editions. This comes up more with Oracle than other platforms, but SQL Server does get asked about. We made a decision to support SQL Server Developer Edition in Redgate Clone, which is in line with what Bob’s post above says: this edition is for developers (and it’s free). There is one caveat here, which I never knew. The licensing guide says you cannot build test data and move that into production. I’m assuming this doesn’t mean you can’t create a list of US states, countries in the world, or statuses. I think this means something more substantial, but if anyone knows, let me know.

    One question I get asked a lot is about how do you develop locally if your production target is Azure SQL Database? The best option is a container, as outlined in this MS Learn article. It’s not completely the same, or necessarily as up-to-date, but it’s close and likely good enough for most of us. If you really want an Azure SQL DB instance for dev/test, it’s not going to be free. Unless you can live within the monthly Azure credits, which are pretty low for this purpose. If you are a Visual Studio subscriber, you can get some discounts, however, and I didn’t know this, you can’t use Azure credits with the VS benefit.

    There is also a “free offer” for Azure SQL database. This is fairly limited, but you do get free compute and 32GB of data for the lifetime of your subscription. If you work with small development databases, and you aren’t querying like crazy, that might work well for you. It’s also a great way for students or job-seekers to get some experience with the platform at a low (or no) cost. You can even try Managed Instance for free for 12 months.

    The cloud isn’t for everyone, every organization, or even every application, but it does work well in some situations. Whether that fits you or not can be tricky to determine, but you certainly should be working in the cloud in a dev/test manner to help inform the decision. I would hope that most of you have dev/test databases in place for on-premises/IaaS systems as well. I don’t run into too many people developing on a production instance, but I do see some, and I certainly see some that work in heavily shared environments.

    Cost shouldn’t be a factor here, at least not for SQL Server, and there are options to help you ensure you have all the dev and test instances you need. Read Bob’s post and see if you can help make your developer’s lives easier with some of the resources. And learn to follow good software development practices for your SQL code, just like the C# developers do.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

  • 30 Years of SQL Server on Windows

    I’ve spent over 30 years working with SQL Server. I saw a post recently from Bob Ward who is celebrating 30 years at Microsoft, noting this is also the 30th anniversary of SQL Server on Windows. It’s been an honor for me to get to know Bob, learn from him, and spend time socially at various events around the world. He’s truly been a force in improving the SQL Server platform through the years.

    While SQL Server has been on Windows for 30 years, I’ve got a bit more than 30 years working with the platform as I actually started managing a SQL Server instance on OS/2. With the addition of Linux, I’ve now run SQL Server on three different server OS hosts: OS/2, Windows, and Linux. That’s somewhat amazing to me, especially as one of my April Fools jokes came true. This was one of the most-read articles on SQL Server Central for a long time, and perhaps it influenced enough people at Microsoft to build and release a Linux version.

    Over three decades, the growth of the platform has been amazing. We’ve had the core engine lose tables in memory and then get them back with In-Memory objects. We have seen the heavyweight trace evolve into Extended Events and SQL Audit. The language has grown with many new ANSI features that help us write better code. We’ve deprecated a number of objects and added the ability to create our own with the CLR. We’ve added new data types, not all of which are named well, but we still have the ability to process more types of data natively. We’ve even added new services, with SSIS, SSRS, and SSAS packaged into the platform.

    Not a lot has been removed, but a few things are gone, like Notification Services. Replication doesn’t seem to have changed a lot in 30 years, which is a bit disappointing to me, but there have been a lot of improvements in HADR capabilities. We even have other companies (Amazon and Google) selling their own version of SQL Server as a platform, where you don’t have to manage the instance.

    In many ways, I’ve found SQL Server to be easier to use, but also much harder to learn about because the breadth of what’s in the product is so wide now. I can’t imagine anyone actually knows the entire product at a very deep level. I’ve also found myself more disappointed with the experiments Microsoft makes when adding features and not continuing to invest and evolve them (or even fix all the bugs).

    There certainly have been plenty of bugs, security issues, and maddening moments in working with SQL Server, but it’s been a great 30 years of a career working on the Windows version. I likely won’t do that for another 30 years, but I hope those of you making a living working with the platform enjoy it as much as I do.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

  • If You Build It, Will They Use Linux?

    There’s a great scene in Field of Dreams where James Earl Jones says that people will come. It’s in response to the voices Kevin Costner hears early in the movie. This is the climax of the movie, where Costner makes a financial decision to trust his instincts and hope his farm will be saved.

    In many cases, organizations do just this. They build something, assuming people will come. They may have some data, research, or other reasoning as to why why people will use what they build. However, that’s not always the case. Sometimes they build something and hope people will come, much like Field of Dreams.

    I wondered about this recently with some friends when discussing SQL Server on Linux. Quite a few people I know haven’t seen any Linux installations inside their organizations. Others are consultants and haven’t experienced any work on Linux, which is interesting. I’ve heard people at Microsoft state there are plenty of installations out there, but I’ve found few people who have moved from Windows.

    I know a lot of people who work in the Microsoft data stack aren’t familiar with Linux. This is despite all the articles and writings the last few years trying to teach people about Linux and how SQL Server runs well. I also see articles like this one, which claims Linux uses more CPU. This is despite Red Hat saying Linux runs SQL Server well and leads in benchmarks from TPC, at least the TPC-H results.

    I learned at the various command lines in school, with a lot of time spent on Unix systems. Even with X-Windows, I often used the interface to open multiple shells to get work done. However, a lot of people have not worked at the command line very often. Despite the popularity of PowerShell, I find no shortage of Windows-based knowledge that struggles with certain concepts, like quoting and piping results between commands.

    I don’t know how many systems run Linux v Windows. Azure states that over 50% of their VMs aren’t Windows, but Linux. I suspect a lot of those are likely web servers or other systems and not database platforms. They don’t seem to publish how many databases are SQL Server on Linux, and I have no idea if Azure SQL Databases (or MI/Synapse/Fabric/etc.) run Windows or Linux. I suspect it could be either, but most likely Windows.

    Do you feel comfortable running SQL Server on Linux? Is your organization considering it? I don’t know I think it’s worth the savings in license costs. Not that Windows is cheap, but the effort to train, learn, and work on a second OS might not provide any savings for years. Maybe not ever if you can’t get work done smoothly and quickly.

    They built it; now, will you come run it?

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

  • Has SQL Server Performance Improved?

    I saw a very interesting blog post this week from Brent Ozar that asked if SQL Server 2019 More CPU-Intensive Than SQL Server 2016? You can read the post, but a client was having CPU issues and thought everything between their SQL Server 2016 and 2019 environments was the same.

    Brent decided to test this and found that on identical systems, he has queries taking more CPU on SQL 2019 than 2016. A few commenters tested as well, with similar results. Not everyone had similar results, but most did. You can get the scripts from the post and try it yourself if you have SQL 2016 and 2019 instances.

    One would expect that more recent versions would run faster, or use fewer resources, for equivalent data sets and hardware. I know that’s not always the case, but it ought to be the case for lots of workloads. If not, then arguably the newer version isn’t better. It likely isn’t worth more money, and definitely needs more development work. This is my view of Windows 11, which seems to have returned to the habit of earlier Windows version of requiring and consuming more resources than its predecessor.

    I don’t often benchmark or evaluate SQL Server version. I don’t have to make those decisions, but I have seen SQL Server continue to improve on the TPC-E benchmark. However, this isn’t necessarily the same hardware. In fact, across versions, it likely isn’t. There could be more CPU consumed by the same queries, masked by hardware advances (and falling hardware prices).

    Is SQL Server using more CPU in newer versions? I’ll let you see if that’s the case on your systems. Even if it isn’t, you might document some queries (in addition to Brent’s) and record the results. That might help you decide when you upgrade.

    Steve Jones