Voice of the DBA

Author: way0utwest

  • Changing the Default DB for a Login–#SQLNewBlogger

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

    Recently I got into a bit of a pickle. I was detaching some databases for a demo, which is something I do periodically to make it easier for someone to see what’s doing on. The database detached fine, and I ran my demo.

    Then, as a sysadmin, I right clicked to attach a database back.

    2017-10-23 15_59_17-SQLQuery6.sql - (local)_SQL2014.SimpleTalk_1_Development (PLATO_Steve (57))_ - M

    and all of a sudden I get this error:

    2017-10-23 15_59_08-Microsoft SQL Server Management Studio

    Interesting. I could have attached the database back from the command line, or with dbatools, but I thought this was interesting. As I go to the attach dialog, a new connection is made. However, in this case, the default database for my sysadmin account was the one I’d detached.

    Fortunately, this is easy to fix. First, I opened a query window with master as the specific database:

    2017-10-23 16_01_20-Connect to Database Engine

    Then, I have a couple options to reset my default database. For old SQL Servers, many of you might have used sp_defaultdb. That’s marked as a deprecated procedure, so ALTER LOGIN is the new way. The syntax uses the WITH to include various options. In my case, I needed the DEFAULT_DATABASE item. This was my code:

    ALTER LOGIN [PLATO\Steve] WITH DEFAULT_DATABASE = MASTER

    If you are on an older version, something like this will work:

    exec sp_defaultdb @login = ‘Steve’, @defaultdb = ‘master’

    Once that was done, the GUI dialog worked. A quick and easy fix in this case.

    SQLNewBlogger

    As soon as I found the error, I knew what was happening. Resetting the default database took less than a minute, but I decided to spend 10  grabbing a few screenshots and putting this post together.

    You could do the same thing. Show that you can recover from errors.

  • How Long Before You Upgrade?

    This editorial was originally published on Apr 11, 2014. It is being re-published as Steve is out of town, with a few minor changes to dates and values.

    It’s 2017. SQL Server 2000 is 17 years old, but there are still quite of you managing instances. SQL Server 2005 is 12 years old, and I’m sure more of you still deal with that version. I know because I work for a software vendor and I’m constantly asked if our software will run against those two versions of SQL Server. Most of our software is no longer supported on those versions, as they’re too far out of date.

    For many of you, however, if you’re managing a SQL Server 2000 instance, it might only be 10 or 12 years old. Your company might still have been installing SQL Server 2000 in the year 2005. The same is true for SQL Server 2005. I wouldn’t be surprised to find companies still installing 2005 instances in 2008 or even 2009.

    Companies don’t care much about versions. They tend to mostly care about databases getting the job done, and sometimes, support. Many organizations don’t see value in upgrading too often because of the overhead. I suspect many managers would prefer to get many years usage out of a platform before they change in order to minimize work that doesn’t add value to their business.

    The question this week asks you about the longevity of your database instances. Think about the average instance, or even the majority of your applications and how long they will remain on a particular version.

    How many years will you run a platform before you upgrade it?

    Years ago I heard someone at a large Fortune 100 company say their stated policy was to get 10 years of service out of a database server. At the time I thought that was a long time, but the more I think about it, the more I think that might be a minimum amount of time I’d want from a platform.

    Let us know this week what you experience, and perhaps what you’d prefer.

    Steve Jones

  • Should We Move to Azure?

    This editorial was originally published on May 23, 2014. It is being re-published as Steve is out of the office, with a few minor editorial changes.

    Update: We made a number of changes last year, upgraded the forums, and have continued to implement bug fixes. We are still considering this idea.

    At SQLServerCentral we have been debating some architectural changes to the site. We’d like to improve the way a few of our features work, add new ones, and replace a few items that are buggy and hard to maintain. The site has worked well over the years, but we’ve made relatively few improvements, mainly due to the fact that like many companies, this website isn’t our business, and work has a lower priority than some other areas in the company.

    Recently I had a suggestion presented to me and I wasn’t sure how I felt. There were certainly pros and cons to both taking advantage of the opportunity as well as passing it up. While we are still talking about the process, I wanted to ask the community what they might think this week.

    Should we move SQLServerCentral to Azure?

    I really like the idea of PaaS. There are issues, and certainly I think Microsoft could do a better job of creating competition as well as securing and even deploying their platform. However I think the idea of just consuming services as opposed to installing and managing pieces of software, is a great direction in which to move. SQLServerCentral would seem to be a perfect type of application that fits in the cloud.

    We run a generic ASP.NET site, using fairly common and simple database features in SQL Server. We don’t store or manage much in the way of PII, financial, or medical data outside of email addresses, and we don’t have the need to scale to high levels, either in data storage or computational resources. The one problematic area is our email requirements. I’m not sure what the cost is to send emails from Azure, but we send millions per week, and CAN-SPAM requirements can be tricky to meet in the cloud.

    However I’m just wondering what the community thinks. If we successfully moved to the cloud, and disclosed details about the process, would that give you more confidence in the cloud? Would it be interesting? Or is SQLServerCentral just a service and you don’t care how it works, as long as it does. Let us know this week.

    Steve Jones

  • Off to DevOps East

    The travel countdown is down to 2 trips this year. The second to the last is this week, as I’m off to DevOps East, a conference in Orlando, FL that tries to bring together lots of DevOps experts and advocates to share ideas and learn more. This is my first time at this event, and I’m looking forward to talking DevOps for a few days.

    I’ll be in Orlando all week, and then I have a month off before one last trip for SQL in the City.

    If you’re at the conference, say hi. And certainly talk databases with everyone that you see.