DevOps–Fixing Poorly Named Constraints

I was building some code the other day and kept getting problems in my deployment for a change. The deployment was having issues, and this came down to this statement.

ALTER TABLE EventLogger DROP CONSTRAINT [PK__EventLog__5E548648B043C0BC]

The problem was that this was the constraint on one developer’s workstation, but on another laptop, and in QA/Staging/Production, this constraint didn’t exist.

When we deploy to other environments, such as QA and Production, we will always see the wrong constraint, as most deployment mechanisms look at the name of the object, not the function. Every upgrade script will typically try to run the above statement and then run an ALTER TABLE ADD CONSTRAINT later to add the PK back.

If we have the correct name of the constraint in QA, the script will work. However, the name is likely different in each environment, so we need to fix this.

We can find the name of the PK with this script:

SELECT 
    A.TABLE_NAME, 
    A.CONSTRAINT_NAME, 
    B.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
       CONSTRAINT_TYPE = 'PRIMARY KEY' 
    AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.TABLE_NAME = ‘EventLogger’

If we take the results of this, we can use this to produce a drop script. Here’s one way to do this. We’ll store the name of the constraint in a variable and use the EXEC() statement to execute some dynamic SQL. We then can execute the ADD CONSTRAINT with a new name later in the script.

DECLARE @s VARCHAR(200)
SELECT @s = A.CONSTRAINT_NAME
FROM 
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, 
     INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
        CONSTRAINT_TYPE = 'PRIMARY KEY' 
     AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND A.TABLE_NAME = 'EventLogger'

EXEC('alter table EventLogger drop constraint ' + @s)
GO
/*
Other work
*/
ALTER TABLE dbo.EventLogger ADD CONSTRAINT EventLoggerPK PRIMARY KEY (LogId)

This is the type of DevOps change that I would release a table at a time, slowly cleaning up the constraint names. This will smooth your process and increase the reliability of your deployments.

Posted in Blog | Tagged , , , | Leave a comment

SSoL: Where are my data and log files?

I don’t think most of us need to know Linux, but if you end up managing a system, it’s good to have a little idea of how to get around. This is a short series of posts as I remember the skills I used to have back in university.

This is easily documented, and once you start working, you’ll learn this, and it is documented, but after a few weeks when I actually go into a Linux VM, sometimes I forget. Most of the time I connect with SSMS, and it’s just another server.

Data and log files are stored in /var/opt/data. You can see this in SSMS by selecting the properties of the server instance.

2017-06-15 11_21_18-Server Properties - 192.168.1.210

If you check the Files tab in a database properties, you’ll also see this:

2017-06-15 11_22_41-Database Properties - AlwaysEncrypted

You can also get this in Linux by starting the file manager as root. In a terminal type:

sudo nautilus

and enter your password. Then browse to the folder (computer/var/opt/mssql/data) as shown here:

2017-06-15 11_19_11-Ubuntu 64-bit SQL Server .210 - VMware Workstation

Posted in Blog | Tagged , , | Leave a comment

Advancing Security

One of the topics that is very important to many technology professionals is security. Security also might be one of the least understood aspects of our jobs. It’s an area that requires regular learning, monitoring, reactions, and vigilance. A healthy dose of paranoia makes a good security mindset, but this an easily devolve into greater stress and worry than might be appropriate, or the feeling that one should throw their hands in the air because of all the potential issues, attacks, and vulnerabilities.

I think as individuals our part is to learn to write better code and scripts with security in mind, perhaps implementing best practices, but really the ways we will get better security is when vendors and platforms develop better ways to implement their security for us. They need people that study the issues and build fixes applicable to protect systems. This means we need good interfaces and basic contracts that ensure we can build software on top of platforms, but we will need to outsource this part of our security.

Microsoft is one company that has been working to help us implement better security through defense in depth, through partnerships with other firms, and is making a difference. They’re not perfect, and there are still flaws in their software, but they are addressing and fixing them quicker and quicker. The world is changing, and Microsoft is a very different company than the one that build SQL Server 2008 R2, or even SQL Server 2012. They are maturing and becoming more responsive, and to me, more responsible about their place in the technology industry. They are striving to produce higher quality products, and when there are issues, they look to fix their mistakes quickly.

Many of us that have struggled to believe in this new Microsoft and apply patches in a more timely manner. I’ll admit that I still rarely apply CUs unless I have a need for a fix, but mostly that’s because I don’t want any unexpected issues to crop up when I’m presenting and I don’t have time to test that regularly. Like you, I have multiple versions of SQL Server. I do catch up periodically, and I certainly try to apply Service Packs within a few weeks of release, if not sooner. As much as Windows updates can be annoying, this is more a matter of timing than concern over quality, and I do try to keep up with these.

Will Microsoft and other vendors make mistakes with updates? Sure, sometimes there will be a patch that causes a problem with some, maybe many systems. However, we do need to grow and advance the security of our systems, which will always have vulnerabilities. Therefore, we need good quality updates from vendors like Microsoft, which I do think has happened. However, we also need customers to apply those patches. You can be slow and conservative, but don’t be negligent and try to avoid them completely. That makes the security situation worse for all of us.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | Leave a comment

The Financial DBA

I’ve got an Azure subscription. At least, I think I have just one. I know I have a few Microsoft accounts, across 4 (or more) different email addresses, that I use for various VSTS development purposes, but only one should have any Azure resources. When  I check each of them, I find that I do only have resources (and charges) for one. However, I also found a strange charge on my credit card statement from Microsoft Azure. It was just a couple dollars, but as I started digging through the various statements, I couldn’t determine where the charge was from, or what it was for. As of now, I’ve opened a support case with Microsoft to track down my usage.

This has me thinking of the future of many data professionals, some of whom will certainly have a few assets in a cloud based environment. I have a small subscription, but plenty of resources, and keeping track of which items belong to which resources and what I need isn’t always easy. It’s quite the challenge, especially when billing and finance aren’t my job. In an organization like Redgate, I’d guess we have lots resources, some of which developer provision, need, and then might forget about. Who reconciles this and ensures the assets we have are those we need?

As we move to a world where services are used as often as assets, perhaps interchangeably, I expect that many of us will need to be accountable for not only the usage, but tracking why we’ve engaged that service. Over time, we need to retire and get rid of assets, but ensuring we remember exactly what we’re using and why can be hard over time. With a little employee movement, many of us might find ourselves in charge of services that we don’t understand, and might not be sure are necessary. We also might not be sure these services can just be discarded. The last thing we want is some uptime disruption because we terminated some service contract.

A whole new dimension to our job is likely coming, and for many of us, this will test our documentation and organizational skills. I wouldn’t be surprised to find many DBAs or lead developers having a standing monthly meeting with someone in finance to be sure we aren’t wasting money.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , , , , | Leave a comment

FizzBuzz–#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’s been a programmer test that was used for awhile to see if a simple program could be constructed. It’s FizzBuzz, after a kids game, and designed to see if someone knows some logical program construction.

The test is to produce a list from 1 to 100 and for multiples of 3, write “Fizz”, multiples of 5 produce “Buzz” and multiples of both produce “FizzBuzz”. This means you get:

2017-07-06 10_18_44-SQLQuery1.sql - (local)_SQL2016.WideWorldImporters-RR (PLATO_Steve (64))_ - Micr

It’s a simple test, but I wonder how long it would take you to write it in T-SQL? It took me about 2 minutes.

Give yourself a little quiz today. For a challenge, also produce this in PoSh or some other language. I took about 5 minutes in PoSh, mostly because I had to look up some syntax.

In case you think this doesn’t matter, I agree with Jeff Atwood. I’d like to look at someone’s code before I interview them for development. They should have some public code, maybe something they recommend.

SQLNewBlogger

If you’re looking for a blog topic, show us you can write this code.

Posted in Blog | Tagged , , | 2 Comments

Spread the Word about Basic Security

I went with my daughter the other day to set up her bank account. She’s becoming a young adult and this is one of those items we need to get done, especially in today’s world of electronic banking and money transfers. It’s time she takes another step towards being responsible and accountable for her finances.

As we went through the process, one of the steps was her setting up an account with the online presence of this bank. I told her to be sure to use a new password, one not in use anywhere. She agreed, but then said she has 3 or 4 passwords that she uses in different places. I told her that’s not enough, and explained why. This week, we have an article about a MySpace security flaw in 2017.

Most of you don’t use MySpace, but you might have. Even if you haven’t, your kids or parents might use some popular tool, such as Facebook, Snapchat, Instagram, etc. Tomorrow we might discover a similar security issue, or worse, a disclosure of your passwords. If you think it can’t happen, go try a few emails over at https://haveibeenpwned.com. You might be surprised at the results. My kids were.

It’s not that your bank is necessarily vulnerable to hacking a password. However, if you’ve used the same (or very similar password like PasswordCNNdotcom) for your news reading at CNN, then if that company is hacked, it’s not too difficult to take all emails  and then try PasswordChasedotcom at Chase National Bank’s site.

Reusing passwords is a bad idea. I do it for demo accounts across virtual machines, and even that bothers me. Everywhere else, I use a password manager and I have hundreds of passwords, separate ones for each site. My wife is annoyed if she needs a password for any of our accounts and I give her something like “4Gbv8A^f8” on my phone, but I’m not going to make simpler ones or reuse them.

Separate accounts and separate passwords are a mantra I’ve used for years at work and at home. Please spread the word and get those in your life to use separate, strong passwords for their online activities.

Steve Jones

 

Posted in Editorial | Tagged , | Leave a comment

Looking Back

Someone sent me this post on 40 years of programming. It’s a read that laments a few things and complains about many others. Most of the thoughts are opinions, and as such, you may or may not see validity in them. I suspect most of you, like me, see some things are mostly true and some as just false. Since I’ve been in this business for nearly 30 years, many of the comments bring back memories and thoughts about my career as well.

One of the things I do lament is the declining quality of documentation. I’ve seen the volume and detail decline over the years. I wouldn’t want to go back to paper, but I would like to see better examples and more complete examination of the ins and outs of the various functions and features. Far too often I find that there are examples, explanations, or behaviors that are missing. I see the same thing in blogs and articles, which often leap and skip steps in their race to publish.

Focus and detail on a specific topic has been forgotten by too many. Even understanding the way your code works, or the ways the dependencies work has been lost. As more and more people move to using NuGet and pulling down packages, I find too often that many developers don’t quite understand how their systems work. The divide between those that practice DevOps well and those that just release code faster continues to grow. Few learn from their efforts and produce smoother software development pipelines. Most release code faster, losing track of which versions and dependencies that are required.

I still see projects from GitHub and other sources that lack explanations of how to actually compile and setup the project. It seems far too many developers half release software, expecting others to spend time learning what works and what doesn’t. What versions were used, and might be needed again. Maybe in some places the software evolves so quickly, adopting new methodologies and technologies constantly, that developers never need to truly understand the system. They just get things working and then change them.

Maybe that’s why so many people want to rewrite and reproduce software rather than using some existing project and improving on it.

The one thing I do wish was in all languages is a standard way of handling data types and comparing them. I constantly struggle with = and == when I move away from SQL. The comparisons in PoSh make no sense, and I regularly get errors from > until I change to -gt. It seems plenty of language designers want to make their own creation and cause divergence for no good reason. I do wish SQL had implemented == for comparisons, or that they would in the future.

As I look back, some things in computer science haven’t really changed at all. Speed and scales have grown, but many concepts remain the same. However, in other ways, I think we’ve come so far, building amazing systems that are interconnected in ways that we might never have imagined a decade ago, much less 30 years ago.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | 2 Comments

Lots of Resources

I’ve got a small Azure subscription, but there are a lot of resources in my mini lab. Forty seven, to be exact right now.

2017-07-18 16_31_56-All resources - Microsoft Azure

When I look at my bill, it’s got lots of details, but not always easy to understand.I get a breakdown and burn rate.

2017-07-18 16_33_17-VS_MSDN - Microsoft Azure

I can also see details, but tracking these down to ensure I’m making good use of my credits and other charges is hard.

2017-07-18 16_33_27-Costs by resource - Microsoft Azure

I can only imagine what this looks like for a company like Redgate, and the struggles between developers, admins, and finance people trying to determine what we have, use, need, and can let go.

Posted in Blog | Tagged , , | 2 Comments

SSMS is Free

Really, Management Studio (SSMS) is free as in beer. Go download it today.

I got a note from a reader recently that was complaining about SSMS and the lack of MDI support. This individual mentioned that they would undock windows (something I never do) and if they minimzed the parent, the child windows all disappeared. I  wondered if there were still issues with SSMS, so I fired up my version, undocked some windows and played around. Things worked as I expected, and every window was independent of the others. I had no issues working, though I did find I’d forget on which monitor a particular child window would appear.

Last year (2016) we saw SSMS get released as a separate download for SQL Server. The tool has it’s own release cycle, and we saw new updates every other month. As this development team at Microsoft got up to speed with the process and began improving and changing the product, we saw some rough release cycles, but things stabilized a bit late last year. The move to the Visual Studio 2017 shell with v17.x was nice, and I’ve found the latest version to be very stable and easy to use.

After working with Enterprise Manager in my career, then moving to Management Studio and seeing the product languish over the years as SQL Server grew, I am pleased with the direction of SSMS. The changelog is quite impressive, and I expect more things to be fixed and improved in the future. The team is more responsive, and while they won’t fix everything I (or you) want, they are making progress.

The new SSMS is not tied to any version of SQL Server. You can use it with SQL Server 2008 and later (though there are OS requirements). It will work with SQL 2000+, though there may be some issues. If you can, I’d say abandon whatever SSMS version you’re using and get the latest 17.1 release. It works well, is stable, and has lots of fixes for previous issues. Plus, you won’t need to apply those old 2008/2012/2014 patches to your workstation. Just update SSMS on your schedule.

SSMS is free, and while some of us have known this for awhile, I regularly meet people still using the version that came with 2008, R2, 2012, etc. Go download the latest bits today. This is the easiest SQL Server upgrade to justify.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | Leave a comment

Back to the Big Easy and SQL Saturday #628 in Baton Rouge

I’ve been fortunate enough to get to the Baton Rouge SQL Saturday a number of times. I think I’ve been 3 times and am heading back in a week for my fourth.

This is an amazing event, one of the largest in the country. I’ve met people from Texas to Florida to Tennessee, all of whom drive to join the crowd of 500+ developers and database professionals. Held at LSU, it’s also a beautiful location.

If you’re anywhere close, come down for a great, free day of training and inspiration. I’ll be doing two sessions:

Hopefully I’ll see a few of you there.

Posted in Blog | Tagged , , | 2 Comments