Voice of the DBA

Tag: administration

  • Enabling Database Containment for an Instance – #SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

    I wanted to test a contained database feature the other day and ran this:

    ALTER DATABASE [sandbox2] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

    However, this didn’t work. I ended up with an error:

    Msg 12824, Level 16, State 1, Line 3

    The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database.  You may need to use RECONFIGURE to set the value_in_use.

    The issue is that the server instance needs to have contained authentication enabled in order to pass any authentication requests to the database

    EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

    Now I can run the code again to alter the database for containment.

    SQLNewBlogger

    The issue was obvious to me since I’d dealt with it in the past, but this is something you could solve and write up in 10-15 minutes.

  • Remove an Active Lease on a Blob in Azure

    I was creating and dropping VMs in Azure, and found myself charged money for disks that I thought I’d deleted. I found later that deleting a VM doesn’t necessarily remove the disk blob. Here was one way that I removed some of those blobs.

    First, go to the classic portal. For some reason, the new Portal doesn’t have the capability.

    2017-06-21 21_11_07-Virtual machines - Microsoft Azure

    Click Disks

    2017-06-21 21_11_19-Virtual machines - Microsoft Azure

    At the bottom, click Delete

    2017-06-21 21_11_31-

    That removed one lease, but not others. To get rid of those, I had to do more research and searching. That process is for another post.

  • Who’s Licensed?

    My employer licenses software to users. Many of you might have SQL Compare, SQL Prompt, or one of our other handy products. If you do, you might have noticed that we have a Redgate login for you that shows your licenses and lets you activate/deactivate them. This was surprisingly a big project across the last year to streamline and smooth our licensing process.

    Early on, I realized this was an issue in one company I where I worked. This was during the 1990s and I started working at a small company with a fancy imaging system. We had purchased software to receive all our faxes as images and file them in a digital system. I’m sure we had one of the smallest (and cheapest) installations of this software, which one of our executives had managed to negotiate. However, the exec had left the company and a few months into my tenure, I needed support.

    Finding our account, verifying our status, and re-enabling an old email account were a few of the cumbersome steps we completed to link the software to our organization. As soon as this was done, I realized this process had created a single point of failure, something I’ve tried to avoid as a technology professional. Immediately I set up a new email (licensing@ourdomain.com) and changed our account to this email. In fact, in all future purchases of software and hardware, I linked all support, warranty registrations, purchasing contacts, and more to this email. I also had this email forward to both myself and the CFO (he got an email rule set up to file this away).

    Since that time, I’ve tried to use centralized contacts for my employers, ensuring that any vendor contact would outlive my tenure. I haven’t always been consistent, and certainly with SQLServerCentral, I purchased any number of things under my own email, assuming if I weren’t there, the company wouldn’t be. However, I did make sure that I put all contact info in a Password Safe that my business partners had copies of.

    I wonder how many of you bother to worry about longevity when you contact a vendor or purchase software. There are certainly times when a personal email makes more sense (research, working to craft an ROI), but for formal contacts, and registrations, a more generic contact might make sense.

    Actually, I’d like to see more vendors take this approach, building their CRM and sales systems to take a general contact and then a list of individuals that might be personal contacts. They could easily add new licenses to this account, tracking them in a central area, perhaps even ensuring that renewals would be a more efficient process.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Checking Your Database Properties–#SQLNewBlogger

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

    I was reading Grant’s Database Fundamental Series on Database Properties, and it got me thinking. I think this is a good set of knowledge to have, but building on the properties, can you check them programmatically?

    You can, and here’s how.

    There is a function, DatabasePropertyEX(), that provides you a way to check properties.  You can use this with two parameters to check your database. These parameters are:

    database name – The name of the database, where you can use dbname() for the current database.

    Property name – These are a series of items to check a value for.

    As an example, one of the items Grant mentions is the recovery model. I can check that with this code:

    SELECT DATABASEPROPERTYEX(DB_NAME(), ‘Recovery’)

    In the current database, I get this:

    2017-07-27 14_31_24-SQLQuery8.sql - (local)_SQL2016.TestingTSQL (PLATO_Steve (52))_ - Microsoft SQL

    There are many properties I can check, and I can see a nice list here from SQL Prompt, or I can check the BOL page.

    2017-07-27 14_31_59-SQLQuery8.sql - (local)_SQL2016.TestingTSQL (PLATO_Steve (52))_ - Microsoft SQL

    As nice as it can be to pop open SSMS and look at dialogs, learn to check things programmatically. Once you can do that, you can start to let the system check and alerts you to changes.