SQL Server with Mr. Denny


June 27, 2014  5:19 PM

Recommended reading from mrdenny for June 27, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Big Data, Cloud Computing, Net Neutrality, SQL Server, T-SQL

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: SQLBalls also known as Bradley Ball

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny

June 26, 2014  4:50 AM

SQL PASS 2014 Here I Come

Denny Cherry Denny Cherry Profile: Denny Cherry
Hyper-V, Server virtualization, SQL, SQL Server, SQL Server 2014, VMware vSphere, Windows Azure

I’m pleased to report that I’ll be presenting at the SQL PASS 2014 Summit this year. I’ll be presenting two breakout sessions this year. The first session that I’m presenting is “Optimizing SQL Server Performance in a Virtual Environment” where we’ll talk about the various ways that we can make SQL Server run better in a virtual environment. My second session is a newer talk titled “SQL Server 2014 and Azure Integration” where we’ll be talking about all the new features where you can use Windows Azure along with your on premise SQL Server 2014 installations.

I hope that you’ll join me at the SQL PASS summit. To give you a good excuse to get registered RIGHT NOW I’ve even got a discount code for you where you can save $150 (US) off the current cost whenever you sign up. That code is CCSUM14. Just use that when you register for the conference and it’ll knock $150 off whatever the price is when you register. The sooner you register the cheaper the cost will be.

Denny


June 20, 2014  8:12 PM

Recommended reading from mrdenny for June 20, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Cloud Computing, Managers, Recommended reading, SQL Server, Storage, Storage virtualization

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: DBA_ANDY also known as Andy Galbraith

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


June 18, 2014  4:00 PM

Getting Replication Distribution Back After a MASSIVE Failure

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server, SQL Server 2008 R2, SQL Server 2014, SQL Server replication
Distribution

Image from Shutterstock

Recently I had to upgrade a SQL 2008 R2 server to SQL Server 2014 on the fly, because the server in question was the SQL Server replication distributor and a couple of databases which were being replicated were being moved to a SQL Server 2014 instance. The Distributor’s upgrade didn’t go well.

After I got SQL up and running and happy with SQL 2014 CU1 it was time to get replication running again. The first thing I wanted to deal with was getting the SQL Agent jobs back up and running. SQL Server had decided that it was going to put the database files into E:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA. The first thing that I did was backup the folder as I was going to be playing in here, and I needed a backout plan in case things went horribly wrong.

Backing up the folder is easy. You stop the SQL Server services (SQL Server and the Agent) and copy the folder. I then grabbed the msdb database files from the SQL 2008 R2 folder and dropped them into the SQL 2014 database folder and restarted SQL. To my surprise this actually worked. All the jobs were listed as expected.

Next step, getting the distribution database up and running and getting SQL Server to think that replication has been running perfectly for years. The first step of this was to make a backup of the replication database files. If something went wrong, I needed a copy of these to restore from as the only backups that I had were missing transactions as those transactions weren’t backed up as the entire upgrade process was supposed to be smooth.

Attaching it was pretty straight forward. I just needed to run through the distribution setup wizard and get the T-SQL for it, then change the database name to match the old I’d been using.

Some permissions needed to be granted, and most importantly the linked servers needed to be added in the correct order. Once you’ve got them in there you can’t take them back out easily. I ended up with them in the wrong order so I had to switch the ID numbers around by putting the instance into single user mode from the command prompt and editing the sys.servers table manually via T-SQL.

Once that was all straightened out, the replication agents all came online and everything pretty much started working.

Denny


June 13, 2014  5:05 PM

Recommended reading from mrdenny for June 13, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Internet, Microsoft MVP, Recommended reading, salary, VMware vSphere, VMware vSphere 5.5, XBOX

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: sqlpass also known as PASS

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


June 11, 2014  4:00 PM

Any Little Thing Can Kill a SQL Server Upgrade

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server 2008 R2, SQL Server 2014
Fail

Image from Shutterstock

So I was going along upgrading a SQL Server 2008 R2 RTM server to SQL Server 2014. I had to install SQL 2008 R2 SP2 or newer before I could upgrade. That’s fine, I can live with that.

Then I get to the SQL 2014 installer. I get all the way to the end, when it fails. All because the folder E:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Jobs isn’t there (SQL is installed on the E drive). All the other folders were there, just this one wasn’t. Now honestly I have no idea why this folder even exists. To the best of my knowledge and remembering there’s never been anything inside of it in the 15 years that I’ve been working with SQL Server. God forbid the installer just create this folder and move on, or I don’t know check to see if it exists during one of the three rules checks that it does during the install process.

So fine, I created the folder and reran the installer. Only to be created with this annoying screen.

upgrade

According to this annoying little screen I’ve got two components to upgrade, but it says that there’s no components available to upgrade. So that’s awesome. Way to be consistent Microsoft.

 

Next step way to try and force the upgrade via the command line. Pretty straight forward upgrading via the command line. Only takes a few switches.

cmdline

Once that was done the install was totally hosed. SQL wouldn’t start. It would just give me a warning about a corrupt installation. Attempting to start it from the command line wouldn’t help either. Hell the command line upgrade process installed the bits and removed the 2008 R2 bits, but didn’t bother to change the service to point to the new files, which apparently is fine because the rest of the SQL install is hosed now.

Repairing the installation did no good, because the installer couldn’t find a valid installer on the server.

The upgrade process couldn’t find anything to work with either.

The next thing that I tried was to add the features that I needed (the SQL Database Engine) through the normal install wizard so add features to an existing installation. At this point the SQL installer didn’t even see an instance on the server, so there was up add features option. I was simply prompted to do a normal install of SQL Server 2014. When I went to install it stopped me from using my Instance ID which was MSSQLSERVER because it was apparently already in use by the SQL Server instance “MSSQLSERVER.INACTIVE”, what ever the hell that is.

Fixing this required using this blog post which talks about how to get rid of this instance manually. If you read the instructors here and think “damn that’s crazy”, yep you’re right it is.

So once that was done I went through the installer yet again. That got me through the installer. I was even able to give the installer the data folder of the old SQL 2008 R2 folder so that it would be able to pick up all the databases when it came online.

The database installed successfully. However it didn’t pick up the databases and attach them. The database that I was really hoping that it would pickup was the distribution database for replication so that I wouldn’t need to recreate all the replication by hand. How I deal with that is for another blog post.

The moral to this post is that apparently you need to make sure that every single folder is where it should be before you try and upgrade SQL Server from one version to another. Otherwise a 45 minute process could easily take you hours to get through.

Denny


June 6, 2014  5:02 PM

Recommended reading from mrdenny for June 06, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
NUMA, Recommended reading, SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: RechousaPT also known as Pedro Martins

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


June 4, 2014  7:00 PM

SQL Saturday Houston Slides Posts

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server, TechEd

The weekend before TechEd North America I had the pleasure of speaking at the Houston SQL Saturday. I present two different sessions at SQL Saturday Houston.

Table Indexing for the .NET Developer
Storage For the DBA

You can download the slides for both sessions from the links provided above. Sorry they took so long to get posted.

Denny


June 4, 2014  4:00 PM

Hekaton and Your Transaction Log

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL performance, SQL Server, SQL Server 2014, SQL Server transaction logs

Hekaton (aka. In Memory OLTP Tables) is awesome. Things run very fast through the system, pushing you to 98x the speed of normal tables.

But be careful. You’ll need to know well in advance how much transaction log space you are going to need. You see there’s a little gotcha that I ran into recently (thankfully in a lab and not a production server). If the transaction log fills because say the hard drive of the server runs out of drive space, the transactions don’t fail to write, the database goes suspect.

Neat trick hugh.

So you’ll want to make VERY sure that you’ve got PLENTY of disk space for the transaction log and that you are backing that bad boy up often otherwise some really bad things are going to happen.

You’ll need to be even more careful if you are using Availability Groups as the Availability Groups feature queues up the data which needs to be replicated within the transaction log. So if a replica goes offline for a while you’ll need to have enough space on all the servers for the transaction log to grow until someone can get to the Availability Group and take the failed replica out of the configuration.

Denny


May 30, 2014  1:50 PM

Recommended reading from mrdenny for May 23, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Database Administrator, Database deadlock, Isolation level, Mobile security, Recommended reading, SQL Server, SQL Server Agent, SQL Server Agent Jobs, VMware

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: PASS_RM_Canada also known as PASS RM Canada

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: