Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Our current production database environment contains about 10 similarily managed databases. Our agency has just purchased and is installing new blade chasses and wants to move my database to a new instance (leaving the other 9 on another). This decision is being driven by one of our IT staff, not a DBA. I am a project manager, not a DBA but I know enough to not necesarrily have a good feeling about this decision and I am urging our IT department to make a sound decision based on what is best for the database. Our IT department has stated that it is not good to have all our eggs in one basket, and has also stated that my database contains "regulatory data" so it should be on its own instance.

A couple of truths: - None of the databases on the current instance are OLTP databases nor are any of them data warehouses - My database currently has joins/views to a couple of the other databases in the production environment

So my questions are as follows:

  1. Am I wrong to disregard a statement about eggs in baskets? (hello, this is why we have maintenance plans/disaster recovery plans). I'll mention that other databases also have regulatory data too.

  2. What types of questions do I need to ask to determine if this is a sound decicion? (A DBA friend mentioned that if the service level agreement of said database does not radically differ from the others then why do they want to do this?)

  3. I have done some research on linked servers. What arguments should I bring forth about the fact that I have views setup that rely on data from other DBs currently?

share|improve this question

migrated from stackoverflow.com May 30 '12 at 12:33

This question came from our site for professional and enthusiast programmers.

    
Voted to close as off-topic since it's not a practically solvable programming problem. –  Anthony Grist May 30 '12 at 12:26
    
Sounds like you need a data warehouse to report from instead of reporting from the production database. There isn't a good reason to have "your" database on the production server - nor can this question be answered without understanding what "your" database is. Does it keep "your" golf scores or does it actually contain something needed by the production environment? –  Dan Andrews May 30 '12 at 12:29
    
This question is really about data management and not about programming. As such, it doesn't really belong in Stackoverflow. The Nearest thing in StackExchange is the forum for Database Administrators. It isn't really a DBA question either, but that's the closest you're going to get to data management in this Q&A environment. I think it's a shame that fundamental and important questions like the one you raised get bounced around between one tecchie forum and another. There ought to be a forum where questions of longer term import and consequence can be raised and answered. –  Walter Mitty May 30 '12 at 13:11
add comment

2 Answers

up vote 1 down vote accepted

From what I've read, your environment doesn't have any "official" DBA. It's hard to make a lot of concrete statements at this distance, but here are some things that I'd be mulling over.

Firstly, it's entirely possible that the IT staffer is correct and that the data needs to be on another server for some regulatory reason. Sometimes, the rules are the rules. They might have been negotiated by persons in the government (or legal system) without regard to how they might be technologically implmented. Even if the rules might not make 100% "logical" sense, you aren't going to change them.

Also, as a sort of over-arching thing, I would suggest that as long as the performance isn't harmed and the uptime, disaster recovery and other SLA-covered items aren't negatively impacted, it's really the IT staffer's neck on the chopping block. You aren't responsible for regulatory compliance (or you would be trained to be more familiar with the rules, I'm guessing) and the IT staff are responsible for compliance. If something goes wrong with the app or they violate some regulatory rule, they will have to live with the consequences. You get to say "The IT guys told me it would be fine." (If the system does have any sort of problem on the new server, I would certainly find out whether or not the problem would have occured if the database was on the old server.)

As a long-time DBA who has worked on a lot of consolidation projects, you generally want fewer instances, not more instances. (I'm not talking about dev/test/qa/production instances; we are only talking production here.) It's easier to manage a smaller number of instances and it keeps the licensing costs down. Sometimes, some people fall in love with the idea of high server counts, on the idea that more is better. "100 servers with 1 database each" is somehow better than "100 databases on 1 server".

Are they going to be moving those other databases with regulatory data to blades or has yours been singled out? If more databases will be moving, why is yours going first?

What kind of SLA do you have for your database? Are the blades capable of handling it? How do they know that? If the old server is clustered, are the new blades clustered? Will the uptime be the same? Or better? Is the storage going to be the same?

Are there external reports or apps that "know" that all of those databases are co-located on the same instance? How about MS Access databases or Excel worksheets that do data extracts? Yes, this sort of thing should be hidden behind a view or stored proc but I've seen many cases were it was not. You don't want to find out that you need to rework app code after you have moved the database. Any such work needs to be figured into the effort to move the database.

On a more negative tack, it is possible that the "IT staff" don't know enough about SQL Server to feel comfortable using SQL Server's features (logins, roles, permissions, encryption, named instances, whatever) to properly segregate that "regulatory data" on just one instance. You can look at this as "the IT staff need more training/someone needs to buy a DBA" or you can look at it as "the IT staff need to be comfortable using what they already know to follow regulations". Both are sane viewpoints, with pros and cons.

If there are performance problems on the current server, IT staff might expect to improve the situation by moving a database onto a different server. That might be true for at least some functionality of a particular database, but inter-database dependancies can be very problematic. Performance of linked server queries will not match performance of local queries, due to network latency, a degraded ability to properly optimize queries and the way that data is sometimes fetched. Sometimes performance is OK, sometimes it isn't. The best way to tell is to test your code before you go live on the new server. Once you are on the new server, it will be harder to do anything about poorly performing queries and this isn't the sort of thing you want to be surprised by. I've seen people run into this in the past. The response is generally to copy the data from the remote server to the local server, usually by SSIS or by use of a linked server. This is extra work and problems with the "staleness" of the data can crop up.

Futher, linked servers can be security holes if they are carelessly configured. If your IT team is primarily worried about access to regulatory data, this should concern them as well.

If a database has dependencies on the other databases, they are all part of the same system. Moving your database to another server will make it more likely that the system will fail because there are more things to fail.

Eggs and baskets are not a great metaphor, even though it is often used. Eggs do not depend on one another. Databases can depend on one another. Interrelated databases should be seen as a system. Moving one database to another server will make failure of the system of databases more likely because there are more things to fail.

The question then becomes, "If you take one of those databases offline at random, how is the entire system affected?" IOW, what happens if the old server crashes? What happens if the new blade crashes? If the whole company stops working if those other nine databases are offline, does it matter if your database is up or not?

share|improve this answer
    
I really appreciate your reply Darin! You've certainly hit on some big points (One of which you gleemed in the fact that our DBA has took another job). Unless someone wants answers to these questions that Darin asks I'll go do my mulling on my whiteboard. Thanks again! –  O'MALLEY May 30 '12 at 17:32
add comment

My database currently has joins/views to a couple of the other databases in the production environment

Ok, so how is this going to work? Linked servers? That will put a LOT of traffic on the network. THe query behavior (view behavior) may change drastically.

share|improve this answer
    
It would have to be linked servers. –  O'MALLEY May 30 '12 at 17:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.