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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-01-04 : 08:30:19
|
| Kevin writes "My department is working partnering with another department to offer data to users throughout our County, Arizona and the U.S.The other department will be maintaining the SQL Server (either 2000 or 2005) and we will be maintaining the web server that will serve up the pages to access the data.I have been tasked to come up with a solution that will:1. allow developers in my department access to the data real-time so that they can continue to work on the project2. provide for fault tolerance so that if the SQL server goes down, personnel in our department will be able to access and input data so they can continue to workEach department has its own domain, so the solution would need to be able to cross domains.Each department is physically located in a different area, so a clustering solution using a common SAN or NAS unit is not possible.I have suggested log-shipping, but because the failover is not automatic this proposal was not well received.Aside from log shipping, the only thing I can think of would be scheduled backups (complete once a day and then transaction logs every 15 mins. to 1 hour depending on performance) to a common domain that our developers could restore to a different server. Failover for this would not be automatic though, so I don't think it would be an option.Can you help me with any solution or suggestions?Thanks,Kevin" |
|
|
Norwich
Posting Yak Master
158 Posts |
Posted - 2006-01-04 : 11:32:55
|
| Hi KevWell in SQL2005 you have 2 facilities1. Snapshots (Not to be confused with Replication Snapshots)2. Database MirroringSnapshots - I recommend that you don't use this as part of your Backup Strategy but just a way of increasing Data AvailabilityA database snapshot provides a read-only, static view of a source database as it existed at snapshot creation, minus any uncommitted transactions. Snapshot exist in the Memory of the Server Instance of the Databse.Snapshot limitations: 1.They are READ-ONLY. 2. If the datase becomes unavailable then they also become unavailable - They are datasource dependent.[url]http://msdn2.microsoft.com/en-us/library/ms187054.aspx[/url]Database MirroringDatabase Mirroring is similar to other forms of mirroring. You can use 2 or 3 servers for a session. If you use 2 servers then machine A will be your Primary server (normal server) and the Machine B will act as your Reserve. You can either use Synchronous or ASynchronous transctions - Commit the transactions on both machines or just on the primary and update the reserve periodically. There is no automatic failover if 2 servers are used.If you use 3 servers then you add a third machine to the above and it acts as a Witness machine. It basically monitors the actions of the Primary and Reserve machines. The Witness machine will also activate Automatic Failover (in less than 3 seconds) should the Primary machine become unavailable.You cannot mirror sys databases.Please note that Mirroring is currently not supported by Microsoft and is disabled by default. Follow the like below to get more info.The simple and bulk-logged recovery models do not support database mirroring. Database mirroring is supported in SQL Server Standard Edition and Enterprise Edition[url]http://msdn2.microsoft.com/ms189852.aspx[/url]I hope this helpsRegardsNIf you want your computer to be faster then throw it out of the window. |
 |
|
|
happydad
Starting Member
1 Post |
Posted - 2006-01-05 : 16:37:16
|
| Thanks for the input.I just found out that SQL 2000 Standard will be used. Do you know of a way to accomplish our goal with that? I am searching the Web now, but have found nothing new.Goal: Real-time or near real-time read-only access to the database (on a dev server though) for development purposes Fail-over capability across domains and/or locations. It is possible that if the need arises our dev server could become the fail-over server, but in a best case scenario we would have the dev server and at least one other server for fail-over.I appreciate the help,Kevin |
|
|
|
Norwich
Posting Yak Master
158 Posts |
Posted - 2006-01-06 : 01:07:51
|
| Hi KevAs far as I know if you want Automatic Failover in 2000 then you have to go the Clustering route which is rather very expensive. Clustering is supposrted by Win 2000 Advanced Server (2 nodes) and Datacenter Server (4 nodes) through Windows Clustering.The Failover is monitored by th Cluster Service. In the event of a failure then the CS will automatically disperse the work to other nodes.Visit this link[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_7t9v.asp[/url]I would suggest the Log Shipping Route.Increase the number of shipments to your standby and set up system alerts on the primary.Visit this link for Log Shipping[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_8elj.asp[/url]RegardsNIf you want your computer to be faster then throw it out of the window. |
|
|
|
|
|
|
|
|
SQL Fault Tolerance