Skip to content

The Case for Upgrading

I heard from a member of the community that’s still on SQL Server 2005 recently. Yes, 2005. That’s an old, unsupported version, but they’ve got some Access/VBA apps that work well and aren’t worth investing a lot of development effort in. The person noted that they could only upgrade to 2008 R2 without rewriting code, and they didn’t want to move from one unsupported version to another.

I suspect a few of you are in this situation, though it seems many people I know that still run 2005 (or 2000), also have newer versions of SQL Server running. As there is the need, or opportunity, to replace and upgrade instances, they do so.

In this case, it seems there’s not good case to upgrade. A change to newer code would be expensive, but also licensing costs have dramatically risen. If the existing hardware were an older 4CPU (licensed), perhaps dual code x 8GB RAM system from, let’s say, 2008 ish hardware, that’s a quad core machine. While you might be able to run on fewer cores with today’s processors, you’d still be looking at a quad core, single CPU as a minimum for licensing and hardware.

There’s also the decision of upgrading to 2014 with new code. That could be a significant effort, with resources spent here instead of on new development. Is that worth it? I think it’s hard to decide without knowing more.

Is it worth it to upgrade to 2008 R2? I’m not sure. While you get some enhancements, you’re still moving to a version that will go out of mainstream support in a year or two. I’d say this isn’t worth the cost.

Ultimately I think Microsoft is shooting themselves in the foot a bit with the minimum core requirements and larger licensing costs, without giving customers flexibility. I think Microsoft would be better served by letting customers license the scale they need.

Steve Jones

The Voice of the DBA Podcast

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

Growing Pains for the Networking Dinner

A few years ago Andy Warren had this idea for a networking dinner. We had been chatting about all the parties and events at PASS, with many of them limited to select individuals. We talked about trying to get more people involved in the community and giving them something to do, so they wouldn’t get stuck in a hotel, as Tim Mitchell did years ago.

This has been a success, but we have growing pains, and we’re looking for some ideas from people in the community, especially those of you going to the PASS Summit for the first or second time.

Skip the history part and drop down if you want to know the issues.

History

We decided we’d host this as a meet-up, just publishing a location and seeing who showed up. We choose a small restaurant in the Pike Place Market that wasn’t busy on Monday night and put the word out. Evidently we know a lot of people because the place was packed. The staff struggled, service was slow, it was hard to move around, but it was fun.

We moved the location to the small mall and the Gordon Biersch in downtown Seattle the next year and sent a notice out earlier. Once again, Andy and I met people at the door, tried to pair them up in groups of four, and let them eat. Once again, it was a bit of a madhouse.

Last year we had a reservation at Buffalo Wild Wings, but apparently some wires got crossed and they lost our reservation. Andy couldn’t make the Summit, but fortunately some friends helped me out, finding a good spot at The Yard House. A couple of us greeted people at BWW and sent them down to the Yard House. It wasn’t ideal, but it worked.

Issues

This year we’re having some trouble nailing down a location. The invitation is up, as is the EventBrite, but we don’t have a location. Here are a few of the issues.

Busy Places – A few places we’ve checked are busy on Monday nights with Monday Night Football promotions. Many don’t take reservations. We could just go, and we’ve asked them if they might bring in extra staff, but we worry that the waits will be ridiculous there.

Reserving a Bar – We did find a couple places that take reservations, but they’ve asked for $600+ for the space, before food and drinks. Andy and I don’t mind spending some money on this, but we weren’t really looking to rent space at $300 apiece.

Possible Solutions

This is where you come in. We’ve talked about a few different ideas, but we don’t like any of them. Let us know what you think.

Using the Convention Center

We could probably get space from PASS. However, food and drink is expensive in the convention center, it’s not great, and it’s a pain. Plus we’re spending all week there, so we wanted to get out.

Finding a Sponsor

I work for Redgate Software, and both Andy and I have good relationships with lots of vendors. We could try to get a sponsor for the event and cover the cost.

However.

If Redgate sponsors, someone will be upset. If someone else sponsors, my boss might be upset. However I’m OK with most of that. What neither Andy or I want is someone using a casual networking event to promote something. We don’t really want to be in the stadium name game and sell the name of the event. We don’t want to push emails out to attendees from a vendor.

In short, we don’t love this plan.

Charging Attendees

We’ve gotten around 200 people each year, and I bet we’ll get the same. Certainly we could charge $3-5 for tickets, and I think people would pay. We’d rather not, but it’s an idea.

Raffles

We could raffle off something. Prizes cost money, however, and they could easily turn us more upside down than we are already for the space. We did think about raffling off easy things, like:

  • a private dinner with Andy or I (or some well known volunteers) – We’d fund part of the dinner, or ask our hosts to donate this. However then we’re asking others to spend their own money.
  • some old swag, like some of my Friday shirts. Not sure how much money this raises
  • Some mentoring time – Perhaps some dedicated,private time for mentoring from Andy, myself, or others. Personally I don’t like this as I think mentoring is a gift, and gifts should be given, nor purchased.
  • ??

Staggering Times

We’re already getting tickets reserved (no charge), so we hesitate to change things, but one idea is to limit the number of tickets to a set number every half hour. Say 30-40 people, with multiple sets of tickets. We’d probably do

  • 5:30 – 30 tickets
  • 6:00 – 30 tickets
  • 6:30 – 30 tickets
  • 7:00 – 30 tickets
  • 7:30 – 30 tickets
  • 8:00 – 100 tickets

This might make it easier to just pick a restaurant, but it doesn’t mean that there won’t be waits or that any additional staff will be on hand.

Multiple Locations

We’ve thought about picking 3-4 places in a small area, having attendees meet us somewhere, and we’d pair them up and send them out to restaurants, round robin style. This could work, but I dislike splitting up the crowd.

Thoughts?

A Good Solution

We haven’t come up with  a good solution, but we’re still looking. If you’ve got ideas, especially if you’ve been to one of the other dinners, let us know.

Ultimately this is about getting people to meet each other and interact, so we’d like to keep it low key, low cost, and casual.

Better Test Data Domains with SQL Data Generator

I’ve been working more with SQL Data Generator (SDG) because it solves some problems that many software developers have with test data. Often each developer needs to create their own set of test data, with these being the common actions:

  • Use a backup of production, perhaps old.
  • Randomly insert a few values each developer comes up with
  • Use random data from SDG, a new project each time.
  • Load a known data set from production or test systems

While I think small amounts of random data work well, I think the data should reflect (somewhat) the types of data in production. Totally random strings don’t work, but similar words, structures, etc. make sense.

However having a consistent set of data for each developer is a great idea. SDG can consistently generate data sets, but to make them meaningful, you might want to have control over the types of data inserted.

Using Real Words

I talked about the difference between random words and real words. However, what if you want to include specific types of items?

Let’s evolve some data. If I have a varchar(500) column, SDG defaults to this:

[A-Z0-9]*

Which gives me this:

2015-08-28 10_03_55-New notification

That’s not great if I wanted to examine specific rows and determine if they were being returned by a query. This is just too random and hard to verify.

However I have options. For example, I could use the “Insert File List” item. This gives me a list of files that could be helpful. In this case, let’s choose “Color”.

2015-08-28 10_05_33-SQL Data Generator  - Fun_Article_Titles_Words.sqlgen

I see this in the RegEx box.

($”Color.txt”)

Now my test data looks like this:

2015-08-28 10_06_14-

What’s in “color.txt”? Let’s see. The file is under the Data Generator 3 folder, in a Config location. I see lots of XML and text files.

2015-08-28 10_08_09-Config

If I open Color.txt, I see what I expect.

2015-08-28 10_08_17-Get Started

Now, let’s experiment. Let’s create a SQL Server file. I’ll put values in like this:

2015-08-28 10_08_17-Get Started

I need to change permissions on the config folder to allow saving, but I put it there.  I also had to close and re-open SDG to pick up the new file.

Now I’ll add that to the RegEx box.

2015-08-28 10_15_44-SQL Data Generator  - Fun_Article_Titles_Files.sqlgen _

That’s cool. What if I made a file with a number of random words in it. Like a dictionary of sorts. I could do this. I create dictionary_small.txt.

2015-08-28 10_22_10-Get Started

Now I include that a number of times.

2015-08-28 10_23_17-New notification

Those are some great descriptions. I’m sure I could have fun with this in other ways as well. Let’s create some good and bad data for a cleaning operation.

2015-08-28 10_28_38-New notification

The ability to include data from flat files is a great option in SDG for putting together a data set that developers can actually use, understand, and count on for loading up new databases or tables, especially when creating quick code branches to test something out.

Software Estimates

We really stink at estimating the amount of time that we’ll spend on building software. However I’m not sure that we’re much worse off than many professions. If you ask a doctor how long it takes to get better, do they know? They do in some small, often seen, well documented issues, but in many cases, they don’t really know. Can lawyers determine how long it takes to solve a case beforehand?

It seems the more accurate estimates typically come from places where the problem is fairly simple and very well known. However that’s probably true in software as well. If I asked you how long to write a new blog site, I bet many developers could come up with a close estimate.

The idea of telling a computer to do x seems more bounded and contained than other professions, but it’s really not. This is often because developers don’t get a complete statement of all the requirements from clients. However this also isn’t really any different than many professions. How many of you know exactly how you want a house built, or legal matter settled, or the specifics of medical treatments and effects?

I read an interesting piece on the idea that developers should just make small estimates, small changes, and move forward. They should be willing to undo, or redo their efforts as needed. That’s one of the benefits of Version Control Systems (VCS) and the great developers take advantage of the possibilities.

While there are certainly problems with moving too fast, there should be plenty of reviews, tests, and other steps in the process that keep the quality of code high. Letting developers push forward, experimenting, and building sections of code quickly can help continue to push your development effort quickly. As long as they’re willing to undo and redo their efforts as they get closer to the end product.

Steve Jones

The Voice of the DBA Podcast

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

Upgrading a SQL Azure Database to v12

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

I was looking to test some of the Azure v12 capabilities, but I needed to upgrade a database. Here’s a quick look at how.

In the old portal, you’ll see your databases.

2015-08-14 13_49_10-SQL Databases - Microsoft Azure

I love that there’s no information on database version. I’m sure the Azure people love this, but I don’t. Very, very annoying.

If I pick one of these, and go to the “Quick Glance” items on the lower right, I’ll see the “update to v12” message.

2015-08-14 13_49_34-SQL Databases - Microsoft Azure

However in the new portal, it’s different. At first glance it looks the same, but zoom in.

2015-08-14 13_51_00-SQL databases - Microsoft Azure

Now you can see the icons are different. I couldn’t read the 12 at first, but I did notice the color difference.

2015-08-14 13_52_12-SQL databases - Microsoft Azure

Now I can go into the properties of the database and see those. Mine said v2 here, which was strange. Anyway, pick that.

2015-08-14 13_54_02-Movies & TV

You’ll get a new blade, which will show you the upgrade benefits. Select “Upgrade this server”.

2015-08-14 13_54_07-Latest SQL database update - Microsoft Azure

Now you get a blade to upgrade the server.

You must type the server name, not the database name, to upgrade. Note that all databases on this server will be upgraded. Be aware of that. However, it’s not the full server name.

2015-08-14 13_54_48-New notification

It’s just the host, but a tool tip will prompt you if you mess that up.

2015-08-14 13_55_00-

Once that’s done, the upgrade will start on the blade. You can close this.

2015-08-14 13_55_07-Movies & TV

The upgrade will also appear in the main blade for the database. These are scheduled, not real time.

2015-08-14 13_55_14-Movies & TV

However, within about 3-4 minutes, mine changed to “upgrading” from the schedule notice.

2015-08-14 14_00_05-Movies & TV

This was a small database, with just one table in it. However the complete upgrade took awhile. The database is available and usable while this is happening, but it does take time to complete. I think mine took about 15 minutes before it was done, but I wasn’t watching the entire time, so I could be wrong.

Once it’s done, it has the new version

2015-08-14 14_04_30-Mytester - Microsoft Azure

Easy enough, and if you need to upgrade your systems, here’s a simple way. If you have lots of them, I’d use PoSh.

SQLNewBlogger

This post didn’t take that long to write. I had the database ready, and I took a few screen shots then started the upgrade. I wrote most of this while waiting for the process to complete and then went on to other work and came back for the final screenshot.

Easy enough, and you should practice this and document it. You’ll have some idea of how to use Azure if someone asks you to later along with some notes for yourself.

References

None

No More Foreign Keys?

I struggle with removing FKs as a general idea. It’s presented in a piece from a developer. It appears to me that if you do this, you are merely moving the problem, and potentially causing other issues. Apart from Grant’s response on performance, which is very true, there are other challenges with microservices and separate databases for each service.
Let’s say we are doing something trivial, like a simple e-commerce site. I could have a separate database for products, one for orders, one for shipping actions, one for customers, etc. However as I scale, I’m not using intra-database queries or FKs/joins/other techniques to verify information for an order. Now I’m sending messages. At small scale, these are trivial. At large scale, my network traffic and messaging is vastly growing and potentially a bottleneck.
Also, since there is a “products” database, and an “orders” database and a “customers” database, I still can’t necessarily scale out these microservices beyond their own machine. Perhaps I can scale them higher in terms of each being as large a machine as I can have now with a single Oracle/SQL Server/MySQL/etc. box, but I’m still have a scaling issue. I also now have a new messaging problem I need to manage and architect. If I lose one database, how gracefully can my application degrade or does it start to fail with unforeseen interactions? Do I create more frustration when customers cannot place an order because we can’t find the customer db or because the site is down?
Certainly there are domains of problems that would work fine here. Spotify is probably a good example of this. There may be e-commerce systems that allow this, perhaps being willing to notify customers after some time (hopefully minutes, perhaps hours) that a product is out of stock because the message from the orders database didn’t get to the application. There are certainly ways to copy and replicate some  data, assuming that “fresh enough” works for your application. However that’s a business decision more than a coding decision. This also means that you also have a new load on your system to move data. I also think the more you work with singleton rows and single objects in an application context, the less you need RDBMS capabilities.
Ultimately I think FKs and strong RDBMS systems work very well in many situations. They work less well in many situations and your domain may qualify for those areas where a NoSQL, a multi-db, or other architecture works. We should certainly investigate and write about where things work and don’t work, but I wouldn’t assume the RDBMS with it’s PKs and FKs isn’t a valid, well thought out and incredibly useful architecture.

Steve Jones

The Voice of the DBA Podcast

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

Adjusting Model

In July the T-SQL Tuesday theme was defaults. There were lots of posts about various items, but I found one by Kevin Kline interesting. Kevin noted that the settings for model are not necessarily what many of us would want in production.

Is that really the case? This week I wanted to take a poll and see if many of you actually do create defaults for your new databases by changing model that are different than what Microsoft has done.

Do you change the model database?

Certainly there are some people that have create scripts with various settings for their databases. Some people prefer to customize the setup each time, based on the purpose of the database. Still others have scripts that alter the database to change settings after a someone has installed the instance.

There are any number of ways to ensure you have good settings for your production (or other) databases. Let us know this week if you have a step to change the model database when a new instance is installed.

Steve Jones

The Voice of the DBA Podcast

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

tSQLt in Azure SQL Database

I was excited to hear about the v12 Azure databases supporting CLR assemblies. Mainly because I’ve been doing testing work with tSQLt and wanted to run tests in an Azure database.

I upgraded a SQL Azure database to v12 and connected in SSMS. My first step was to open the tSQLt file.

2015-08-14 14_27_22-Start

I had the file open, connected to Azure. This was the easy part. Now I need to actually send the batch across and compile the code:

2015-08-14 14_27_33-Start

My next step was to execute it. However that didn’t work as well as I expected.

2015-08-14 14_27_58-New notification

There are multiple errors here, but it’s possible that one error causes others. I pinged Sebastian Meine, the creator of tSQLt about the External_Access and he noted there was only one method that needs it.

So I decided to make a change. First, a search.

2015-08-14 14_28_18-tSQLt.class.sql - dkranchapps.database.windows.net,1433.Predictions (sjones (54)

That got me the assembly installation.

2015-08-14 14_28_28-Start

I decided to try and change this to something that’s contained inside the database. Since Azure is a bit of a black box, I thought safe was the way to go.

2015-08-14 14_28_42-Start

With that change made, I compiled the entire file again. This was the only change I made.

2015-08-14 14_30_18-Movies & TV

That’s cool, but does it work? I connected in Object Explorer and then opened SQL Test. I saw my database, and I could create a test.

2015-08-14 14_31_36-Cortana

However I got an error on the connection and creation of a procedure.

2015-08-14 14_42_31-Movies & TV

Even from Object Explorer, I refreshed the procedures, but got this:

2015-08-14 14_42_44-Microsoft SQL Server Management Studio

This appears to be a SQL Server 2014 RTM error. Actually I was on a CU, but not SP1. It is supposed to be corrected in SP1.

However the procedure was created, and I could alter it.

2015-08-14 14_43_50-Movies & TV

More importantly, I can execute it.

2015-08-14 14_44_04-SQLQuery4.sql - dkranchapps.database.windows.net,1433.Predictions (sjones (52))_

If I change the code.

2015-08-14 14_44_23-Movies & TV

Now it fails.

2015-08-14 14_44_33-SQLQuery4.sql - dkranchapps.database.windows.net,1433.Predictions (sjones (52))_

It appears that tSQLt can now work in v12 databases in Azure, so start adding those unit tests to your database projects.

A Clock With Benefits

One of the difficulties I think that developers face is that so few people understand what actually goes into producing software. The end result that a user sees can mask the effort and complexity of the underlying code. A person might see one application that required months of effort and not understand how it’s different than a similar (in their view) application that was prototyped and completed in a week.

I’ve seen a few efforts to try and explain how code is written, and I think the huge piece by Business Week was a really good explanation for the layman. It tries to simplify concepts, and does so in a humorous way. I suspect that this piece might actually help our clients and customers gain some appreciation for the difficulty of producing a basic application.

However I think we need some additional articles like this to help explain our database world. While there are some good, basic pieces on what a database is, we don’t necessarily help anyone understand the complexity of assembling disparate data, especially at scale.

I don’t think end users would care much about why database performance can be an issue, but I do think that some well written, easy to read, enjoyable pieces on the issues of writing poor database code might give developers some appreciation for why indexes matter, what RBAR does to performance, and what solutions might help their code shine.

No one wants software written poorly, and no one wants deadlines missed. However the world of software development is complex and the more that we can help our clients understand this, the less time we waste on explanations.

Steve Jones

The Voice of the DBA Podcast

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

The Log Chain

I ran across a question recently from someone that received a similar message to the one showed in this image:

2015-07-30 12_02_22-SQLQuery3.sql - ARISTOTLE.master (ARISTOTLE_Steve (62))_ - Microsoft SQL Server

In this case, the log chain is broken. I’ve received this message, usually from a script that is automatically restoring all the logs in a folder. I’ve also had clients call me with this message, saying their log shipping system is broken.

What has happened is that you (or someone) is restoring the logs out of order. Each log backup forms part of a sequence that we call the log chain. The restore process requires that each log backup be restored in the same sequence in which is was created.

In the error message above, SQL Server is letting you know that the Log Sequence Number (LSN) contained in the file from which a restore was attempted doesn’t match the last LSN in the restored database.

Fixing This

The fix is easy; restore the correct files in the correct order. In this case, I knew I’d taken 3 log backups, and I was restoring in the incorrect order. I’d restored log backup 1, and was now trying to restore log backup 3.

If I restored log backup 2 (using with norecovery), the restore would work. I could then restore log backup 3 and eventually bring the database online.

If you can’t find the correct log backup, then your database is still usable. Just run RESTORE DATABASE WITH RECOVERY, and you can access your data. However the data will only be correct to the point in time of the last successful restore.

That could be a serious problem if you are missing a log backup early in your restore sequence. It could be a career limiting problem.

Always be sure you keep all log backups since the earliest full backup you keep handy for restores and protect those files. Losing one could be a major problem for your organization.

Follow

Get every new post delivered to your Inbox.

Join 5,380 other followers