Voice of the DBA

Tag: administration

  • 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.

  • Backup Log to Nul– #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.

    There are times when you might be working on your demo/lab system and you generate a lot of tranasaction log activity. This isn’t data you want to save, so perhaps you want to remove the activity without saving it.

    There are a few choices:

    1. Run a normal log backup to a file, then delete the .trn file.
    2. Switch to simple mode
    3. Run a log backup to nul

    The first one is easy, but it’s a pain. I have to go to explorer, or open a VM, delete the file once I find it. The second one is what I’d suggest. In fact, as soon as you install SQL on a lab system, set model to Simple.

    The third item is valid, and I ran across this recently. When you use this syntax, make sure you use “nul” and not “null”. We are trying to send to /dev/nul, which is nowhere. If you backup here, then nothing happens. You can use this command:

    BACKUP Log sandbox2 TO DISK = N’nul’

    This will run a backup, and discard all of the backup data. When I say discard, I mean it’s not written anywhere.

    However, this is a real backup. It’s marked as such. This breaks a log chain, and you can do this with a full database backup as well, which means you really need another full backup after this to reestablish a baseline.

    Again, I ran across this, but it’s not what you want to do. If you need to clear the log, use

    ALTER DATABASE xx SET RECOVERY SIMPLE

    ALTER DATABASE xx SET RECOVERY FULL

    and take a full backup.

    More thoughts from Gail Shaw.

  • Database Mirroring Needs FQDNs

    A quick basic post, and one that I’ve forgotten. Since blogging is a good way to remind myself of things, here goes.

    I was testing Database Mirroring (DBM) recently for an upgrade situation. I’ve set up it up in the past, but since it’s deprecated, I’ve moved on to working with Availability Groups (AG) for the most part. However, mirroring might still be in use for you, or you’re looking to perform a simple rolling upgrade, and DBM works well.

    I restored a database on a new instance, opened the firewalls for 5022, and then went through the mirroring wizard. Once I was done, I enabled mirroring on the secondary database (the one restoring) and that worked fine.

    Then I ran this on the primary:

    ALTER DATABASE Baseball SET PARTNER = 'TCP://192.168.1.201:5022'

    After a few minutes, I got an error:

    Msg 1418, Level 16, State 1, Line 10
    The server network address “TCP://192.168.1.201:5022” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

    I tried all sorts of things, including shutting off firewalls, and disabling the rebuilding mirroring. My endpoints were fine, the domain accounts running the instances had access, but it wouldn’t work. I tore down mirroring and added it back, verifying each machine could see the other by name. I tried again.

    ALTER DATABASE Baseball SET PARTNER = 'TCP://SQL02:5022'

    I knew I had a problem when this took more than 5sec to respond. Again, an errror.

    Msg 1418, Level 16, State 1, Line 10
    The server network address “TCP://SQL02:5022” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

    Finally I tore things down again, deleted endpoints, but this time I connected to the instance with an FQDN and configured things. I made I connected to the mirror with an FQDN as well. Finally things worked:

    ALTER DATABASE Baseball SET PARTNER = 'TCP://SQL02.HOME.XXXXX.COM:5022'

    I must have read the documentation numerous times, each time reading the FQDN, but somehow thinking that couldn’t be the issue in a small network.

    Hopefully this blog will help me remember.