Voice of the DBA

Category: Editorial

  • The New SQL Server 2016 Edition Limits

    I was thrilled with SQL Server 2016 SP1. I’ve been asking for all features to be in all editions for a long time, and now we have most of the features available. There are still a few enterprise only items, but these are based on speed and scale, and not programming, and I can live with those. While I’d still like some “in between” edition that had higher limits than Standard, or even some sort of core/RAM add-ons, I recognize there’s a complexity in doing this that might not be worth undertaking.

    That changes in SP1 that allow features like snapshots, CDC, Columnstore indexes, and more mean there are some other changes that are important to understand. The SQL Server Tiger Team recently posted a note about some of the limit changes in SQL Server, showing how some of the memory limits have been raised with the new Service Pack.

    In the last few versions, SQL Server Standard Edition (SE) has been limited to 128GB of RAM in a server. While that might not be a lot to some of you, I’ve rarely had servers with more memory, so that’s been a good edition for me. However, when considering using Columnstore or Memory-Optimized tables, I could see why there could be some concern over memory shortages. After all, SQL Server really, really likes lots of memory.

    There still is a 128GB limit for the buffer pool cache in SE, but this is separate from the Columnstore cache and memory-optimized data stores. For Columnstore cache, you get an additional 32GB of RAM that can be used per instance. That means that if I use Columnstore indexes, I could use 160GB for caching data (128 + 32). What’s more, if I decide I need to use In-Memory OLTP tables, I get an additional 32GB per database to use here. If I have a single database with both features, that means I can use 192GB of RAM for data. If I have two databases, I could get 224GB of data in RAM. Of course, things aren’t quite this simple, and certainly, running out of RAM with In-Memory OLTP tables is bad.

    While these changes might not mean much for some people, I bet lots of customers will appreciate the chance to use these features, and not take any existing RAM away from their SE server. Of course, if you only bought a server with 128GB, you might want to get a little more before you start playing with Columnstore or In-Memory OLTP tables. And if you really need lots of RAM, Enterprise Edition now supports 24TB of RAM. I can’t wait to read a post when someone publishes their experiences on that system.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 6.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • The War Room

    I remember the first time I worked in a large, 10,000+ employee company, and we had a crisis with our systems. A number of us crowded into the cold computer room, gathering around a few workstations and trying to solve the issues. We worked furiously to restore service, with various managers and executives periodically knocking on the locked door wanting status updates, unaware they were slowing us down. Eventually we stabilized things, but it was a chaotic and inefficient environment with too many people involved and more time spent talking and discussing problems than solving them.

    Later I worked in a similarly sized, but more mature company. We had various virus issues, including SQL Slammer. When we realized there was a crisis, we’d contact a director who would convene a crisis team. There were designated individuals from various groups (network, security, database, etc.), each of which had a backup, but only one representative from each area was a part of the team. Those were the only people that participated in the meetings, giving status updates, or taking actions to be distributed to their team. Each was responsible for coordinating the activities of their area with others. Status updates were scheduled regularly with a specific individual posting them. The director leading the crisis would update executives.

    I thought back on these experiences (I was the main person in the database area) when I read about the Amazon war room experiences around launching one of their products. While our crisis management wasn’t quite like this, it was fairly well scripted. There were times that the process didn’t proceed smoothly, but it worked well overall for problem situations. I wish that we had handled deployments a little more formally, though not as strictly as Amazon did. Our deployments didn’t have a large an impact as a product launch, but we certainly could have used more coordination between different groups. I remember no shortage of networking/firewall issues, security mismatches, or missed communications with customers from deployments.

    I’d like to see software deployment to be an easier and simpler process. My hope is that more people learn to code better, and they implement unit tests to ensure they meet requirements and prevent regressions. I want to see automated deployments into staging environments to catch potential issues, and eventually, smooth execution from the client perspective. I want these things to happen for both database and application software.

    A better development, test, and deployment process doesn’t mean that there isn’t a need for strong coordination among everyone involved, and certainly doesn’t mean a crisis team isn’t prepared to respond if there are issues. Thinking ahead to potential issues and ensuring everyone is on the same page helps to smooth any of the bumps that will occasionally crop up. At least, I expect they are only occasional if you follow a good CI/CD process.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • The Purpose of a Database

    A great quote from a blog on machine learning in SQL Server: “…nobody buys a DBMS for the sake of DBMS. People buy it for what it enables you to do”. The post is from Rimma Nehme, who has given a few keynotes at the PASS Summit. While the focus of the post is how you can implement deep neural network learning with R Services in SQL Server, I thought that quote stands out for any database, relational or NoSQL, from Microsoft, another vendor, or open source.

    I think it’s easy to get caught up in the debate over which features are better than others, or which database might perform better for the money spent. Pehaps we want to debate how easy or difficult it can be to build an application with the platform. We can look at the ROI, the ability to easily implement HA, DR, or some particular subsystem that we need. Those are all good questions, and certainly part of the decision to use a particular platform.

    At the end of the day, it doesn’t often matter which database platform you choose. Whether a JSON file, a relational platform like SQL Server, or the Neo4J graph database. The people that will use the database to query information, make decisions, or just store information need the system to work for them. The system needs to do something that helps their organization in some way. Often that’s based on the capabilities of the software that connects with the database, the capabilities and performance of the platform, and certainly the abilities and execution of the staff that work on the system.

    There’s plenty to debate about using SQL Server with the R language. We can make some determination about whether or not there’s value in spending licensing dollars on expensive SQL Server licenses and using those cores for analytics rather than some other, cheaper hardware. Microsoft R Server (another some other service) might be a better choice. Ultimately, the value to the end user is in getting the data processed and returned to them, whether this is through a query, a report or some recommendation from a machine learning algorithms.

    My view is that more complex processing, whether through machine learning or other types of data analysis, are going to be more important for data professionals in the future. As we build new applications, or even seek to keep older ones viable for a long time, we need to keep in mind that the DBMS isn’t the reason we have a project or job. It’s because we can somehow extract information from the DBMS and process it in a way that adds value to an organization. Whether we do this in a database or application is up for discussion and debate for each individual situation, but we need to ensure we are providing value for our customers.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Legacy Limits

    We are seeing changes to the SQL Server platform every month in Azure. Since that’s the same codebase used to product SQL Server on premise, that means the enhancements are not only being tested in the cloud, but they are available for release on a regular basis. SQL Server has been on a two year release cycle for major versions, but things are speeding up and I expect a new version (SQL Server 2017?) around a year after the last version (SQL Server 2016) was released.

    My thoughts are that this cadence will mean that many of us with more than a dozen servers will end up supporting more versions of SQL Server in the future. New applications will want newer versions, as do the employees, but there isn’t always a business case to upgrade all the older instances. If we were to see new versions every 18 months, given a 5-7 year life cycle (the standard support time frame) for database servers, I would expect that many of us would be always supporting the last 4-5 versions of SQL Server. If we go to a ten year life cycle (may be more realistic to me), then we would be looking at 6-8 versions.

    If you think ten years is too long, SQL Server 2005 is just over 11 years old. How many of those instances do you support? SQL Server 2008 is almost 9 years old and I bet a few of you have those instances around. Certainly if you have a support agreement and you have applications that use fairly core SQL Server features you can upgrade, but certainly keyword and language behavior changes might limit your flexibility.

    I read a piece recently noting that many organizations still use Windows XP for various systems. A number of the reasons given are to support legacy hardware or software. Some don’t have replacement versions of the hardware/software, or can’t find any value in managing an upgrade. The same issues hold true for SQL Server. I did some work for a company in 2008 that was still running a SQL Server 6.5 instance to support their keycard system. The database was virtualized, worked fine with Internet connectivity, and essentially cost a few hundred dollars a year for consulting fees. An upgrade to newer software, which would support SQL Server 2005+ would have cost over $50,000. Plus support.

    For no new useful features. No wonder they didn’t want to upgrade.

    This week I’m wondering how many of you are tied to older versions of SQL Server because of compatibility issues with software (or hardware). Are there reasons you maintain old database platforms? Any plans to upgrade, or is the cost not worth the benefits?

    I find legacy software and hardware to be a problem with the technology paradigm. While vendors want to move to new versions and reduce their support burden, many people feel that the cost of regular upgrades every few years is too high. Perhaps we could move to renting software, and getting constant development, but developers seem to be loathe to just constantly develop one version of the software for decades, preferring to fundamentally change the architecture at some point. Certainly that may make sense for some customers, but others who don’t need new features might wish for software to just work.

    I don’t have a great solution, but I would like to see options for software like we have with many other products. An example might be autos, where there are companies manufacturing parts that repair or upgrade older models, without the need to purchase a new vehicle. Maybe older software can be licensed in some way to allow independent developers to produce patches, security or otherwise. The vendor could continue to sell new versions, but a licensee could support old ones. I know there are times I’d prefer to have an older version of software work well, and continue to work for the foreseeable future.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.