Testing SQL Replication

Hello Experts
I have a client that has 2 2008 R2 servers both running SQL 2008 R2 and are setup to do SQL transactional replication.  I am fairly limited in my SQL knowledge and would like someone to help me determine if the replication is taking place and also to figure out what I would need to do in the case that the main server was to go down to get them going again.
I know this is a rather large task but I need to get this documented in case they go down and I would rather do in advance so we can have a recovery plan.
One other thing the subscriber is running in a Hyper-v enviroment and the Publisher is on a physical machine but they are two seperate physical serves.
I am sure you need more info than i have provide so just ask and I will get it ASAP.
Thank You
Don
ccofficeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ccofficeAuthor Commented:
Thanks for the reply Jagdish
Sorry but my SQL skills are not very good.
I tried copying and pasting the query in the SQL query window in the 2nd and 3rd link but it just gave me a debugging message on the 2nd link for several minutes and an error on the 3rd link.
Can you please explain what I need to do prior to running these queries to insure they work.
Thanks Again
Don
0
TAB8Commented:
Open sql management studio and connect to your sql server ..

expand the replacation tab ..

You will see publisher and subscriber folders as shown in attachment

open publisher ..  if there is anything in there this is your publications
open subscriptions .. if there is anything in there this is your subscriptions

if both are empty then you server is not participating in replication
repl.JPG
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ccofficeAuthor Commented:
Thanks for the reply tab8 and yes I can see the publication and subcription so I believe it is working but I would like to test it to see what needs to be done in case the main server goes down. Would we simply rename the subscriber server and change its IP to match the publisher server?
Don
0
TAB8Commented:
You have 3 main ways to do this:

1 Redirect via Connection String      
This requires changing any application connection string that points to the old primary server to point to the new secondary server.
This may be done manually of via a login script that changes the points upon user login.When only a small number of the total number of applications are to be failed over.
When changing all the connections strings is fast or simple to do (i.e. Such as for applications that all use the one string in either an IIS or Citrix server.)
 
2 Redirect via Server Rename      
This requires a change to the new secondary DR server to change the server name and IP address to that of the old primary server. The new primary server must then be rebooted.
This will in effect make the secondary server act in a primary server role. The old primary server can no longer be “on the network” else IP clashes will be raised. When all applications on the primary server need to be redirected, such as when the entire old primary server is off line.
This solution may not be possible when multiple primary servers have a single consolidated DR server.

3 DNS Sername change - alter your DNS to point trafi from your prod to your dr server .. not the database names need to be the same..

Also NOTE
with replication it doesnt automaticly copy new user accounts that you create on your publisher to your suscriber .. you will have to create some scripts to do this

If this solution is just for DR then i would suggest you take a look at log shipping as a better solution
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ccofficeAuthor Commented:
Thanks Tab8 that is what I was loking for.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.