[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