Asger Blond
ab-mi at post3.tele.dk
Fri Mar 14 16:49:51 CDT 2008
John, One thing I forgot to mention is that replication is always "article based", meaning that you only get synchronization of objects you have included in your publication - new objects (tables, views etc.) that you create in the database won't be propagated to the replica. - For that reason replication may not be a choice in your case. Database Mirroring on the other hand is "database based", meaning that the whole database is duplicated, and that new objects are propagated to the mirror as well. But in a mirror-scenario you have a "principal database" which is operational, and a "mirror database" which is in a recovering (non-operational) state. You can easily switch the role of the two databases but you can't have both databases operational (run jobs on both) as the same time. An Active-Active Clustering solution would certainly do, but probably would explode your budget too... If you want new objects propagated in an active-active scenario I don't know of an affordable solution. Maybe other listers do? Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby Sendt: 14. marts 2008 03:06 Til: 'Discussion concerning MS SQL Server' Emne: Re: [dba-SQLServer] Mirrored servers Asger, These are not transactional databases, so I am not too worried about "up to the minute" (or second) synchronization. These databases are huge name lists that I maintain for a company that sells name / address lists to bulk mailers. I routinely use one of the servers, and I do what has turned into a standardized count of people WHERE (a bunch of criteria here). The client asks for counts of how many people fit a criteria list. He calls these things "counts" because that is the terminology in his business, I call them jobs. I have created a database with about 20 odd views. A couple of views pull a specific set of fields from two or three other databases. One view is a criteria view. All of these 3 or 4 views are inner joined to pull a set of names. I then have about 20 "count" views that give me Order by / count information on a fixed set of data items in 20 specific fields. It is these counts that he really cares about. So... I have a "template" database that contains all of these views already designed and working. I just literally (from inside of SQL Server) COPY that template database to a specific name. I then set that one "criteria" view to pull a set of PKIDs, and then I run the ~20 count views, and manually (for now) copy and paste each count "table" (dataset) into a page of a spreadsheet. When I am done I have a spreadsheet with a page for each count dataset. I then email him the count spreadsheet with the 20 odd pages of counts. I go through all this rigmarole because he may come back next week and say "give me 100,000 actual names and addresses based on the count order I gave you last week". I can then go into this count database and run another view that hands me 100,000 of those names. Obviously the counts are his marketing to his client, the order may or may not ever come but if it does I had better be able to pull as many names as the counts say he has, and do so quickly. So as you can see, the business is a copy database, modify a view, run a set of count queries, copy to a spreadsheet and email it. Now I have a database that I have to archive because I may someday get an "order" (as opposed to a count). My objective is twofold. First I want a backup of all these databases off on another machine in case the main server I use goes down. Second if I am busy running a job on one server, I want to be able to run a different job on the other server. But at the end of the day I want both servers to have any work done on either server. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Thursday, March 13, 2008 7:30 PM To: 'Discussion concerning MS SQL Server' Subject: Re: [dba-SQLServer] Mirrored servers Database mirroring is not an option, since you want both databases to be operational. For the scenario given I would definitely choose replication, and more specifically: Peer-To-Peer Transactional Replication, which will give you fully operational databases on both sites and near real-time synchronization. Using this type of replication you have to make sure that conflicts can't arise, since Peer-To-Peer Transaction Replication doesn't have conflict resolution, as Merge Replication have. You could choose Merge Replication, but then you would have latency of synchronization. Asger _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com