September 14, 2011 at 4:54 am
Grant Fritchey (9/14/2011)
Although, the current database was frequently very small and very responsive, the situations where you had to create cross-database joins were a pain.
Why?
September 14, 2011 at 4:58 am
Ninja's_RGR'us (9/14/2011)
Grant Fritchey (9/14/2011)
Although, the current database was frequently very small and very responsive, the situations where you had to create cross-database joins were a pain.Why?
The syntax is simple, right? No big deal. But the key was that we had to have the same queries written multiple times. One way against just one database and then against the other database and then against all three. I'm tired just typing it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 14, 2011 at 5:08 am
Grant Fritchey (9/14/2011)
Ninja's_RGR'us (9/14/2011)
Grant Fritchey (9/14/2011)
Although, the current database was frequently very small and very responsive, the situations where you had to create cross-database joins were a pain.Why?
The syntax is simple, right? No big deal. But the key was that we had to have the same queries written multiple times. One way against just one database and then against the other database and then against all three. I'm tired just typing it.
I have the same "issue" here. I just had to recondition my brain that this what had to be done to do the correct job and remind me that I'm paid by the minute (hours are long, minutes so darn shorter).
My brain blockage went away with that simple switch.
September 14, 2011 at 8:31 am
SQL Kiwi (9/13/2011)
...I don't disagree at all; though that so many professional DBAs don't 'get' partitioning (a new feature in 2005!) is a bit depressing. Luckily, it seems 'commodity' DBAs will be free to acquire alternate employment when the environments they currently 'manage' migrate to the cloud
The stats I saw in late 2008 showed Enterprise selling about 25% of Standard. So most DBAs never get the chance to use EE or partitioning. It also showed that something like 80+% of databases (numerically) were < 2GB (or 5GB), so again, not a lot of value.
However the recovery time changes dramatically between 5GB and 45GB. It can be the difference between a bad delete being an inconvenience and an all day down.
Don't remotely count on commodity DBAs going away with the cloud. I heard the same thing with hosted services and you still needed plenty of people to deal with systems staff, and with the application. We might need a few less DBAs, but I wouldn't count on it unless you outsource large slices of your infrastructure. And then we'll have commodity DBAs working for cloud vendors (yikes!)
September 14, 2011 at 8:55 am
Steve Jones - SSC Editor (9/14/2011)
The stats I saw in late 2008 showed Enterprise selling about 25% of Standard. So most DBAs never get the chance to use EE or partitioning. It also showed that something like 80+% of databases (numerically) were < 2GB (or 5GB), so again, not a lot of value.
The cheapest option will always be highest volume - no surprises there. Every DBA should have a copy of Developer Edition, so there's really no excuse not to learn and grow. Local partitioned views are available in every edition, and offer a complementary solution to 'proper' partitioning. Again, no excuses for the professional DBA not to know these things.
However the recovery time changes dramatically between 5GB and 45GB. It can be the difference between a bad delete being an inconvenience and an all day down.
No doubt. As I said, I have seen both solutions implemented effectively - so much depends on the circumstances and priorities. There are all sorts of recovery strategies, as you know.
Don't remotely count on commodity DBAs going away with the cloud. I heard the same thing with hosted services and you still needed plenty of people to deal with systems staff, and with the application. We might need a few less DBAs, but I wouldn't count on it unless you outsource large slices of your infrastructure. And then we'll have commodity DBAs working for cloud vendors (yikes!)
Perhaps. So much of this is opinion - we will see how it pans out. My own, slightly cynical, view is that a DBA cull could be a good thing.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2011 at 5:44 pm
Steve Jones - SSC Editor (9/14/2011)
However the recovery time changes dramatically between 5GB and 45GB. It can be the difference between a bad delete being an inconvenience and an all day down.
That's exactly what I believe the true purpose of a historical database actually is. If the "system" is designed correctly, the "current" database can operate quite well for several days if the historical database is down for some reason. If the "current" database needs to be restored for some reason, it's a whole lot easier and quicker to do the restore without the added and, sometimes, very large overhead of restoring the historical data at the same time because they're all in one.
YES! It's a bit more difficult to maintain security for twice as many databases but that won't be a problem for a good DBA that has control and automation of his/her systems. So far as having to maintain more than one copy of the same code goes, it's just not necessary and not desired to have a single database if you know how to use such things as synonyms and partitions even in SQL Server Standard Edition. You can even use "pass through" VIEWs that combine the historical information and the current information. If something goes wrong with the historical database, a good DBA would have scripts (perhaps even stored procedures in a UtilDBA database) at the ready to quickly and automatically regen the VIEWs to point only at the current database until the historical database could be brought back on line.
Remember, it's not how fast you can make backups... it's how fast you can use them for restores that really counts and so very many people forget about that until it's too late.
Anyway, that's my 2 cents on the subject and, yes, it's what I've done at several companies with great success.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply