Voice of the DBA

Tag: sql server

  • Syntactic Sugar

    There are all sort of features and enhancements that Microsoft can make to the SQL Server platform. If you look around Connect, you’ll see suggestions for improvements, such adding common checks, as well as additions like adding virtual tables. I’m sure many of you would like to see simple things, like Regex added to the T-SQL language. In fact, you might find that some of these small changes, which we can code around or build, should just be added. After all, if Dynamic Data Masking can be added, shouldn’t some other simple features be included, such as helping us solve the “string or binary data truncated” error?

    These handy, useful, simple changes to the platform are often called syntactic sugar. These are changes that are simple, many of us could easily code them, but they make development easier. Or even administration in the case of the SQL Server platform. These are not necessarily expanding the power or capability of the platform, but they can make working on the system more enjoyable.

    Should Microsoft create more syntactic sugar for SQL Server? Certainly they do at times, but perhaps not as much as many of us would like. The thing to keep in mind is that making changes to the SQL Server platform can be very difficult and time consuming. Adding small features that might be helpful, while enticing, can slow the evolution and development of more core product features. Or they can cause more problems than we might expect in other parts of the platform. Would you rather have something like DDM that makes obfuscating some data easier, or a more robust replication engine that recovers from more problems? Do you want stronger security features like Always Encrypted, or more robust Always On features, or is it more important to get Regex added? I think we might have differing opinions here.

    These can be really hard questions, and certainly I think our feedback can help influence Microsoft. After all, if there is a nagging issue that is constantly causing issues, then maybe it’s worth a syntactic sugar improvement, even if this takes resources away from some other area. The one thing I hear from Microsoft over and over is that specific business cases and issues are more important than complaints. Express the issues you have with the platform in terms of workarounds, developer time lost, or specific performance issues rather than just a “I don’t like this” or something “doesn’t work as expected.”

    Ultimately Microsoft is a business, and they do look to add new features regularly to the platform to increase sales. I get that, and I try to temper my requests and complaints. I like to see them focus a portion of resources on core improvements to systems that work, and while I think this does happen, I’d like to see a bit more improvement in existing features. Certainly our backup and log reading systems have improved over time. SSMS is decoupled and being updated regularly. There is more work to be done, and if lots of us provide specific feedback, I’m sure we will see even more core improvement over time.

    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.

  • Provisioning

    This editorial was originally published on May 24, 2013. It is being re-run as Steve is on holiday.

    At a talk recently, the presenter talked about the time lag for an on-premises server to be installed and configured to be on the order of months. Not that many, but it could easily be six months.

    That seems crazy, but in many companies I’ve worked in, that wasn’t an unusual time frame. From the time someone decided that a purchase was allowed, it could be days for the accounting department to approve the funds. A day or two to place an order, assuming that IT had already provided the specifications. Vendors respond quickly, but it could still take a week or two, possibly even more, for a server to arrive at our company.

    One of the advantages of cloud computing services is that new virtual machines can be bought and enabled in minutes. That might be true, but I wanted to ask many of you if you know how longs it actually takes for you to begin working on a new SQL Server.

    How long does it take to provision a new server?

    Imagine that you were to ask for a database server today, and you management agreed to grant the approval, think about how long would it be before you could sign into a SQL Server. I’m sure some of you have virtual infrastructures available, and that should reduce the time it takes, but let us know if that’s your setup and how long it takes.

    The ultimate service would be a cloud like application inside your organization that allowed you to select a SQL Server template, and send you a server name and login with minutes after an automated build took place. I don’t know many companies that have that yet, but I think many of us would appreciate that capability. Especially if we could choose the hardware we required.

    Steve Jones

     

  • Connecting to a Specific Port–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I recently blogged about finding the port your SQL Server uses. At the end of that post I showed a connection to a server using a port. This is something I’ve used rarely, but it’s been very helpful.

    The specification for specifying a port can vary. For example, my build server at home runs TeamCity on port 8077. In my case, the URL to connect is:

    http://192.168.1.201:8077/overview.html

    If I had an FQDN, I might have this instead:

    http://Atlas.dkranch.net:8077/overview.html

    When I connect to SQL Server, I get this dialog:

    2016-11-15 15_28_10-Connect to Database Engine

    Some of you might note there is a “Connection Properties” tab, but there’s no port setting there, even if you choose TCP/IP.

    2016-11-15 15_28_27-Connect to Database Engine

    When I connect, I give a server name. In the example above, I used “.\SQL2016”. The default is that my client will try to connect on 1433 or use the SQLBrowser to get the port. However, I could specify the instance name like this:

    .\SQL2016, 1433

    For this instance, that wouldn’t work as the port is 6077. I’d have to write this:

    .\SQL2016, 60087

    But since I’m choosing a port, I don’t need that. I can use

    .,60087

    and I’ll connect.

    Learn to use the port in your connection string. At some point, this will help you to troubleshoot connection issues.

    #SQLNewBlogger

    This is a quick, easy post to write. It’s five minutes work for most of you. Maybe 10 if you have to Google/Bing.

  • Faster Versions and More Support

    When I started working with SQL Server, the versions came a strange paces. Three years between v4.2 and v6.0, but less than 1 before v6.5. Then two years to v7, one more to SQL Server 2000, but five until SQL Server 2005. After SQL Server 2008, we’ve moved to a 2-3 year cycle, but I can see that accelerating a bit more as SQL Server vNext looks like it might come in 2017, a year after SQL Server 2016. Since much of the code is released to Azure first and exercised there, I could see us getting on premise releases coming every 1-2 years in the future.
    Overall, I like this pace. We can see changes coming to the product on a regular basis and enhancements that I can choose to implement or not. We no longer have to wait years to get an enhancement or evolution of a feature. We always have the choice to upgrade. Many of us won’t, but that’s OK. Perhaps we’ll use new features in new applications, and learn how they might make the case to, or not to, upgrade existing instances.
    There’s a flip side to the rapid releases and enhancements to the platform. Many of our organizations expect a database server to last for years. We invest plenty of resources in building these database servers, and I think many managers and organizations expect they will be in service for at least five, and maybe ten, years or more before they are decommissioned or perhaps upgraded. I know there are still plenty of SQL Server 2005 and 2008 instances (probably not 2000) that are being used on a daily basis with no plans to replace them.
    This means that as the developers and DBAs that work with SQL Server, we will likely be supporting a wide range of versions in the future than ever before. Even if you started a company two years ago with a SQL Server 2014 instance, I’d guess you might have a few more now, perhaps some SQL Server 2016 instances. In two years you might have 2014, 2016, and 2017 (assuming vNext becomes SQL Server 2017). In five years, perhaps you’ll have five versions to support. I know there are people in that situation today with 2014/2012/2008R2/2008/2005 instances in production.
    There was an announcement this week from Microsoft. They are now offering SQL Server Premium Assurance, which will give you 6 years of support after the 10 you ca now get with Extended support. Certainly there are restrictions and costs, but I expect that there will be some large organization that think it’s worth paying this to continue to use older versions as long as possible. After all, this is a platform, which implies some level of stability.
    That doesn’t solve the knowledge issue and need to keep staff around that understand the platform, but with all the education and knowledge available from places like SQLServerCentral, perhaps supporting multiple versions isn’t a bit deal.
    Steve Jones