Importing Excel into SQL Server using PowerShell

Ah T-SQL Tuesday, is it that time again? And the 94th one at that! Wow!

Words: 797
Time to read: ~4 minutes

Update: 2017-09-20 Thanks to Rick Fraser for pointing out I showed a $ServerConnection but hadn’t defined it in the function or separately or at all! Thanks Rick!

Continue reading “Importing Excel into SQL Server using PowerShell”

TSQL Tuesday #93: Interviewing Patterns & Anti-Patterns.

TSQL Tuesday, the brain-child of Adam Machanic ( blog | twitter ), has come around once more and this time it is being hosted by Kendra Little ( blog | twitter ). The topic? Interviewing Patterns & Anti-Patterns.

tsql2sday150x150

Truth be told…

… I have not had that many interviews. A combination of not being that long in the working community since college and staying at the same company for quite a while means that it’s just not something at which I’ve had a lot of practice.

I suppose that I should do one or two, if not to look for a new place to work, then to practice them, see how I measure up, and test my skill.

Mainly though to answer some questions that I have.
Questions about interviewing for DBAs.

I hope you don’t find them too boring or basic.

How do you interview a DBA?

This question is one that I couldn’t really wrap my head around. How do you interview a DBA? If the purpose of an interview is to evaluate a candidate for a position then how do you measure them?

Technical wise, what do you do? Most of the interviews that I’ve been to have involved some aspect of testing, but the thing is there are different types of DBAs, all to do with what they focus on.

Do you judge a DBA, who is focused on Virtualisation, on the intrinsics of SQL Internals?
Or a DBA, focused on Azure, on their knowledge of SQL Server 2005 and when certain T-SQL functions came in?
A company who is looking for a database design expert is going to focus on that and may not care about a DBA’s expertise in HA/DR options.

How do you ensure that you are adequately testing the competency of a DBA?

How do you interview a company?

Interviews go two ways though, and companies can be more wrong that right (it happens).
The question here is when you run into a company with the wrong beliefs, what do you do?

If the company interviews you and says that you’re wrong in saying that TRUNCATE TABLE can be rolled back, what do you do?
If they say that index rebuilds doesn’t update index statistics on the columns in the index, again what do you do?

What do you do if they won’t listen, if they won’t look at any examples, if they won’t see reason when given proof to the contrary?

How do you deal with a company that is incorrect in their basic assumptions and unwilling to learn?

They say that the DBA role is changing…

…and that we, as DBAs, have to learn to change with it otherwise we’ll get left behind.

A concern for me is that maybe the way that we interview DBAs isn’t right, and that it needs to change or it, too, will get left behind.

Unfortunately, like most things, I don’t have the answer yet…

I’m learning though…

 

T-SQL Tuesday #92 – Lessons Learned the Hard Way…

That’s T-SQL Tuesday #92, not 92 lessons learned the hard way

T-SQL Tuesday this month is hosted by Raul Gonzalez ( blog | twitter ) and the topic this month is “Lessons learned the hard way”.

I make no effort to hide the fact that I am not the biggest fan of GUIs, and I’ve been fortunate enough to turn that dislike into an admiration of command line tools. I said “an admiration” not that I’m any good at them yet! I have been fortunate enough to provide a function for dbatools.io (have you helped them out yet?) but just goes to show that anyone can help out, regardless of skill level.

In case you ever wondered where this dislike came from, let me tell you a hypothetical story about…my friend that I used to work with.

Now my friend wasn’t a DBA then, he wasn’t even an Accidental DBA, he was more a “that guy is good with databases, ask him” kind of guy. In short, my friend knew just enough to be dangerous without knowing that he could be.

Back in the SQL Server 2012 days…

…which was either today or 5 years ago, depending on what version of SQL Server you’re running but we’ll say 5 years ago, my friend was working as a SQL Support Engineer for a software provider.

The provider didn’t handle backups, that was all taken care of by 3rd parties. In case something went wrong, these 3rd parties provided the backups and either the software provider, or the in-house I.T. would restore them. (FYI, I’m very cautious of 3rd party backup tools as well).

One Friday, we did a release…

…and eventually a bug was discovered in the release that could have potentially had some data impact (no particular reason to say Friday, I just don’t think you should release on one).

So a plan was made to request a 2 week old backup and to compare the current data against the current production database.

GUI Time…

My friend goes to the Object Explorer, opens the “Databases” node, and sees that there is two databases there; Live ([TheEarlyBird]) and a disused copy of Live ([TheEarlyBird2]) that is a day old and can be overwritten.

Not knowing any better, my friend right-clicks the old copy, clicks “Tasks”, then “Restore”, then “Database…”, and a lovely GUI pops up.

InitialSetUp_WithName.PNG

Now my friend doesn’t know any better, he thinks that the GUI is here to help him and in most of the cases it is. What my friend failed to realize is that there is a difference between helping him and doing the work for him…

Setting Up…

The 3rd party backup file has not yet been retrieved but that stops my friend not! This is a urgent case so my friend forges ahead, thinking that he can get everything set up and ready then all he would have to do is select the file when it was made available.

Files Page:

  • My friend would be overwriting the disused database so this would not need to be changed.

Options Page:

  • Checked the box “Overwrite the existing database (WITH REPLACE)” as we are overwriting the disused database

File is now available…

So my friend goes back to the General Page, clicks the “Device” radio button, and selects the backup file…

WhenChooseDevice.png
Can you figure out what went wrong here?

…and clicks “OK” to start the restore!

Errors! Errors galore…

My friend encounters errors:

Exclusive access could not be obtained because the database is in use.

This confuses my friend as this is a disused copy of the database, the only person who should be on it is himself.

Does my friend go and maybe check out EXEC sp_Who2; to see who else could be on this database? No, remember that my friend knows just enough to be dangerous. My friend goes back to “Tasks”, “Restores”, “Databases”, goes to the Options Page and checks the box labelled “Close existing connections to destination database”….

OverwriteExistingConnections.png
If you figured out the above, you know that this is even worse…

With that, my friend clicks the “OK” to restore the database and continues on his merry way…the dumb fool that he is.

SQL Server 2012 GUIs…

…have this little “optimization” technique where it looks at the name on the database backup file and matches up with the database name.

Now what this actually meant was the moment that my friend clicked the “Device” button, all his work was gone and his destination database reverted to the Live Database!

The first time my friend clicked “OK” to restore wasn’t a problem since there were connections and the Live database wasn’t affected.
But then my friend goes back and clicks “Close existing connections to destination database”…just enough knowledge to be dangerous…

So in summary, what my friend had done was kick every single connection off of Live and then effectively wiped 2 weeks worth of data.

Thank goodness for tail-log backups!

GUIs are good for….

…discovery.

They give you the option to script out the configurations you have chosen. If my friend had chosen to script out the restore, rather then clicking “OK” to run it, maybe he would have caught this mistake when reviewing it – rather than overwriting the Live database with 2 week old data and spending a weekend in the office with 3 colleagues fixing it.

Plus if you ever want to ensure that you know something, try and script it out from scratch.

Failures or Learning Experiences?

There is this saying that…

…there is no such thing as failure

I guess it’s a personal experience but I say that it is thanks to “my friend” that I was able to do 2 side-by-side WITH STOPAT database restores today.

Oh and FYI SQL Server 2012 Enterprise Core Mainstream Support ends today.
I’m very upset about that… 😐