Author: marciolsf

About marciolsf

Subscribe Feeds

Getting started on my mini-lab

Posted on by marciolsf

As part of my motivation to do more, I’ve started on my new home-lab. As mentioned on a previous post, this is all running off of an inexpensive Dell desktop! You really don’t need much hardware to start with, just enough to run 2-3 vms (or even just one!). It kind of depends on how you want to continually use your lab:

  • If you’d like to use your new computer for tasks other than running VMs (maybe that’s your only desktop?), then windows 10 is perfectly adequate! It’s more convenient to run windows 10 pro, since it has greater hardware support and remote desktop, but home edition is ok too.
  • You could have a dedicated VM server instead. In this case, you’d want to go with the free edition of ESXi (link —  I think it’s called vSphere Hypervisor edition now?). ESXi uses far less resources than windows 10, so that leaves you more room for the actual guests.

For now, I’m only using windows 10 home. For my VMs, I went with VirtualBox (Link). It’s free, very easy to use and performs great. I could’ve gone with Hyper-V, but that’s not available on Window 10 home.

image

I have this obsession with running windows 3.1 as a VM. The nostalgia is strong.

Downloading the required server software is easy!

  • For Linux, I went with Ubuntu Server (link). I’m an OpenSuse guy at heart, but it seems to me that Ubuntu is taking preferential treatment at Microsoft, so I went with that.
  • For Windows Server, I went with the 180 day free trial of Server 2016 (Link). I believe every DBA should have some degree of understanding of how the operating system is setup, how to maintain it, etc.

Looking back at Windows NT4/RedHat 7 days (ubuntu was still called Slackware back then), it’s amazing how easy it’s become to install a server anymore. Both Ubuntu and Windows Server only require you to select a handful of options, so you’re up and running in no time. Setting up an entire infrastructure (AD/DNS/DHCP) takes considerable more work, but for a basic server, it doesn’t get much easier than that (well, it can, thanks to containers, but that’s a different post).

For my next trick, we’ll download and install Windows Server 2019 (since it’s finally available again). Stay tuned!

SQL Server upgrade shenanigans

Posted on by marciolsf

It’s late at night, I’m watching Yours, Mine and Ours (the 1968 version), and it’s time to upgrade one of our SQL2014 dev instances to SQL2017. We’d already gone through the upgrade advisor, the issues raised (mostly CLR) were not a big deal, so let’s go ahead and get this done. It’s all smooth sailing until I get this error:

Very odd! After a quick googling, I find this page by Pinal Dave –> https://blog.sqlauthority.com/2017/01/27/sql-server-sql-installation-fails-error-code-0x851a001a-wait-database-engine-recovery-handle-failed/

His suggestion is that there’s an issue with the AD service account, and the fix is to switch to network service. This is dev anyway, and I feel a little careless, so why not? Unfortunately, though, it didn’t work! Not only that, when I tried switching service accounts, I got even weirder errors:

 

I absolutely love the %1 to logoff.

After a restart and another attempt at the upgrade, and getting the same results, I decided to ditch the upgrade summary and take a peek at the sql server log itself. This is the actual service log, saved under MSQL12.MSSQLSERVER\MSSQL\Log. In there, I’m able to verify a few things:

  • The server starts
  • It tries to run the upgrade scripts

So that’s good, right? Then I find the real culprit (database names hidden to protect the innocent):

Based on this message, what the issue appears to be is that the upgrade script is trying to upgrade some system objects (you can tell they’re system objects because the begin with MS%), and when that fails, it fails the entire upgrade. Interesting! I know that this particular object is left over from some weird replication issues that have not been properly cleaned up, so it’s safe to drop them. But how? I can’t even get the service to come up, but I know I have a few options:

  • I can look up the trace flags that skip upgrade scripts (This very handy link lists all the trace flags –> http://www.sqlservercentral.com/articles/trace-flag/152989/ ) The correct flag is 992 902 — this flag will skip upgrade scripts in the master database. Once the server comes up, I can fix the issue, remove the flag, then retry the upgrade
  • I can be lazy and rename the data file and log files in the filesystem, then restart the upgrade. In theory, SQL should fail to attach the database (because I renamed the files!), then apply the upgrade scripts on any of the databases that actually mounted, and then complete the upgrade. I’m feeling adventurous, so I try that, and it worked! I have a working instance again.So now I have to clean after my mess. I switched the service account back to the proper AD account, and I also stop SQL and rename the data files to the correct extensions. Once I start SQL again, the database mounts properly… I think it’s odd that it didn’t fail the upgrade scripts, so I can only assume that the upgrade scripts did not run again. I’m not super worried, at this point, because next up is fixing the issue by dropping the orphaned replication objects. Since I have to apply CU11 afterwards, I figure that at that point the upgrade scripts will be applied. 10 mins later, I was done.

What are the lessons learned? First of all, before you run an upgrade, make sure your databases are clean, with no weird system objects laying around. Second, if you have a failed upgrade, take a look at the SQL server log itself, there’s usually a ton of useful information in there to help you get up and running again.

Not a minute to waste!

Posted on by marciolsf

Ok, so I’ve been really excited about this “deployment automation” stuff! That’s not really new, though. I’ve been trying to implement some form of database source control for a few years now, with minimal success. The reasons are various:

  • The tools aren’t all that great. That’s not really their fault, though.
  • There’s not really a true way of doing it, as far as databases are concerned (which is why the tools aren’t that great). Like everything else in database land, the approach sorta… “depends” on a number of different things.

But this isn’t a post about source control or automation! Not yet, anyway. This is a post about how I’m getting ready for it.

We live in an amazing era; tools and processes that were really difficult 5 years ago are easily available now, and work really well. Take Azure and devops, for example! It is really easy to get setup with a free account, download a bunch of tools, and get going. That should be its own post…

I’ve already got my azure instance up and running, so next for me is setup a local environment and a bunch of vms. I picked up a relatively inexpensive machine just two nights ago, a dell inspiron 3670. With a 8th-gen core i5-8400, a 1TB drive and 12GB of RAM, it should provide enough umph to run a couple of VMs and setup some basic integration between local and the cloud. In the next few posts, I’m going to detail how I got things setup.

New (old) directions

Posted on by marciolsf

I just returned from my early peregrination to Seattle, to attend SQL PASS. This was really an amazing year, and I learned a great many things! I’ve decided that, after 11 years as a full-time DBA, it was time to take up the proverbial pencil and share some of the things I’ve learned, and some of the projects I’m working on.

Things have changed quite a bit since SQL2000 days, and there’s a lot of exciting things going on. I figured that writing them down will help me keep track of how I’ve accomplished them, as well as share the knowledge with others. So I’ve repurposed the old site, dusted the cobwebs, made a few tweaks, and now I’m ready to go! I haven’t actively written about work since I left PCMagazine all the way back in the 90’s, when I worked for the Brazilian edition of PCMag, so I’m a little nervous, and more than a little rusty, but hey, what could possibly go wrong?

Trees and snow

Posted on by marciolsf

Here’s a picture I took while camping this last week, at Heber Valley. It was cold!

I liked the contrast between the two trees — One was white and crooked, with a bunch of branches pointing left. The other had a darker bark, much fewer branches, and all pointing to the right. I’m sure someone can derive some kind of symbolism out of these two. What’s yours?