We had an issue with an Oracle Linked Server today and I documented the fix. So I thought I’d put together a doc that shows how to configure a Linked Server to Oracle from start to finish. And while I’m not a fan of Linked Servers in the least, sometimes you just don’t get to dictate these things.
I hope it helps somebody.
Configuring Oracle Linked Server
I was helping someone out with something so I thought you guys would like to have these 2 short lines of code that can be pretty useful. Here I’m going to show you how to check your DB for tables that have triggers, and then for tables with no indexes. Do with them what you will.
First, let’s talk about triggers and how to avoid them and why.
Triggers are a tricky thing to support becuase the complicate things. They can be nested and you won’t even realize it. Their firing order isn’t even guaranteed. You can specify the first trigger to fire, but no ordering after that. So you could see intermittent perf problems from both of these aspects of triggers. And the reasons I usually see people putting triggers on tables aren’t even valid. They need something to happen in another table like a audit or maybe they even need to change a value in the current row. That’s the one that drives me crazy the most. If you need to update a value you just inserted, then just change it before you insert it. Inserts should be as fast as possible and putting an update inside a trigger for a row you just inserted is just stupid. Put that code in an SP and do whatever you need in params so by the time you get to the insert everything is as it should be.
And if you need to do something in another table, like auditing that an action took place, then again put that code in an SP and just do the 2nd table write after the main insert. This really isn’t hard to figure out. In my career I think I’ve seen maybe a couple honest uses for triggers and I can’t even remember them right now. But pretty much every time you need a trigger, you would be better served by putting several statements into a single transaction in an SP. At least someone could easily see what’s going on and you can tightly control the order of ops.
So to find all the tables in your DB with triggers, just go to the tables node in PS and run the following code. Once you get it you can do anything with it you like.
dir | ?{$_.Triggers -ne $NULL} | ft schema, name, triggers -auto
Easy, right?
Now, if you do a get-member you’ll see a handful of properties that give you info on triggers.
dir | gm
HasAfterTrigger
HasDeleteTrigger
HasInsertTrigger
HasInsteadOfTrigger
HasUpdateTrigger
And of course you can use those, even for what we’re trying to do here, but it makes the code a little long to be practical. The problem is you have to compare all of those properties.
dir | ?{$_.HasAfterTrigger -eq $True -OR $_.HasDeleteTrigger -eq $True -OR $_.HasInsertTrigger -eq $True -OR $_.HasInsteadOfTrigger -eq $True -OR $_.HasUpdateTrigger -eq $True} | ft name, triggers -auto
Both of these lines of code do the same thing, but one is just less tedious than the other.
And you don’t have to filter it at all. You’re free to just look at the raw data if you like:
dir | FT schema, name, triggers -auto
Ok, that’s it this time.
I just got this question in the user group and thought I’d write a blog instead of just answering a sub-set of users who could benefit from it. The question was:
I have customized the values of the Auto growth according to the size of the database and the rate at which it grows. I have noticed that Auto growth kicks in about every 3 months – 6 months on an average. Is that OK? I have read articles where the advice on it ranges from “Auto growth is OK” to “Auto growth should kick in only during emergency”.
This is one of those topics that comes up again and again, unlike AutoShrink which I hope is settled by now. I suspect it keeps coming up because there’s no real solid answer.
Ok, so whether or not to AutoGrow your files. I’m going to talk about both data and log files together unless there’s a difference. So unless I call one out over the other, I’m talking about them both.
You should definitely use AutoGrow. And you should definitely NOT use AutoGrow. That’s my way of getting around saying “it depends”.
It depends on a few factors really.
1. What you’re going to do with the files.
2. How big your environment is.
3. How many other files are on the drive.
4. How much activity is on the files.
5. Monitoring method
Maybe there’s more, but that’s all I can think of right this second, but you get the idea. Ok, so let’s go through them one at a time.
1. What you’re going to do with the files.
From time to time I step into a shop where the DBAs have bought into this idea that AutoGrowth is bad so they have some job setup to monitor the size and they grow the files manually. Now while that sounds like a good idea, it can cause more problems than it solves. Let’s look at a scenario I’ve encountered more times than I care to try to count. You get a request from a group to restore the DB to a dev or maybe a QA box so they can do some testing. This is a common scenario, right? I’ve done it plenty in almost every shop I’ve been in.
So you go to do the restore and it fails telling you that there’s not enough space on the drive. You look and there’s not that much data in it so it should fit on the drive right? No, not right. The drive has to support the size of the file, not the size of the data. So if you’ve got a 50GB drive, and a 100GB file it will fail even if there’s only 20GB of data in that file. So now what do you do? Do you go beg the SAN guys for more space or do you manage your files in smaller increments?
With AutoGrow you can set your own growth rate, so you can have it grow the files at whatever interval you want. So how is manually growing the file going to add anything to the equation here? And with Instant File Initialization (IFI) you don’t even have to worry about incurring the cost of zeroing out the file unless it’s a log.
Now, for log files specifically, I know some of the top experts say that you can have perf problems if you grow your files too much and get too many VLFs, but honestly that problem really doesn’t come up that often. And logs are very volatile. Lots of things log activity that you don’t realize and I wouldn’t want the log file to rely on me. And again, I can’t stress too much that it really matters what you’re going to be doing with the files. If you’ve got an extra 60GB of space in your log because you’re afraid of VLFs, then you’ll need that extra 60GB on every other system you plan to restore the DB on. And you may not be afraid of the VLFs on those lower-level servers.
2. How big your environment is
Now let’s talk about large enterprise environments. I tend to be in really large shops with hundreds or thousands of servers. And I don’t know about you, but I don’t wanna spend my time managing file growths. Consider my last environment where I had over 900 servers with over 4,000 DBs spread across all of them. And that was just prod. I’m not going to do that kind of analysis on all of those servers and manually grow all of those files. And it’s honestly just ridiculous to even try. There are 2 ways I could solve a problem like this.
I could develop a process where I monitor the free space in all the files, and when it reaches a threshold it grows the file by a certain amount. Hell, that’s just a homegrown version of autogrow isn’t it? So that’s not a solution really.
I could also use autogrow on some of my boxes and manually grow my really important or trouble boxes. And again we’re back to “it depends” aren’t we? What we’re saying here is it’s ok to use autogrow on some servers and not on others, which means there’s no solid answer. You just can’t spend all your time growing files. Use autogrow here unless you have a reason not to.
3. How many other files are on the drive?
This argument may or may not have any teeth… it just depends on how you look at it. The main reason for manually growing your files on drives where you’ve got a lot of other files is for the fragmentation. And here I’m talking about fragmentation at the filesystem level, not inside the files themselves. If you’ve got you files on a drive with lots of other files and they’re all growing, then they’ll be growing over each other esp if they’re growing in smaller increments. So you could fragment your drive pretty easily and that can definitely cause perf issues. So the solution is typically to manually grow the files to a larger size so it reduces the amount of fragmentation you create when they do grow. And that does have merit, but why not just set the AutoGrow setting higher then?
I can see a reason why you wouldn’t. If there are a lot of DBs sharing that drive and they all grow fairly often, then you wouldn’t want to AutoGrow it to a certain size and have it fill up too much of the drive and starve the other DBs. The most logical way around this issue though is too fold:
AutoGrow at smaller increments. Unfortunately, this may put you back in the fragmentation scenario though. If you go this route then you need to defrag the drive on a regular basis and you should be ok.
Split those DBs off onto their own drives. This is the best solution because you get stuff for free. Things like simplified space mgmt., 0% fragmentation, and I/O isolation are all things that come along for the ride when you put DB files off onto their own drives.
However, all that said, if you can’t put the files on their own drives and you’re really afraid of starving the other DB files, then your only real choice may be to monitor the size and grow manually. But this shouldn’t be the norm if you’re in a big shop. Keep this kind of activity to a minimum if you can help it.
4. How much activity is on the files.
This one is almost like the other one, only this doesn’t necessarily rely on what else is on the drive. This counts even if the file is on its own drive. If the file grows a lot every day or every week, then you don’t want to take a chance on missing an email alert or whatever else you use and having the file fill up because you didn’t grow it. So while there may be some exceptions, my skills are better spent elsewhere than growing files manually.
5. Monitoring method
Many shops monitor with 3rd party tools and those tools monitor disk space. However, none of them are smart enough to know the difference between a full drive and a full file. You could have a 100GB file with a 99GB data file on it and the alarm will trip even if the file is only 3% full. And depending on whether or not your monitoring team is friendly, they may or may not help you out by either turning off the alarm on that drive, or doing something so that it knows something about the space in the file. I’ve honestly worked with both friendly and unfriendly teams. So I could either setup an outlook rule to ignore all space alerts (bad idea) or shrink my file back again so it didn’t trip the alarm.
Conclusion
So you can see there are several factors involved with this decision and chances are you’ll have a mixed solution. I’ve worked in shops where I never managed space at the file level, and shops where it was very necessary, and everything in between. For me #1 above is one of the biggest deciding factors. I’m constantly fighting DBAs growing files a lot to be proactive and then we can’t restore to any of the other environments. Even DR becomes an issue because you have to have that space anywhere you restore those DBs. And that’s a lot of extra space to keep on hand for such little return. Don’t get me wrong, I’m not a big fan of thin provisioning either. I think that’s going a bit far, but it’s basically the same thing at the SAN level. This provisioning is AutoGrow for the LUN itself. And the biggest problem I have with it is that they tend to not grow it enough or they set the threshold too high so the file fills up and brings the DB down while you’re still waiting for the LUN to expand. If they can get it right though it’s not the evil it used to be. So what we’re really doing with AutoGrow is we’re thin provisioning our DB files. And that’s actually much easier with IFI because they expand in just a couple seconds. That’s only for data files though. Log files still have to be zeroed out so you can run into the issue now and then where the log file is still growing when the process runs up against the end of the current file and everything stops. Hey it happens. Those are the cases where you might consider manually growing your log files. These would be more DSS type systems where it’s unlikely that you’ll restore it to a different box.
Having huge files can also slow down your DR plan. If you’ve got a huge log file and a 30min SLA, you could easily spend more time than that zeroing out your log file. So you’ve orchestrated that you’ll miss your SLA just by trying to make sure you don’t run into an almost non-existent VLF issue. So you’ve got to consider that too.
So anyway, I hope this helps you at least consider the different factors involved in making this decision. Leave me comments if I’ve messed something up really badly. Or if I’ve gotten something really right. Hell, just tell me I have great hair and call it a day.
I got an email from a user at another company today and he told me about the SQL 2012 upgrade they just finished. And apparently it was a disaster. Here’s the general gist of what happened.
They have a huge app that runs their entire business and the vendor talked them into upgrading to 2012. Originally they were slated to do tons of testing and upgrade probably sometime in november. But they decided to not listen to their DBA and instead allowed themselves to be lead by the vendor who told him that SQL upgrade was easy and nothing to worry about. So they did some perfunctory testing and pushed the upgrade to this past week. I know, smart right?
So this vendor did their upgrade for them and it completed ok from what I know about it. The problems came after the upgrade. Now, I don’t have any real specifics, but I do know that it caused a 10hr downtime. One of the directors asked about the backout plan and he was politely told to mind his own business. Everyone is calling the upgrade a disaster. They didn’t have any way to restore in case the upgrade failed in a really bad way… and that means no final backup, no scripted objects, and no mirrored system. This was an in-place all or nothing upgrade.
Just so we’re clear on this, that’s not the way you run an upgrade. Upgrades take plenty of testing from the DB side, and the app side. You should never upgrade anything without extensive testing. And you should always have a backout plan. Now, what does a backout plan really mean? Because I find that often times the backout plan gets overlooked and I think it’s mainly because they have a hard time defining it.
To me a backout plan means a few different things depending on what you’re after. Let’s take this upgrade as an example. No matter how good you think SQL upgrade is, there’s always something that can go wrong. So at the very least, you need to take a final backup of ALL the system and user DBs right before the upgrade. Make sure you kick everyone out of the DB first because it’s not a final backup if there are still going to be modifications afterwards. That’s a good start for sure, and what I’d consider to be a minimum effort. Here’s a quick list of the steps I like to take for an important upgrade such as this:
1. Copy all system DBs to another location. This just makes restore much easier because with DBs as small as most system DBs, you can just drop them back in their original location and you’re good to go.
2. Script all logins with SIDs.
3. Script all jobs.
4. Make sure I have all SSIS pkg projects at the ready so I can redeploy all pkgs if I need to.
5. Do a test restore of the final backup before starting the upgrade.
6. Script any system-level settings like sp_configure.
7. Script any repl, log shipping, mirroring scenarios.
8. Make sure I have pwords to any linked servers. While I try to keep everyone off of linked servers I have to admit they’re a part of life sometimes. And you don’t want your app to break because you don’t know the pword to the linked server. It’s not the end of the world if this doesn’t happen, but it’ll make life easier.
So basically, the more important the DB, the more of these steps you’ll follow. You need to prepare for a total meltdown and make sure you can recover in as timely manner as possible. As I sit here and write this I feel stupid because it seems so basic, but there are clearly those out there who still need this kind of advice, so here it ia.
And if you have a good test box handy, make sure you test as many of these procedures as possible. Script out your logins, etc and restore them to the test box and see if things work as they should. Deploy your SSIS pkgs to your test box and make sure they run, etc. Don’t just rely on what you think *should* work. Actually make sure it works. This is why some upgrade projects take months to complete. It’s not the upgrade itself, it’s all the planning around it. And while this isn’t a full list of everything you could do to protect yourself, it’s a damn good start.
Happy upgrading.
For a very long time now I’ve preached to my various teams the value of doing your 20s. And I don’t expect most of you to know what that is, so here’s a quick explanation. I believe there’s certain syntax you should know by heart. Things like backup/restore, DB creation, working with user accts, etc. should be so ingrained you never forget them. And the only way to do that is to do it again and again and again. So when I start new DBAs down their road to discovery, I start them on their 20s. And that means writing the command you’re working on 20x a day for 2 weeks. And by the end of the period, you’ve written it at least 200x. Now, in the case of backup/restore, I typically pick a test server with around 10-15 DBs (I often create empty DBs) on it and make them type the commands for every DB 20x a day for 2 weeks. And that means that they’re writing the commands 200x for each DB. And a little quick math tells you that the total they’ve written the backup command by the end is at least 2,000x. Now, name me something you’ve done 2,000x and have forgotten easily.
The whole point of this is to give them tons of experience with the commands in a very short period of time. Innovation and understanding don’t come when you’re struggling to remember syntax and they don’t come if you don’t know any of the options available to you. So by forcing my DBAs to learn like this, I’ve found that they really start to become competent and ultimately they appreciate that I forced this on them. And it’s incredible how simple things start getting when you know what you can and can’t do and now that you know how to code it you’ve got a world of options available to you. And I still find way too many DBAs are button monkeys and don’t know anything that isn’t presented to them in a wizard.
So anyway, the main reason I wrote this is to say that it’s proven itself to me today. We needed to add a PK to a table and I was in a room with a bunch of SQL devs. They started to lookup the syntax and I said, wait, give me a crack at it. I sat down and busted it out without any slips. And the thing is that this is one of the things I’ve practiced a lot. But I haven’t touched it in almost a year so I’m a little surprised to find that it didn’t leave me. Then again, when you do it as much as I have you won’t forget it anytime soon. So my point here is that if you practice enough you can go a few months without doing it and you’ll still be ok. And the funny thing is I didn’t even really remember the syntax myself. My fingers did it for me. I love how muscle memory works. So hey, if you really want to get good at SQL then practice some of the more common syntax. Do your 20s. You’ll really be glad you did. And you’ll get things done a lot quicker cause you’re not always looking up basic syntax.
One of the biggest battles I fight when I teach PS or show it to different companies is adoption. DBAs and managers just don’t get why they should be using PS to manage their environments. And the reason so many of them are slow to adopt is because the right message is very hard to come by. In fact, I see the wrong message all the time. This was driven home again just now as I received my “SQL Server Pro UPDATE” email (read it here). The editorial was about why DBAs should care about PS. It was written by Michael Otey (pronounced Otee) and I’m sorry to say that as the Sr. Technical Director he’s completely missed the mark. He’s managed to relegate the most important management tool DBAs have to a mere sideline player that allows you to combine SQL work with OS-level work. Dude, you’ve completely missed the boat!!!
I’ve said this a number of times and I’ll say it again here. Let’s get the right message out about PS. Let’s start telling everyone that truth about it and that truth is that you’re going to get left behind if you don’t know PS and you’re not going to be able to provide true value to your company.
I’m a working production DBA in a very large shop. And I can tell you with absolute authority that PS is more than just a way to give you some OS-level functionality in your SQL processes. PS is vital to the management of my shop. The primary role of PS is to allow you to manage hundreds of objects at once with just a few lines of code. As you’ve seen in my videos again and again, and in my classes again and again, you just can’t manage any decently sized environment by hand. It’s just too much and it’s accident-prone.
Now, a large environment doesn’t have to mean hundreds of servers. Oh no, it can mean dozens of DBs on a single server. It can mean dozens of schemas in a single DB, or dozens of tables, SPs, views, etc. Or it can even mean dozens of jobs or logins. It doesn’t matter what you’re cycling through. The point is that PS is much more efficient at managing these objects than T-SQL will ever be. There may be a built-in SP for running a T-SQL statement against all the DBs on your server, and there’s even one for running something on all the tables I believe. But there’s nothing for all the jobs, or SPs, or views, or logins, or credentials, or any of the other many objects you can manage natively with PS. With just a few characters of code you can manage all the tables in your DB. And with just a few more characters you can do that same operation against all the DBs on your server. And with yet just a few more characters of code, you can do that same operation against all the DBs across as many servers as you like. THAT’S THE POWER OF POWERSHELL. It’s more than just a way to manage files while you’re doing some T-SQL operations.
A perfect example is adding user accounts to DBs on several boxes. We quite often get contractors who come in and do specific work for us and they need to be given rights to all of the environments for a given application. So this one we have has 500+ DBs on each server. There are 7 prod servers, 3 QA servers, and 5 dev boxes. All of them have 500+ DBs on them. So that’s 15 servers, and at least 7500 DBs that I have to add these users to. And using windows groups will only take you so far because when the next contractor comes in he may need different rights so you still find yourself modifying the perms regardless. The point is I go through this exercise about once every 2mos and PS has made it a trivial matter. And that’s just ONE example.
The time has come to stop downplaying the role of Powershell in SQL Server. DBAs need to be running to it. We’re being asked to do more and more with less time and fewer resources. And there are too many courses being taught, too many SQLSaturday sessions, too many videos (FREE on MidnightDBA.com) for you to remain in the dark any longer. I’ve managed 900+ servers by myself using mainly PS. I’d say it goes quite a bit deeper than being a helper to keep you from using xp_cmdshell.
So we upgraded a cluster from 2008 to R2 tonight. We upgraded the inactive node to R2 SP2 and then went to failover the cluster so we could upgrade the other node. But when we tried to failover it failed. The only thing the critical events for the resource said was that it couldn’t failover. So no help there. The only other error was in the event viewer and it said that the shared component upgrade failed… even though nothing in the upgrade process failed. There actually were other errors in the event viewer but I’ll let the link below cover those. The big important error was Error: 19019.
I remembered something from a few yrs ago that involved a registry fix so I started looking at the registry. After opening regedit I went to the most logical place:
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008
Once there I saw the Setup folder, which seemed like a good place to start. Inside there I saw a key called SQLDataRoot and its value started with E:\
Well, I have a pretty standardized environment so I know the SQL root was supposed to be on D:. I changed the location to D:\ and the SQL Server resource came online instantly.
So again, that registry key is:
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008\Setup\SQLDataRoot
This is not only an interesting fix, it’s also a testament to what having a highly standardized environment can do for you. I could have compared this entry with node1 to see if they matched, but it may not have even been flagged as an issue if we didn’t always install SQL on D:\.
So once I had the fix in place, I had something to search for, so I went to google to see if I could find anything about it out there. I came across this great blog post that even gives more detail on this error, so rather than try to reproduce the entire thing here, I’ll just point you to that post.
So this is why I call myself the bumbling DBA. I don’t actually know anything, but I manage to find my way through solutions somehow. And I know I’ll get lots of comments on this with everyone telling me they knew this cause they’ve seen it 100x and all I can say is bully for you. I’ve only seen it once and it was a few yrs ago. I don’t get out much.
Ok, well I’ve been very busy again and released 3 new PS vids today.
There’s one on dropping tables. I use regex to make this happen, so even if you’re not interested in dropping tables, you can come learn how to do a simple regex.
http://midnightdba.itbookworm.com/VidPages/PowershellDropTables/PowershellDropTables.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellDropTables/PowershellDropTables.wmv
The next one is on truncating tables. Here I just limit it by a specific schema.
http://midnightdba.itbookworm.com/VidPages/PowershellTruncateTables/PowershellTruncateTables.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellTruncateTables/PowershellTruncateTables.wmv
And the last one is just damn good. I show you how to get sp_configure functionality in PS. There’s a trick to it so don’t discard it cause you think you can figure it out on your own.
http://midnightdba.itbookworm.com/VidPages/PowershellServerConfigSettings/PowershellServerConfigSettings.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellServerConfigSettings/PowershellServerConfigSettings.wmv
Here’s another vid. This one is on working with detach in PS.
http://midnightdba.itbookworm.com/VidPages/PowershellDetachMethods/PowershellDetachMethods.aspx
Hey guys, I posted a new video last night on how to get IP and DNS info from your servers. I know there are more ways to do it so if you guys have a way you like better send it to me and I’ll make another vid.
http://midnightdba.itbookworm.com/VidPages/PowershellGetIPandDNS/PowershellGetIPandDNS.aspxhttp://midnightdba.itbookworm.com/VidPages/PowershellGetIPandDNS/PowershellGetIPandDNS.aspx