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

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@xxxxxxxxxxxxxxxxxx> wrote in message
news:uXZNXRY2FHA.744@xxxxxxxxxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxxxxx> wrote in message
> news:OcVcS$Q2FHA.400@xxxxxxxxxxxxxxxxxxxxxxx
>> 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: newbie help: failover server
    ... I will look into Log Shipping & the SQL Resource ... As far as business decision vs technology decision, ... > Server 2000 Resource Kit includes a simple log shipping example that you ...
    (microsoft.public.sqlserver.clustering)
  • Re: active/passive cluster vs log shipping
    ... network and update a dns record to point the database server to the standby ... > Columnist, SQL Server Professional ... > active/passive cluster & using log shipping as a failover strategy. ... > To go with clustering I would have to go with enterprise edition of both ...
    (microsoft.public.sqlserver.clustering)
  • Re: Cannot connect to SQL Express on 2003 Machine from XP machine
    ... And i double checked the connection string, and i did have it the right ... protocols and listening ports. ... But I have another 2000 server that I can connect to fine, ... I am trying to connect from an XP SP2 machine remotely to a SQL Express ...
    (microsoft.public.sqlserver.connect)
  • Re: newbie help: failover server
    ... but the inputs of cost and availability don't change. ... I support the Professional Association for SQL Server ... I will look into Log Shipping & the SQL Resource ...
    (microsoft.public.sqlserver.clustering)
  • Re: newbie help: failover server
    ... best availability technologies are replication and log shipping. ... Log shipping is included with Enterprise ... Server 2000 Resource Kit includes a simple log shipping example that you can ... SQL Clustering is one option and log shipping is another. ...
    (microsoft.public.sqlserver.clustering)