Need Feedback on Trans. Replication w/ Remote Distributor

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]
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@.NOSPAM.hotmail.com> wrote in message
news:OIcAYyM2FHA.4008@.tk2msftngp13.phx.gbl...
> 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]
>
|||Hi Geoff,
Thank you so much... yes, this is probably exactly the kind of information
I was in need of. I knew I was missing something about all of this
replication business!!!
I will start reading into Log Shipping, hoping the BOL will give me a good
foundation. I have only vaguely heard the term before, so I will need to
get myself up to speed in that arena. I'll save my questions until I have
at least done my own preliminary studying. But I do need to ask if that can
be done over FTP protocols?
As for backups, we use Veritas NetBackup and have uniquely specified ports
for that traffic through our firewall between LAN & DMZ. Yes, Exec mgmt has
also decided that all backups go through our 100Mbit firewall... that's
why they want a standby DB in the DMZ. I would rather a dedicated mgmt
network on secondary NICs, but I dont get to make the final say.... go
figure. ;-)
However, nowadays, I'm beginning to wonder if there is such a thing as too
much security... I've been finding the tighter the better - you know...
single function and hard-coded applications are making more and more sense.
Plus they keep passing more and more laws that you may be better off erring
on the side of excess. Just my 2 cents on the security end.
Thanks again Geoff! Your feedback was very much appropriate and
appreciated!!!
Keith C. Jakobs, MCP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OcVcS$Q2FHA.400@.TK2MSFTNGP09.phx.gbl...
> 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[vbcol=seagreen]
> 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@.NOSPAM.hotmail.com> wrote in message
> news:OIcAYyM2FHA.4008@.tk2msftngp13.phx.gbl...
purchased[vbcol=seagreen]
box[vbcol=seagreen]
Replication[vbcol=seagreen]
A)[vbcol=seagreen]
additional[vbcol=seagreen]
done?[vbcol=seagreen]
I[vbcol=seagreen]
that[vbcol=seagreen]
distributor.[vbcol=seagreen]
I[vbcol=seagreen]
deploy[vbcol=seagreen]
via[vbcol=seagreen]
helps[vbcol=seagreen]
4[vbcol=seagreen]
they
>
|||Built-in log shipping works over SMB protocols. I suppose you could build
something over HTTP/FTP protocols for the file transfers, but I wouldn't
want to write it. I do recommend a second, closed SQL management/backup
network but as you said, we don't always get the final say on such
decisions. BOL describes the built-in stuff for Log Shipping in Enterprise
Edition. The SQL 2000 resource kit has some more information on roll your
own log shipping. You can always Google the term and get many results from
various web sites.
I agree that tighter security is a good thing, but arbitrarily saying that
all communications have to go through X is not security, it is
simplification for network managers.
If you are migrating to SQL 2005, there is a new type of replication called
Peer-to-Peer that may meet your needs. Here is teh latest version of
SQL2005 BOL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Keith Jakobs, MCP" <elohir@.NOSPAM.hotmail.com> wrote in message
news:uXZNXRY2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Geoff,
> Thank you so much... yes, this is probably exactly the kind of
> information
> I was in need of. I knew I was missing something about all of this
> replication business!!!
> I will start reading into Log Shipping, hoping the BOL will give me a good
> foundation. I have only vaguely heard the term before, so I will need to
> get myself up to speed in that arena. I'll save my questions until I have
> at least done my own preliminary studying. But I do need to ask if that
> can
> be done over FTP protocols?
> As for backups, we use Veritas NetBackup and have uniquely specified ports
> for that traffic through our firewall between LAN & DMZ. Yes, Exec mgmt
> has
> also decided that all backups go through our 100Mbit firewall...
> that's
> why they want a standby DB in the DMZ. I would rather a dedicated mgmt
> network on secondary NICs, but I dont get to make the final say.... go
> figure. ;-)
> However, nowadays, I'm beginning to wonder if there is such a thing as too
> much security... I've been finding the tighter the better - you know...
> single function and hard-coded applications are making more and more
> sense.
> Plus they keep passing more and more laws that you may be better off
> erring
> on the side of excess. Just my 2 cents on the security end.
> Thanks again Geoff! Your feedback was very much appropriate and
> appreciated!!!
> Keith C. Jakobs, MCP
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OcVcS$Q2FHA.400@.TK2MSFTNGP09.phx.gbl...
> post.
> think
> well
> purchased
> box
> Replication
> A)
> additional
> done?
> I
> that
> distributor.
> I
> deploy
> via
> helps
> 4
> they
>
|||Here is the link.
http://www.microsoft.com/downloads/d...displaylang=en
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Keith Jakobs, MCP" <elohir@.NOSPAM.hotmail.com> wrote in message
news:uXZNXRY2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Geoff,
> Thank you so much... yes, this is probably exactly the kind of
> information
> I was in need of. I knew I was missing something about all of this
> replication business!!!
> I will start reading into Log Shipping, hoping the BOL will give me a good
> foundation. I have only vaguely heard the term before, so I will need to
> get myself up to speed in that arena. I'll save my questions until I have
> at least done my own preliminary studying. But I do need to ask if that
> can
> be done over FTP protocols?
> As for backups, we use Veritas NetBackup and have uniquely specified ports
> for that traffic through our firewall between LAN & DMZ. Yes, Exec mgmt
> has
> also decided that all backups go through our 100Mbit firewall...
> that's
> why they want a standby DB in the DMZ. I would rather a dedicated mgmt
> network on secondary NICs, but I dont get to make the final say.... go
> figure. ;-)
> However, nowadays, I'm beginning to wonder if there is such a thing as too
> much security... I've been finding the tighter the better - you know...
> single function and hard-coded applications are making more and more
> sense.
> Plus they keep passing more and more laws that you may be better off
> erring
> on the side of excess. Just my 2 cents on the security end.
> Thanks again Geoff! Your feedback was very much appropriate and
> appreciated!!!
> Keith C. Jakobs, MCP
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OcVcS$Q2FHA.400@.TK2MSFTNGP09.phx.gbl...
> post.
> think
> well
> purchased
> box
> Replication
> A)
> additional
> done?
> I
> that
> distributor.
> I
> deploy
> via
> helps
> 4
> they
>