Got it!

Thank you for all the guidance. I guess I have my alternativities clear.
Thank you very much.
Now I have another issues to address and if someone has experience in this I
would really appreciate the advise. Right now we are implementing the
second site, eventually we'll have 5.
1. I need to implement Failover. If my Server at Site 1, which right now is
my publisher and distributor I need to be able to keep working. I don't
think that changing the roles of the servers is going to be a smooth way to
address this. I think I need a hotspare of my server at Site1. Does going
with merge or transactional can have any impact for my Failover setup?
2. In order to be ready for deployments in the others sites I was planning
to a ghost of my Server 1 site, but now that I'm implementing replication,
this Server 1 will have a specific configuration, so I was thinking in make
a ghost of my Site 2 (subscriber server). Is this a good idea or I should
just ghost my database without any replication configuration and then do
this through an script.
Thanks in advance for any input
Marlene A. Roman
1. There will be a dramatic difference. You have to remember as well that
all of the names are embedded in. That means you can't simply restore
everything from the publisher to a different server and have everything
work. Transactional also doesn't have any really good solutions for this at
the moment. You would in essence have to rename your standby server to
match the original publisher and then restore everything. This is also
assuming you have followed the backup process documented in the BOL article
called "Synch With Backup". Then you really cross your fingers and pray
that it works. You honestly have a 50/50 chance of that happening. If you
are using merge, you still have to rename the server to match the original
publisher, but then you simply restore the published database along with
msdb, kick off the agents, and you are back in business. Alternatively with
merge, you can configure the standby as an alternate publisher for the other
subscribers such that if the publisher goes down, they automatically switch
over and continue to propagate transactions which then get moved back to the
publisher when it comes back online. Details for this are also in BOL.
2. If you are going to ghost a server, do NOT have replication configured.
Ghost the server and then use a script to setup replication. Ghosting with
replication in place is a quick trip to having to take everything back out
and rerun the scripts anyway.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Thanks Michael, this is a lot of valuable information:
I think the alternate publisher is the solution that I need, I just have a
question related to this setup:
Can the alternate publisher be at the same time a subscriber of the primay
publisher?
Thanks very much for your help
Marlene A. Roman
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:eE5LWCfLEHA.808@.tk2msftngp13.phx.gbl...
> 1. There will be a dramatic difference. You have to remember as well that
> all of the names are embedded in. That means you can't simply restore
> everything from the publisher to a different server and have everything
> work. Transactional also doesn't have any really good solutions for this
at
> the moment. You would in essence have to rename your standby server to
> match the original publisher and then restore everything. This is also
> assuming you have followed the backup process documented in the BOL
article
> called "Synch With Backup". Then you really cross your fingers and pray
> that it works. You honestly have a 50/50 chance of that happening. If
you
> are using merge, you still have to rename the server to match the original
> publisher, but then you simply restore the published database along with
> msdb, kick off the agents, and you are back in business. Alternatively
with
> merge, you can configure the standby as an alternate publisher for the
other
> subscribers such that if the publisher goes down, they automatically
switch
> over and continue to propagate transactions which then get moved back to
the
> publisher when it comes back online. Details for this are also in BOL.
> 2. If you are going to ghost a server, do NOT have replication
configured.
> Ghost the server and then use a script to setup replication. Ghosting
with
> replication in place is a quick trip to having to take everything back out
> and rerun the scripts anyway.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
|||Yes it can.
BUT, I wouldn't simply use merge because of this. You need to evaluate what
each replication option means to your environment. You may find that the
alternate publisher feature in merge works great in the case you are down,
but that the overhead in having merge in place as well as managing it makes
your system very difficult to work with during the other 99.99+% of the
time.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Thank you for your answer Mike.
OK. Now I don't know what to do! :P
What about if I implement a Transactional Publication with updating
subscribers and have a HOTSPARE server ready in case my primary Publisher
goes down.
But then this server needs to be a subscriber of the primary Publisher and
at the same time have all the subscriptions setup but disable. Is that
posible?
Seems to me that I'm trying to use replication for replication-failover
purposes, when this is really not the purpose of replication. I was
thinking in suggesting to management that we should address the failover
issue using the clustering features of SQL SERVER. Does this make sense?
Thank you in advance for any input
Marlene A. Roman
|||Yes and no. Replication can be used very effectively for high availability.
I've done so at many companies. Because you have multiple sites that you
want to be able to access data, it would be your primary choice.
What I'm saying is that each replication method has pros AND cons. None of
them are perfect. You can get all kinds of answers out here. In the end,
we don't need to live with the system and manage it on a day to day basis,
but you do. Before settling on one method or another, you need to set up a
test environment and test each solution. That isn't just putting it in and
seeing if the data will move. I mean really test it and that means devising
tests that you think will break it and then running those tests to see how
it handles all of the situations where things aren't running smoothly. This
also tells you what kind of effort will be required for each one.
In the SQL Server 6.5 world, someone would have a problem/question and there
would be a very cut and dried answer. SQL Server has grown up
significantly. There are a lot of questions which don't have nice pretty
answers anymore and you have to understand the trade offs that you are
making for your system.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Michael thank you for all your help, I don't think I'm going to have time
to setup a test environment for both approaches. This week I'm going to set
up the system for training with real load of data using merge replication
and see how it behaves.
Again, thank you very much for all the advises.
Marlene A. Roman