Re: Need Feedback on Trans. Replication w/ Remote Distributor

First, don't apologize for the post length. I strongly prefer a detailed
post with relevant information to a "my server don't work, help, TIA" post.

Second, Transactional replication will give you a read-only copy of the
data, but not of the complete database schema. All objects don't get
replicated and stuff like Identity columns, referential integrity, unique
constraints, views, and stored procedures just won't work the way you think
it will. The short answer is that without a LOT of extra work, you can't
use a subscriber in place of a publisher as a DR strategy.

If you don't use any file copy protocols other than FTP, how do you get
backups off of the host computer? There is such a thing as too much
security.

I would use Log Shipping to handle the creating and maintaining a warm
standby server. There are some scripts in the SQL 2000 Resource Kit as well
as various web sites that you can adapt for your own use.

I realize this isn't answering the question you asked, but it may be the
answer you really need.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP


"Keith Jakobs, MCP" <elohir@xxxxxxxxxxxxxxxxxx> wrote in message
news:OIcAYyM2FHA.4008@xxxxxxxxxxxxxxxxxxxxxxx
> Greetings:
>
> I have been asked to set up replication between two SQL servers on our
> network. Though I am primarily a network security engineer, and would
> consider myself just above a novice SQL Admin, replication is definitely
> new
> territory for me, and I was hoping I could get some help and feedback on
> what I think I am trying to accomplish...
>
> The scenario: We have a SQL server that currently serves as the backend
> database for a web-based application. It consists of several databases
> (on
> one instance) with no clustering nor data redudancy other than the RAID
> structures currently housing the data. A second server has been purchased
> with much more processing power, that they wish to use as a failover
> device,
> but do not wish to make it the PRODUCTION box until/unless the current box
> fails. They just want a copy of the data to be duplicated to another
> device
> as close to real-time as possible (and without clustering).
>
> So, because of the power of the new box compared to the old one, I had
> decided based on my initial research, to set up Transactional Replication
> between the two boxes, and defining the new server (Server B) as the
> Remote
> Distributor and Subscriber with the original production server (Server A)
> as
> only a Publisher. I also intended/hoped to use pull subscriptions. The
> intent of all these decisions being to absolutely minimize the additional
> overhead on the original production box. What I have not been able to
> find
> is any documentation on how to configure an alternate snapshot location
> when
> using a remote distributor that is the sole subscriber. Can this be done?
>
> The reason I need to use alternate snapshots (I believe) is because when I
> do implement this on the production servers, they exist in a DMZ zone that
> not only has no domain, but also does not have any NetBIOS nor windows
> mgmt
> protocols enabled (except Terminal Services for Remote Admin). There is
> NO
> Windows SMB file sharing, so I need snapshots to be distributed to the
> subscriber via FTP .... but as I said, the subscriber IS the distributor.
>
> Can this be done? Did I inadvertently make my first replication project
> too
> complex? Did I overlook something as to how it can be done? Basically I
> am
> stuck at the point where I have definied my first susbscription, did NOT
> create an initial snapshot yet, and am trying to figure out how to
> configure
> the 'Snapshot Location' properties on the Publisher so that it will deploy
> the snapshot to a location that the Distributor/Subscriber can access via
> FTP, BEFORE running the snapshot agent for the first time.
>
> And yes, this is all on a test environment using Virtual PC's at the
> moment.
>
> My apologies for the length of this message, but I know how much it helps
> to
> have as much detail up front as possible. And Thank you in advance for
> any
> feedback or recommendations.
>
> Keith C. Jakobs, MCP
>
>
> [FYI... Exec mgmt has INSISTED that all System files, program files, SQL
> data and transaction logs are configured on the SAME RAID-5 partition on 4
> physical disks with a 5th hot spare drive... I've tried to convince them
> to
> allocate dedicated log drives and pull swap files off the RAID-5, but they
> are not interested in deploying multiple physical disk structures on a
> single server]
>
>


.

Relevant Pages

  • Re: Replication
    ... Looking for a SQL Server replication book? ... old subscriber which was SQL 2000. ... Lunch a SQL Server Enterprise manager on OWSQL Server ... Right -Click Distribution Clean up Job: ...
    (microsoft.public.sqlserver.replication)
  • Re: Problems replicating over internet....Still....
    ... Subscriber in EM...and I can connect to the server via EM or SQL QA. ... >> to your Publisher to download the snapshot and transactions. ... >> Looking for a book on SQL Server replication? ...
    (microsoft.public.sqlserver.replication)
  • Re: Replication over FTP
    ... My publisher, when setting up the replication, ... The subscriber, a pull merge subscription, has all the ... > place over port 1433 except for the application of the snapshot which is ... >> Can I, when setting up the database, export all objects to the server. ...
    (microsoft.public.sqlserver.replication)
  • Re: Web Replication Error When Creating Subscriptions
    ... Did you create an alias to your subscriber using SQL Server Configuration ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Transactional Replication 2005
    ... subscriber b and then use subscriber B as a publisher to subscriber c. ... I am using 64 bit sql server 2005. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
Loading