July 24, 2011 at 2:06 am
I'll try to find out. I don't know alot about what happened because the "acquiring company" tried to upgrade to 2008 R2 before our departments were integrated.
Put it to you this way:
The version of the "system of record" COTS product that the merged company has chosen to use (the latest) runs only on SQL Server 2008 R2. It's a very good product and pretty much pays Medicare claims with a 60-80% adjudication rate (getting rid of a dept of 100s of people) by running each claim through hundreds of rules and automating payment to providers. Lovely. Again, I have used this COTS for many years but for the "acquiring company" it is all new to them. I recommended to them a nightly backup/restore (using RedGate or some compression backup type) to get the online system image to the warehouse every night, as that's how we (the acquired company) have done it for several years with great results and about the same amount of volume. Sure it takes about 3 hours to backup the db, compress it, move it, and then restore it, but then you have it locally on your warehouse server so everything blazes and it's a simple, foolproof mechanism.
But then we realized $hit, the "acquiring companies' " warehouse servers are still on SQL 2005 because of the failed upgrade because of that CTE performance problem. You can't restore a SQL 2008 R2 db onto a SQL 9 server duh (actually took me a couple days to realize this, sometimes I wonder if I'm just losing it lol).
So I said, hey, we've got like until 2012 before the first deliverables are needed so surely that's enough time to get your warehouse boxes to SQL 2008 R2, right? I mean we can't have them stuck on 2005 forever right? Apparently, the "acquiring company" is so scared, or the "upgrade" that was attempted when I was not there went so poorly that they are yanking that option off the top of the pile and are now looking into my second suggestion: SQL replication (as apparently we can go backward from SQL 2008 R2 to SQL 2005, publish just the articles we need, and even change up the indices on the warehouse side to better suit crunching these tens of millions of rows).
So "they" (and I hate to still use that term but for lack of a better term) are doing a POC of SQL replication) and if it is not provable by end of July guess what? We have to write dozens of SSIS packages to move the data from the online server to the warehouse server. I'm like, cmon how far down the rabbit hole do y'all wanna go? Eventually "they" have to upgrade these servers and then all this SSIS code would be throwaway.
In other words they are TERRIFIED of upgrading, bottom line.
I wish I knew more of what happened. There's a guy at work who's sort of a neutral third party who I'll try to get the skinny from but I know that for sure the issue with upgrading to 2008 R2 was with the CTEs and had to do with performance and fixing it with join hints.
My interest is very piqued as well.... Give me a few days to see what happened during this past "upgrade."
August 27, 2011 at 5:35 am
I guess schemas can be useful to structure data, but SMSS makes it hard to use them for that .
Generally I don't want to build big databases, I want to build lots of small ones - put data which is essentially read-only in separate databases because it should have a different backup scheme from mutating data, which you can't do with schemas. Also, if thet essentially read-only data consists of several chunks with different lifetimes (one chunk gets replaced every 6 weeks; another every 8 weeks; another every 3 months) I want to split it into sepatrate DBs so that the DB is the unit of replacement (with sensible use of db renaming, replacement doesn't require any downtime; and smaller DBs mean less chance of someone wanting to access it during the small fraction of a second it takes to switch). And I don't want all my eggs in one backup basket even for the mutating data - because restores take too long (yes, I know I can buy tools to get around it; but I'd rather just buy an extra sever with a couple of Terabytes of storage to do a full restore onto, and then pull the bits I need across from that - probably cost me less than those tools by the time I account for the cost of people learning to use them).
I do however want referential integrity constraints - so that will sometimes tend to push things together into one big DB; asking for cross-db referential integrity constraints would probably be laughed at, and anyway I think that if I have such a constraint I probably want both sides of it in the same backup. So providing an extra layer of structure could be one place where schemas would be really useful if only SSMS handled them sanely (but it doesn't: changing filters all the time is a pain, and when one wants to see two schemas at once they are not nicely separated). They are also useful for handling protection, I'd rather assign permissions to access a schema than to access individual objects in it - but the highlighted sections marked Important and Caution on [the BoL page on GRANT Schema Permissions suggest that using schemas that way is fraught with pitfalls.
I think that considering all the above schemas are good, but they could easily have been better. Their treatment in SSMS suggests half-baked approach, and the pitfalls in the permissions area could perhaps have been avoided, or at least mitigated (the caveats referred to above show that someone in MS was thinking about the pitfalls - it's a pity they didn't think of a way to eliminate them, instead of just documenting them).
Tom
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply