SQL Server Things Someone Running GP Dynamics Should Care About. Part 2

Posted on by

Ok,

So now that you have a documented, tested and implemented backup plan in place what is next?

Checking the health and integrity of your databases, much like your own there are ways sometimes to detect problems before they become big problems, not every problem, not every time but sometimes.

The most direct way of doing this is by running DBCC CHECKDB. It’s is the easiest, most direct way of looking for integrity issues but there are some things you need to keep in mind while running and when running DBCC.

In general DBCC CHECKDB will not find any issues because odds are you don’t have any issues but if you do see an issue reported by DBCC CHECKDB.

First, do not, I repeat, do not run DBCC Checkdb with any repair option unless instructed to do so by Microsoft or someone you trust implicitly.  Do not take the advice given on some web page, blog or forum post that tells you to run any sort of repair stuff. Period, full stop.  If you walk away with nothing else from this entire post, that is your take away…

Secondly, if anyone tells you to run DBCC CHECKDBwith a repair option take a backup first.  Do not overwrite any existing backup, put the backup it it’s file and clearly name it so you know you took a backup of a potentially corrupt database.  Yes, you are backing up a potentially corrupt database, but running any sort of fix may make things worse and you may want/need to have what you had before running any sort of fix.

Third, if DBCC CHECKDB does find an issue, run DBCC CHECKDB again while you Google the issue you found the first time. There is a chance the issue may not appear a second time, this is not generally true with DBCC CHECKDB, but it is worth trying. If it comes up clean a second time, then run it a third.

If you are still seeing errors then depending on the issue you may have to restore from backup, so get your backups ready and accessible if they are not already ready.

Finally, if recovering from a backup is not an option (too much data lost, etc) contact Microsoft  Support for suggestions and next steps. Yeah it is going to cost you money, but it is money you are going to have to spend.

SQL Server Things Someone Running GP Dynamics Should Care About. Part 1

Posted on by

This is going to be a multipart set of posts about things that someone who is administering  Dynamics GP should care about when it comes to their SQL Server databases and server.

The first thing is backup and restore, but actually more about restore.

I am not going to focus on the backups themselves. There is enough documentation about SQL Server backup and the different options available both within SQL Server and third party backup applications that I am not going to go over them here (at least not yet).

Instead I am going to ask some basic questions you should be able to answer about your backup and recovery plan and recovery process.

How much data can you lose in terms of time for it to be an inconvenience, a problem and a situation where someone has to update their resume?

Normally the answer to this question is a function of time such as… Losing an hour is an inconvenience a day is problem and longer than two days is a career limiting move.

Can you answer this question?  Can you backup your answers with data and management  buy-in.  It’s one thing for you to think that losing two hours of everyones work in Dynamics GP is  not a problem it is another thing to have management agree with that.  Establish those numbers with the relevant stakeholders and have them buy into them.

When is the last time you tested a restore?  How long did it take to run?  

A backup plan is great, but you need to test restores of the backups, you don’t have to test every backup but you need to test often enough that you feel comfortable about them.

Just testing a restore is not enough, you also want to get an idea of how long a restore is going to take. Why?  If something bad happens being able to provide a reasonable estimate of how long it is going to take to restore a database is something that will be quite handy.  If you can say “when we tested a restore last week it took 2 hours” then you reduce the odds of someone hovering over your next in 20 minutes asking if it is done.

Also in general practicing a restore is a good idea for the simple reason it keeps you in practice of how to do restores. When you have to restore prod after an issue, you are going to be more nervous, more prone to make mistakes, it is human nature for stress to have an impact on personal performance, that is what makes it, well stress.  Heck I have been working with SQL Server for something like 18 or 19 years now and I still get nervous restoring after a prod issue. Practice helps you work through the nerves and the last thing you need is to appear you don’t know what you are doing on a restore with a boss breathing down your neck.

Practice.

Is the knowledge of how to restore a database a single point of failure?

If it is, fix that, either have more than one person who knows how to do a restore (and has the system rights to do so) or make arrangements with a consultant or someone to provide cover.

Even if Joe never takes a vacation (a bad practice for anyone who deals with financial data btw) he can get the flu.. Make sure more than one person knows how to do a restore.

Do you have a way of being notified when a backup fails?

Regardless of how you take backups, do you have something set up that lets more than one person know (see Joe reference above) that there has been a backup issue?  The last thing you want to find out is that you haven’t taken a backup for a week because the backup disk was full, or the tape drive had an issue. The key on this one is active notification, that is someone gets an e-mail or a page, not that someone has to check every day.

Well that is part one…

Taking ownership of Database Maintenance Part 1 Index Maintenance.

Posted on by

One of the things I have noticed that most applications do a very poor job of is index maintenance for their databases.  They generally fall into one of four categories..

  •  A script or process that rebuilds every single index if it needs it or not. The blunt force method.
  • Instructing you to set up a maintenance job that basically makes you the owner of the blunt force method
  • Nothing, leaving it entirely up to you.
  • A well thought out maintenance script that takes multiple factors into account.

First, let me say that there are a host of issues with the blunt force methods but they do have one advantage. If run frequently enough, they get the job done.  It can be a bit like replacing the sod every week in your front yard instead of mowing, but you end up with a a lawn that is the right height.

But there are several disadvantages to the blunt force methods.  The biggest being that you are doing a lot of work that accomplishes next to nothing.  Rebuilding indexes that don’t need it wastes time, cause unneeded blocking and locks that can get in the way of other processes. Also by having an impact on what ends up the in the transaction log, it can have an impact on your recovery time if you use the full or bulk logged recovery models since rebuilding indexes (even when not needed) can add significant amounts of data to your transaction.

A good index maintenance script should contain at least these things..

  • Something that looks for fragmentation, the best metric for determining if an index needs maintenance. There is variation on how much fragmentation to look for but anything that looks for fragmentation below 10% to 15% is going to do too much
  • Something that checks the size of an index, if an index is not at least X number of pages in size, it isn’t worth bothering with.  The size of X varies a bit I would go at least 30 pages. But something that either has this set or controls this.

That’s about it, there are scripts that give you a ton of knobs to adjust, some that will make a rebuild vs a reorganize decision based off of certain factors and a host of other things.  These scripts are great, the only problem is that the more knobs you have, the more things you have to set and the more likely you are to miss something.  So only go as complex as you are comfortable with.

If by chance your vendor script does the two bullet points above, by all means use it.

 

Going to be presenting at SQLConnection Suburban Chicago

Posted on by

On Tuesday February 12th at 6:30 PM I am going to be presenting on

Lessons From The Software Company

Why software companies design and implement databases the way they do even when it does not make sense to you. Also what you can learn from them about how and how not to design and implement databases that might be installed, managed and used by others.

I will cover why software companies (ISVs) do what they do with design and implementation on SQL Server. I also discuss what I found to work in terms of getting quality support from ISVs..

The presentation is at the BIS at University Of Illinois (the Naperville facility).   You can register here.

Man, Do I Love Me Some Line Documentation….

Posted on by

Was looking at a stored procedure produced by an outside entity yesterday and had a few minutes of pure joy…

There was header doc… there was line doc… comments within the code explaining why something had changed…. there was even an example call to the stored procedure… It was pure magic, it made everything I had to do about 10x easier.

Documenting your stored procedures, functions, etc. is not something that pays off when you write it, but especially in today’s agile environments it is in an investment worth making.  It is easy, too easy in fact to write, write once read never code adding some line doc and some header doc not only makes it easier for the next person who has to deal with the code, it makes it easier for you to identify problems during development.

 

Some Truth Is Better Than Others…

Posted on by

At some point you are likely to see something like this in a SQL Statement  1= 1, this generally shows up in dynamic SQL so there is always something after the where clause.

So you can end up with

select * from myobject where 1=1

Which would return everything.

So far no problem, I see the logic behind it. I used to reverse the dynamic string and see if the reversed string started with erehw (where backwards)  and then remove that from the string if that was the case.  A bit more awkward but it also produced a bit of a cleaner statement IMHO.

But today I saw this where ‘c’ <> ‘C’  I am not sure why they tossed down a statement that would appear to be false on it’s face

However it could be true or false depending on a case sensitivity setting.  For what it is worth, this vendor sometimes UPPERs stuff and sometimes does not.   So go figure.

The lesson here is if you need to put a specifically true or false condition in a statement, use integers that way you never have to worry about case sensitivity or any setting at all. Because I am fairly confident you can’t make 1=2..

Some love for sp_BLITZ

Posted on by

Brent Ozar has produced (a while back) a script designed for when you have to take over a server.  Information on it can be found here I would highly recommend that you also just run it on any server, not just one that you have taken over, but a server that you have been running for years.

It can provide some useful insights.  If you haven’t given it a try I recommend it.

Just Submitted My Last Answer To A Final Question For Mongodb For DBAs Class

Posted on by

Have to say I learned more than I expected from the class. We will see what grade I get on the final. I ended up with net of 100% on the homework, they dropped the lowest homework score so the one week I didn’t finish one question didn’t count.

Still looking at use cases, but I have some government data I may try using with it just to keep myself in practice.

 

 

Sometimes Old Is Worse Than New…

Posted on by

Working with an application that has been around for a while and wanted to look at some of the default report data about index usage and had the report fail. Keep in mind the server is running SQL 2005.

Figured out it was because the database is in 2000 compatibility mode, so I had someone ping the vendor to see if we could take it up to at least 2005 compatibility mode.

The answer was no and I know the application has been patched and updated since 2005, so this isn’t a turn it on in 2002 and forget it thing.

Really guys, you haven’t bothered to solve the compatibility issue? Well, I did using the upgrade wizard and some traces, it turns out there isn’t any to speak of. It appears they don’t want to make the effort to test or patch, pure and simple.

Yeah they have a new version coming out, but come on, it isn’t rocket surgery, spend a couple of days and make it at least run with 2005. It may not help you sell an single additional copy, but at least one customer will not think you are lazy.

 

 

 

The Value Of SSC

Posted on by

Was at training on Saturday for being an Assistant Scoutmaster and heard the following used as a way to discus how a Troop meeting went.

SSC (Stop, Start, Continue)

What we should Stop doing

What we should Start doing

What we should Continue doing

So they next time you interact with a customer, ask yourself those questions. The answers might be nothing, but they are worth asking.  Heck, if you get along with the customer ask the customer, I know they would appreciate the chance to give some feedback.