Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mfemenel
Professor Frink
1421 Posts |
 Posted - 2011-01-25 : 10:31:18
|
| Looking for opinions on how to solve a problem, there isn’t a right way/wrong way to skin this. We have a server with 8 databases, one of them is a central data storage database for all of our shared data, let’s call it customer information. The other 7 join to this database via synonym. Since these db’s are linked to the central db via synonym they all have to fail over as a group in the event of a disaster or if we try to put one of them on their own instance. For example if DB 1 goes down it fails over from Texas to Virginia, which will perform poorly via synonym and also require us to add a linked server to provide the connection back to Texas. The only way to keep performance would be to move the central database over which then means that the other db’s would have to come over with it.So, what are the options. We could do a linked server and keep the synonyms, my concern there is that poorly written code would cause performance problems and there is a lot of existing code that would need updating. Next option, replicate the data to the other databases rather than use a synonym. However this causes a storage issue, multiplying all of our development lanes as well as production we incur quite a large storage cost by doing this and really defeats the purpose of centralized storage for common data. Another option is using an ORM or distributed cache solution, I think this might be the technically “right” way to go but requires quite a bit of front end code changing to use this. Also requires applications to do a data “mash up” on the front end. Take for example a table in an app that has a “modified by” column and holds a users login id. Stored procedures using this join to the synonym for user data and return the first name/lastname of the user that modified the record. In an cache world this would require a user to call a proc to return the data from the app db and another call to get the user name information from the cache and then “mash up” that data into the front end to display it to the users. I can’t see that performing well but I like the idea of the cache. Finally I had considered CLR calling out to a service or cache and using it in a UDF or stored proc. This is minimal recoding in the procedures however there are a lot of overhead considerations calling out to a service as well as out of process threading etc etc. So that’s it, that’s the problem I’m trying to solve and kicking around various options. Please throw any ideas you have out there, everything is on the table at this point and maybe your idea will knock something loose for me. BTW, this is all in SQL 2005 today, moving to 2008 by the end of the year. Thanks!Mike"oh, that monkey is going to pay" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-01-25 : 10:42:00
|
| Need clarification on "fail over", are you mirroring between sites, or using some kind of synchronization already? (sounds like no, so "fail over" is not clear) |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2011-01-25 : 10:46:51
|
| Right now we're log shipping and all our db's are on the same instance. Today's practice is when we fail over for a DR test everyone fails over together. Because of the synonyms they have to go together if we were in a true dr. What we're trying to get to is if one of the db's fails over then just that db needs to go without anyone else caring. We'd like to move from log shipping to mirroring but until the synonym issue is addressed we can't do it. Also we're reaching the point where we'd like to distribute these db's among other servers/instances rather than stacked up on the same box. So even within the same datacenter we face the synonym issue as we'd be spanning servers.Mike"oh, that monkey is going to pay" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-01-25 : 10:56:34
|
| Let me put thinking cap on...although this is a perfect scenario to use the HADRON enhancements in the next SQL Server version:http://www.brentozar.com/archive/2010/11/sql-server-denali-database-mirroring-rocks/I've have a strange mirror scenario with 4 DBs on 3 servers that split roles across them. I query them all using linked servers to determine which one is the principal for each DB, then construct some OPENQUERY dynamic SQL to put the data I need into a temp table. This won't work as a synonym though, but might be an option in stored procedures. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2011-01-25 : 10:59:50
|
| Totally agree with your HADRON comment. Unfortunately where I work if it comes out next year we wouldn't see it until 2014 at the earliest by the time it makes it's way through the approval processes. I need to keep the coding simple as we have offshore resources who are considerably underwhelming in their ability to produce code that doesn't cause me to get paged after it hits production.Mike"oh, that monkey is going to pay" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-01-25 : 11:13:05
|
| Let's see if I understand this: you're currently log shipping and want to apply a "failover/availability group" concept to these databases? (but you currently don't have one) What's your current failover process like?Are you currently log shipping the shared Customer Info DB? What kind of circumstances (if any) would require a single DB to fail over to another server but not the others? |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2011-01-25 : 11:22:06
|
| Well you almost have it. Yes we're currently log shipping and would like to get to mirroring by the synonym issue is a problem for us. In addition to that issue we would like to start moving these db's off onto their own servers/instances even when they're in the same data center so that a server/instance issue doesn't take down all of the applications. All of the db's are log shipped including the customer info db.Mike"oh, that monkey is going to pay" |
|
|
|
|
|
|
|
|
Breaking apart DB's with synonyms