ho to do sql replication having sql on diffrent ip addresse

can any body help is sql replication is possibel when we have sql
installed on
servers who have diffrent ip addresses.
here i have three sql servers two servers are are installed at
localley and one is at remote side. my remote side server is having
diffrent ip address when i try to register my remote side server to it
does not registers it gives
connection failed. can any body help me
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict249894.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=865268
If the server is on a non-trusted domain, try creating a client-alias to the
server (client network utility) and then register this servername.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Hilary's new replication book

To Hilary: I've been eagerly awaiting your new book on SQL
Replication that had been scheduled for April publication.
Any idea when it will become available? Thanks!
http://www.nwsu.com/forthcoming.html
http://www.nwsu.com/0974973602.html
I'm not sure if I will be hitting the June ship date, but it will be soon
after that. I'll let you know. I had a disagreement with Apress and decided
not to go with them. I don't know why they claim they are still publishing
it or why they claim an April print date.
"fundster" <anonymous@.discussions.microsoft.com> wrote in message
news:8e3c01c432c8$e6fbdbd0$a301280a@.phx.gbl...
> To Hilary: I've been eagerly awaiting your new book on SQL
> Replication that had been scheduled for April publication.
> Any idea when it will become available? Thanks!
|||And to increase the goodwill in the world, Hilary will be
providing, free of charge, all posters in this newsgroup
with signed copies.....:-)
|||Yes, I am very excited to be able to freely distribute these volumes, and I
am even more excited that Paul has agreed to pick up the tab
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:8f6801c43345$a077eaf0$a401280a@.phx.gbl...
> And to increase the goodwill in the world, Hilary will be
> providing, free of charge, all posters in this newsgroup
> with signed copies.....:-)
|||I will gladly pay for mine (signed of course). You have been a great source
of knowledge, help for me, Hillary. I will like to take the sopportunity to
say thank you and congratulate you.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OpsLcX1MEHA.268@.TK2MSFTNGP11.phx.gbl...
> Yes, I am very excited to be able to freely distribute these volumes, and
I
> am even more excited that Paul has agreed to pick up the tab
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:8f6801c43345$a077eaf0$a401280a@.phx.gbl...
>

Hilary's Merger Replication Book ...

In Hilary's replication book there is mention of a second volume on merge
replication. Is this available yet? Is there a target date for publication?
Thanks,
Bob Castleman
DBA Poseur
I am mid way through it. I am not sure when it will be in print. Probably
not July.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bob Castleman" <nomail@.here> wrote in message
news:OQZlb6zeFHA.228@.TK2MSFTNGP12.phx.gbl...
> In Hilary's replication book there is mention of a second volume on merge
> replication. Is this available yet? Is there a target date for
publication?
> Thanks,
> Bob Castleman
> DBA Poseur
>
|||Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bob Castleman" <nomail@.here> wrote in message
news:OQZlb6zeFHA.228@.TK2MSFTNGP12.phx.gbl...
> In Hilary's replication book there is mention of a second volume on merge
> replication. Is this available yet? Is there a target date for
publication?
> Thanks,
> Bob Castleman
> DBA Poseur
>

Hilary: Replication Latency (Part 2)

Hilary,
I know you have helped me with a replication latency script in the past. But
what i want is more of the following .
I have trans replication set up in continuous mode.
If I stop the log reader agent and the distribution agent while my publisher
is receiving some changes, I want to find out
a) how many commands/transactions are in the publisher that have not made it
to the distribution db ( Log Reader Agent Latency )
b) how many commands/transactions are in the distributor that have not made
it to the subscribingdbs ( Distribution Agent Latency )
c) How long are these command/trans are in the publisher since they came in
and not made it to the distribution db and also how long have they been
sitting in the distribution db since they came in and not made it to the
subscribing db
So primarily no. of outstanding cmds/trans and time is what im looking at ..
Is this something that you may have a handy script already ?
Thanks
answers inline
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23izmyu4FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hilary,
> I know you have helped me with a replication latency script in the past.
But
> what i want is more of the following .
> I have trans replication set up in continuous mode.
> If I stop the log reader agent and the distribution agent while my
publisher
> is receiving some changes, I want to find out
> a) how many commands/transactions are in the publisher that have not made
it
> to the distribution db ( Log Reader Agent Latency )
you can tell the number of transactions by issuing by sp_repltrans - this
will give you the number of transactions - but not the number of commands

> b) how many commands/transactions are in the distributor that have not
made
> it to the subscribingdbs ( Distribution Agent Latency )
select * from distribution.MSdistribution_status
Look at the undistributed commands column
> c) How long are these command/trans are in the publisher since they came
in
> and not made it to the distribution db and also how long have they been
> sitting in the distribution db since they came in and not made it to the
> subscribing db
run this in your distribution database
select time, entry_time from
SubscriberServerName.SubscriberDatabaseName.dbo.MS replicationX_subscriptions
,
msrepl_transactions
where transaction_timestamp=xact_seqno
You may want to run this in your subscription database as well to get time
in seconds as opposed to minutes
alter table MSreplication_subscriptions
alter column time datetime

> So primarily no. of outstanding cmds/trans and time is what im looking at
...
> Is this something that you may have a handy script already ?
> Thanks
>
sql

Hilary Cotters cleanup script

Hi,
Can someone point me to where I can find a copy of Hilary's replication celanup script?
Thanks,
Andrew
go to ava.co.uk and look in the technical resources section.
There is a version of it there. I'll try to post another one soon that has a
correction for non dbo objects.
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:21679998-9A1D-4645-BD94-93EFC264C7BF@.microsoft.com...
> Hi,
> Can someone point me to where I can find a copy of Hilary's replication
celanup script?
> Thanks,
> Andrew

Hilary Cotter - How do I buy your book on merge replication?

How do I buy your book on merge replication?
The site doesn't really explain. Thanks for the pointer in previous posts.
Well its not done yet. When it is printed it should be available from
nwsu.com and amazon.com.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:%23JA272UgEHA.644@.tk2msftngp13.phx.gbl...
> How do I buy your book on merge replication?
> The site doesn't really explain. Thanks for the pointer in previous posts.
>
|||and when transactional?
it shoud be released now.
On Fri, 13 Aug 2004 12:15:29 -0400, "Hilary Cotter" <hilaryk@.att.net>
wrote:

>Well its not done yet. When it is printed it should be available from
>nwsu.com and amazon.com.
|||The transactional book is being typeset, and has been since 7/22. We are
hoping it will go to press next week. After this it will be a 4-5 week print
process.
I am sorry about the delays, some of them have been due to final editing
processes, which will make the book an even higher quality product.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Thomas Hase" <tohas@.freenet.de> wrote in message
news:411e3897.55027546@.news.t-online.de...
> and when transactional?
> it shoud be released now.
>
> On Fri, 13 Aug 2004 12:15:29 -0400, "Hilary Cotter" <hilaryk@.att.net>
> wrote:
>
|||Any idea whem it will be released. I have a project in about 6-8 weeks which
it sounds really good for.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ehtuVEVgEHA.636@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Well its not done yet. When it is printed it should be available from
> nwsu.com and amazon.com.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:%23JA272UgEHA.644@.tk2msftngp13.phx.gbl...
posts.
>

Hilary Cotter - about your replication book

Does it show you how to replicate over the internet. I have read it can be
done. There are so many applications for this but little in the books or
internet in general. Regards, Chris.
Yes, it does. Review the sample chapter for an idea of its scope.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:eoFxolweEHA.708@.TK2MSFTNGP09.phx.gbl...
> Does it show you how to replicate over the internet. I have read it can be
> done. There are so many applications for this but little in the books or
> internet in general. Regards, Chris.
>
sql

High-transaction replication

We are trying to set up replication of a large (over 200GB) database,
attempting to break the transactional processing and reporting apart,
obviously replicating from the processing database. (This is a temporary fix
until we can implement our data warehouse.)
There are approximately 70 tables that will need to be replicated for this
to work for us, and I have broken these tables into logical grouping by
publication. We ran into some errors initially ("The process could not
execute 'sp_replcmds'..." errors), and found some references to assist with
this.
The next issue we have run into and are struggling with now are errors in
the log reader stating "The process could not execute
'sp_MSadd_repl_commands27hp'..." The descriptions reference that there was
deadlocking and the logreader was killed. The Distribution cleanup agent
appears to be the other process that was locking the table(s), with the job
taking up to 58 seconds to complete (48k transactions and 60k statements
deleted). I took and modified the schedule of the cleanup job to run every
minute between 6 and 6, and every 5 minutes during the night - thinking there
would be fewer transactions to delete, and allowing the table to be locked
for a shorter period of time. My thinking was correct, but the deadlocking
still happens, but recovers quicker than previously.
The worry we have is that we have not created the publications for all the
tables, and are already sending over 500k transactions/hour through. Once we
get all the tables back up and going we are projecting about another 500k
transactions, and are worried about the deadlocking. Are there any
ideas/settings we should be looking at to enable SQL to handle transactional
replication of 1 million+ transactions an hour, or are we really looking for
SQL to do what it cannot handle?
Thanks in advance for any ideas!
You should migrate to a remote distributor. You should also look at
replicating the execution of stored procedures. This will improve the
performance of your solution dramatically.
I would also look at decreasing your transaction retention period to perhaps
a day.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:1A4C62BB-A2E9-445D-8FE7-A9339E2EC209@.microsoft.com...
> We are trying to set up replication of a large (over 200GB) database,
> attempting to break the transactional processing and reporting apart,
> obviously replicating from the processing database. (This is a temporary
fix
> until we can implement our data warehouse.)
> There are approximately 70 tables that will need to be replicated for this
> to work for us, and I have broken these tables into logical grouping by
> publication. We ran into some errors initially ("The process could not
> execute 'sp_replcmds'..." errors), and found some references to assist
with
> this.
> The next issue we have run into and are struggling with now are errors in
> the log reader stating "The process could not execute
> 'sp_MSadd_repl_commands27hp'..." The descriptions reference that there
was
> deadlocking and the logreader was killed. The Distribution cleanup agent
> appears to be the other process that was locking the table(s), with the
job
> taking up to 58 seconds to complete (48k transactions and 60k statements
> deleted). I took and modified the schedule of the cleanup job to run
every
> minute between 6 and 6, and every 5 minutes during the night - thinking
there
> would be fewer transactions to delete, and allowing the table to be locked
> for a shorter period of time. My thinking was correct, but the
deadlocking
> still happens, but recovers quicker than previously.
> The worry we have is that we have not created the publications for all the
> tables, and are already sending over 500k transactions/hour through. Once
we
> get all the tables back up and going we are projecting about another 500k
> transactions, and are worried about the deadlocking. Are there any
> ideas/settings we should be looking at to enable SQL to handle
transactional
> replication of 1 million+ transactions an hour, or are we really looking
for
> SQL to do what it cannot handle?
> Thanks in advance for any ideas!
|||You can also take a look the Microsoft White Paper Replication Performance
Tuning. I have found some very interesting results with much larger databases
than that with implementing some of the modifications in there particulairly
the (-maxcmdsintran ) switch in the log reader agent and creating a
performance profile for the distribution agent. I can give you examples or
better documentation if you require. Please email - Richard.Hale@.MM-Games.com
with any questions
"Rob C" wrote:

> We are trying to set up replication of a large (over 200GB) database,
> attempting to break the transactional processing and reporting apart,
> obviously replicating from the processing database. (This is a temporary fix
> until we can implement our data warehouse.)
> There are approximately 70 tables that will need to be replicated for this
> to work for us, and I have broken these tables into logical grouping by
> publication. We ran into some errors initially ("The process could not
> execute 'sp_replcmds'..." errors), and found some references to assist with
> this.
> The next issue we have run into and are struggling with now are errors in
> the log reader stating "The process could not execute
> 'sp_MSadd_repl_commands27hp'..." The descriptions reference that there was
> deadlocking and the logreader was killed. The Distribution cleanup agent
> appears to be the other process that was locking the table(s), with the job
> taking up to 58 seconds to complete (48k transactions and 60k statements
> deleted). I took and modified the schedule of the cleanup job to run every
> minute between 6 and 6, and every 5 minutes during the night - thinking there
> would be fewer transactions to delete, and allowing the table to be locked
> for a shorter period of time. My thinking was correct, but the deadlocking
> still happens, but recovers quicker than previously.
> The worry we have is that we have not created the publications for all the
> tables, and are already sending over 500k transactions/hour through. Once we
> get all the tables back up and going we are projecting about another 500k
> transactions, and are worried about the deadlocking. Are there any
> ideas/settings we should be looking at to enable SQL to handle transactional
> replication of 1 million+ transactions an hour, or are we really looking for
> SQL to do what it cannot handle?
> Thanks in advance for any ideas!

High CPU utilization on Merge Replication with SQL 2005 Mobile

I have a question for anyone who mas some tips/pointers for optimizing SQL merge replication publications.

The front end web server is running IIS 6.0 on Windows 2003 x86 Server Standard (Server A). The back end database server is running SQL 2000 Standard on Windows 2003 x86 Standard (Server B). The merge replication clients connect via HTTPS over the Internet from a custom C#.NET 2005 application using SQL 2005 Mobile running on Windows Mobile 5.0 (Client).

The publication itself has several filters on it. The entry point uses the user's Windows username to start the filter. Based on the user, it then filters the records in multiple tables. There are 68 articles and 44 filter statements. The filters extend multiple layers deep, in other words they are not all filtering off the HOST_NAME() variable, some tables filter from records in tables that filter from the HOST_NAME() variable. The publication is set to minimize data sent to the clients, and considers a subscription out of date if it has not synced in the last 4 days. All the rowguids are indexed as well.

There are approximately 35 clients actively using the application at any given time. On average, a client will initiate a merge replication 3-4 times per hour from 8am-5pm. Generally, a sync will take between 10 seconds and 2 minutes to complete, with most of them being around 30 seconds on average.

When a client starts a sync, there is a spike to about 50% on the server's CPU graph. If multiple clients attempt to sync at the same time the CPU utilization can be pushed to 100% for extended periods (more than 30 seconds).

I recently completed a project to increase the bandwidth available to the clients, and plan to reduce the number of filters significantly (although this will obviously increase the amount of data going to the clients and the storage needs on the individual devices). I also plan on changing the setting to not minimize the amount of data sent to the clients.

Having said all that, does anyone have any information about how to further optimize merge publications to mobile clients? The next publication will be on SQL 2005 x64 Standard if I can solve the issues in the text environment. I would like to enhance the publication as much as possible to make the end user experience better than it currently is.

Thanks!

You're talking about CPU usage at the publisher, correct?

Can you double check that all columns involved in the merge join filters are indexed as well? If the columns are not indexed, this leads to table scans during syncs which can result in high CPU usage.

Are you also getting conflicts? There's a performance issue (which will be fixed in SP1) that can slow things down due to missing indexes on some conflict tables, but this shouldn't be an issue unless you're getting hundreds and hundreds of conflicts.

ALso, how "deep" are your filters? Do the merge join filters have 1 to many relationships, or many to many (see the @.join_unique_key parameter). The more levels deep you are, or any level that contains a @.join_unique_key = 0, can negatively affect performance.

Regardless, you can always run profiler at the publisher and trace a single subscriber to see which procs are consuming the most time. You can start with RPC:completed or SP:completed, and just grab the duration. You'll quickly see which procs are the problematic one. From there, you can then enable SP:StatmentEnded and enable ExecutionPlan to see exactly what statement and why it's slow.

|||

Indexes were definetly a piece of the puzzle. The @.join_unique_key was also in play, so thanks for putting me on to that one. For anyone else who is using Merge Replication with SQL Mobile, there are 2 very useful articles:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_replmergepartitioned.asp

http://msdn2.microsoft.com/en-us/library/ms147840.aspx

High CPU utilization on Merge Replication with SQL 2005 Mobile

I have a question for anyone who mas some tips/pointers for optimizing SQL merge replication publications.

The front end web server is running IIS 6.0 on Windows 2003 x86 Server Standard (Server A). The back end database server is running SQL 2000 Standard on Windows 2003 x86 Standard (Server B). The merge replication clients connect via HTTPS over the Internet from a custom C#.NET 2005 application using SQL 2005 Mobile running on Windows Mobile 5.0 (Client).

The publication itself has several filters on it. The entry point uses the user's Windows username to start the filter. Based on the user, it then filters the records in multiple tables. There are 68 articles and 44 filter statements. The filters extend multiple layers deep, in other words they are not all filtering off the HOST_NAME() variable, some tables filter from records in tables that filter from the HOST_NAME() variable. The publication is set to minimize data sent to the clients, and considers a subscription out of date if it has not synced in the last 4 days. All the rowguids are indexed as well.

There are approximately 35 clients actively using the application at any given time. On average, a client will initiate a merge replication 3-4 times per hour from 8am-5pm. Generally, a sync will take between 10 seconds and 2 minutes to complete, with most of them being around 30 seconds on average.

When a client starts a sync, there is a spike to about 50% on the server's CPU graph. If multiple clients attempt to sync at the same time the CPU utilization can be pushed to 100% for extended periods (more than 30 seconds).

I recently completed a project to increase the bandwidth available to the clients, and plan to reduce the number of filters significantly (although this will obviously increase the amount of data going to the clients and the storage needs on the individual devices). I also plan on changing the setting to not minimize the amount of data sent to the clients.

Having said all that, does anyone have any information about how to further optimize merge publications to mobile clients? The next publication will be on SQL 2005 x64 Standard if I can solve the issues in the text environment. I would like to enhance the publication as much as possible to make the end user experience better than it currently is.

Thanks!

You're talking about CPU usage at the publisher, correct?

Can you double check that all columns involved in the merge join filters are indexed as well? If the columns are not indexed, this leads to table scans during syncs which can result in high CPU usage.

Are you also getting conflicts? There's a performance issue (which will be fixed in SP1) that can slow things down due to missing indexes on some conflict tables, but this shouldn't be an issue unless you're getting hundreds and hundreds of conflicts.

ALso, how "deep" are your filters? Do the merge join filters have 1 to many relationships, or many to many (see the @.join_unique_key parameter). The more levels deep you are, or any level that contains a @.join_unique_key = 0, can negatively affect performance.

Regardless, you can always run profiler at the publisher and trace a single subscriber to see which procs are consuming the most time. You can start with RPC:completed or SP:completed, and just grab the duration. You'll quickly see which procs are the problematic one. From there, you can then enable SP:StatmentEnded and enable ExecutionPlan to see exactly what statement and why it's slow.

|||

Indexes were definetly a piece of the puzzle. The @.join_unique_key was also in play, so thanks for putting me on to that one. For anyone else who is using Merge Replication with SQL Mobile, there are 2 very useful articles:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_replmergepartitioned.asp

http://msdn2.microsoft.com/en-us/library/ms147840.aspx

High CPU utilization on distributor and sp_MSget_repl_commands

Hi,

In SQL 2005 Replication Monitor i was not seeing details for any of the publications on the "Distributor to Subscriber Histroy Tab" so i decided to stop and start synchronisation on this one publication. At this time there were approximayely 20000 undistributed commands. After the stop/start of the distribution agent, i started seeing messages like "x trasactions with x commands were delivered". Then i went and restarted all the other distribution agents using the Replication Monitor.

Has anyone experienced this kind of a behaviour?

The second issue is that our trasnactional replication looked to have caught up but i was supprised find that the distribution server was running at 100%. A profiler trace of the distribution database revealed that sp_MSget_repl_commands procedure was being executed and costing approximately in excess of 400 000 reads, 7000 in CPU cost and 15sec in duration. To me it looked as if sp_MSget_repl_commands has chosen an inefficient execution plan but then realised i couldn't recompile system procedures. I think a stop and start of the SQL instance is the only option i have.

PK

You can't recompile the proc itself, but you can force a recompile by updating the stats on the table. This should in theory trigger a recompile for the proc, and I'm sure it applies to procs in the resource db as well, but don't quote me on it.

Do you know if it was the replication monitor or the distribution agent that was making the proc call you were profiling? In pre-RTM days, there was a performance bug for the call made by the replication monitor, making it very expensive. I assume you're on RTM version of SQL 2005?

Without knowing what the plan looks like for the queries inside the proc, it's hard to say what the problem is.

High Availibility SQL 2000

Hi
We are setting up a soloution with 2 sql 2000 servers as a manuel
failover soloution.
My question is: What is best, to use transactional replication or log
shipping?
My old book about sql2000 says only logshipping for this situation but
i cant't figure out why not to use replication.
And please, I'ts not my desission to use this layout I only follow
orders so its not possible to use SQL clustering or SQL2005 for
example.
Best Regards Henrik Alstersj=F6Replication copies data elements, not the entire database. Foreign keys,
stored procedures, unique constraints, and user-defined functions are not
transferred in replication.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<alstersjo@.hotmail.com> wrote in message
news:1183648932.226829.223710@.m36g2000hse.googlegroups.com...
Hi
We are setting up a soloution with 2 sql 2000 servers as a manuel
failover soloution.
My question is: What is best, to use transactional replication or log
shipping?
My old book about sql2000 says only logshipping for this situation but
i cant't figure out why not to use replication.
And please, I'ts not my desission to use this layout I only follow
orders so its not possible to use SQL clustering or SQL2005 for
example.
Best Regards Henrik Alstersj|||On Jul 5, 11:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Replication copies data elements, not the entire database. Foreign keys,
> stored procedures, unique constraints, and user-defined functions are not
> transferred in replication.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <alster...@.hotmail.com> wrote in message
> news:1183648932.226829.223710@.m36g2000hse.googlegroups.com...
> Hi
> We are setting up a soloution with 2 sql 2000 servers as a manuel
> failover soloution.
> My question is: What is best, to use transactional replication or log
> shipping?
> My old book about sql2000 says only logshipping for this situation but
> i cant't figure out why not to use replication.
> And please, I'ts not my desission to use this layout I only follow
> orders so its not possible to use SQL clustering or SQL2005 for
> example.
> Best Regards Henrik Alstersj=F6
Just eo ensure I'm on the right page, when you state that you are
utilizing a manual failover, does that mean you will not be utilizing
Microsoft Clustering Services (MSCS)? Using this would take care of
any necessity for log shipping or replication and you could simply
utilize an active/passive configuration.
Otherwise, if you aren't utilizing this, what would the damage be of
simply backing up your database and when a manual failover is
necessary, simply restore the backup to your secondary server? Of
course, this may be a bit time consuming, however, with your current
situation, it doesn't sound like high availability is a top priority
and the time to restore your database should be fairly quick.
Aaron|||On 5 Juli, 22:06, acorcoran <acorco...@.gmail.com> wrote:
> On Jul 5, 11:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>
>
>
s,[vbcol=seagreen]
ot[vbcol=seagreen]
>
>
>
>
> Just eo ensure I'm on the right page, when you state that you are
> utilizing a manual failover, does that mean you will not be utilizing
> Microsoft Clustering Services (MSCS)? Using this would take care of
> any necessity for log shipping or replication and you could simply
> utilize an active/passive configuration.
> Otherwise, if you aren't utilizing this, what would the damage be of
> simply backing up your database and when a manual failover is
> necessary, simply restore the backup to your secondary server? Of
> course, this may be a bit time consuming, however, with your current
> situation, it doesn't sound like high availability is a top priority
> and the time to restore your database should be fairly quick.
> Aaron- D=F6lj citerad text -
> - Visa citerad text -
Hi
Thanks for your input, both of you.
The reason that we don't want to use backup restore functionality is
that the failovertime is not needed to bee quick but the data must be
up to date to the crash. So if we have a sevear servercrash we cant
get so fresh data from the sql server.
Regards Henrik

High Availibility SQL 2000

Hi
We are setting up a soloution with 2 sql 2000 servers as a manuel
failover soloution.
My question is: What is best, to use transactional replication or log
shipping?
My old book about sql2000 says only logshipping for this situation but
i cant't figure out why not to use replication.
And please, I'ts not my desission to use this layout I only follow
orders so its not possible to use SQL clustering or SQL2005 for
example.
Best Regards Henrik Alstersj
Replication copies data elements, not the entire database. Foreign keys,
stored procedures, unique constraints, and user-defined functions are not
transferred in replication.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<alstersjo@.hotmail.com> wrote in message
news:1183648932.226829.223710@.m36g2000hse.googlegr oups.com...
Hi
We are setting up a soloution with 2 sql 2000 servers as a manuel
failover soloution.
My question is: What is best, to use transactional replication or log
shipping?
My old book about sql2000 says only logshipping for this situation but
i cant't figure out why not to use replication.
And please, I'ts not my desission to use this layout I only follow
orders so its not possible to use SQL clustering or SQL2005 for
example.
Best Regards Henrik Alstersj
|||On Jul 5, 11:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Replication copies data elements, not the entire database. Foreign keys,
> stored procedures, unique constraints, and user-defined functions are not
> transferred in replication.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <alster...@.hotmail.com> wrote in message
> news:1183648932.226829.223710@.m36g2000hse.googlegr oups.com...
> Hi
> We are setting up a soloution with 2 sql 2000 servers as a manuel
> failover soloution.
> My question is: What is best, to use transactional replication or log
> shipping?
> My old book about sql2000 says only logshipping for this situation but
> i cant't figure out why not to use replication.
> And please, I'ts not my desission to use this layout I only follow
> orders so its not possible to use SQL clustering or SQL2005 for
> example.
> Best Regards Henrik Alstersj
Just eo ensure I'm on the right page, when you state that you are
utilizing a manual failover, does that mean you will not be utilizing
Microsoft Clustering Services (MSCS)? Using this would take care of
any necessity for log shipping or replication and you could simply
utilize an active/passive configuration.
Otherwise, if you aren't utilizing this, what would the damage be of
simply backing up your database and when a manual failover is
necessary, simply restore the backup to your secondary server? Of
course, this may be a bit time consuming, however, with your current
situation, it doesn't sound like high availability is a top priority
and the time to restore your database should be fairly quick.
Aaron
|||On 5 Juli, 22:06, acorcoran <acorco...@.gmail.com> wrote:
> On Jul 5, 11:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>
>
>
>
>
> Just eo ensure I'm on the right page, when you state that you are
> utilizing a manual failover, does that mean you will not be utilizing
> Microsoft Clustering Services (MSCS)? Using this would take care of
> any necessity for log shipping or replication and you could simply
> utilize an active/passive configuration.
> Otherwise, if you aren't utilizing this, what would the damage be of
> simply backing up your database and when a manual failover is
> necessary, simply restore the backup to your secondary server? Of
> course, this may be a bit time consuming, however, with your current
> situation, it doesn't sound like high availability is a top priority
> and the time to restore your database should be fairly quick.
> Aaron- Dlj citerad text -
> - Visa citerad text -
Hi
Thanks for your input, both of you.
The reason that we don't want to use backup restore functionality is
that the failovertime is not needed to bee quick but the data must be
up to date to the crash. So if we have a sevear servercrash we cant
get so fresh data from the sql server.
Regards Henrik

High Availability in Yukon

Yukon,
Can someone just describe briefly how much MS has improved
High Availability, using replication or clustering in Yukon
as compared to clustering. That is, what new features in
replication and clustering is available in Yukon.
TIA.
Hi,
'An Overview of SQL Server "Yukon" for the DBA'
http://www.microsoft.com/technet/pro...n/sqlydba.mspx
'Top 30 Features of SQL Server "Yukon"'
http://www.microsoft.com/sql/yukon/p...30features.asp
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"SQL Server DBA" <sqlsdba@.bigfoot.com> wrote in message
news:2jurb4FtnqijU1@.uni-berlin.de...
> Yukon,
> Can someone just describe briefly how much MS has improved
> High Availability, using replication or clustering in Yukon
> as compared to clustering. That is, what new features in
> replication and clustering is available in Yukon.
> TIA.
>
>

High Availability in Yukon

Yukon,
Can someone just describe briefly how much MS has improved
High Availability, using replication or clustering in Yukon
as compared to clustering. That is, what new features in
replication and clustering is available in Yukon.
TIA.Hi,
'An Overview of SQL Server "Yukon" for the DBA'
http://www.microsoft.com/technet/prodtechnol/sql/yukon/maintain/sqlydba.mspx
'Top 30 Features of SQL Server "Yukon"'
http://www.microsoft.com/sql/yukon/productinfo/top30features.asp
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"SQL Server DBA" <sqlsdba@.bigfoot.com> wrote in message
news:2jurb4FtnqijU1@.uni-berlin.de...
> Yukon,
> Can someone just describe briefly how much MS has improved
> High Availability, using replication or clustering in Yukon
> as compared to clustering. That is, what new features in
> replication and clustering is available in Yukon.
> TIA.
>
>

High Availability in Yukon

Yukon,
Can someone just describe briefly how much MS has improved
High Availability, using replication or clustering in Yukon
as compared to clustering. That is, what new features in
replication and clustering is available in Yukon.
TIA.Hi,
'An Overview of SQL Server "Yukon" for the DBA'
http://www.microsoft.com/technet/pr...in/sqlydba.mspx
'Top 30 Features of SQL Server "Yukon"'
http://www.microsoft.com/sql/yukon/...p30features.asp
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"SQL Server DBA" <sqlsdba@.bigfoot.com> wrote in message
news:2jurb4FtnqijU1@.uni-berlin.de...
> Yukon,
> Can someone just describe briefly how much MS has improved
> High Availability, using replication or clustering in Yukon
> as compared to clustering. That is, what new features in
> replication and clustering is available in Yukon.
> TIA.
>
>

High Availability in Yukon

Yukon,
Can someone just describe briefly how much MS has improved
High Availability, using replication or clustering in Yukon
as compared to clustering. That is, what new features in
replication and clustering is available in Yukon.
TIA.
Hi,
'An Overview of SQL Server "Yukon" for the DBA'
http://www.microsoft.com/technet/pro...n/sqlydba.mspx
'Top 30 Features of SQL Server "Yukon"'
http://www.microsoft.com/sql/yukon/p...30features.asp
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"SQL Server DBA" <sqlsdba@.bigfoot.com> wrote in message
news:2jurb4FtnqijU1@.uni-berlin.de...
> Yukon,
> Can someone just describe briefly how much MS has improved
> High Availability, using replication or clustering in Yukon
> as compared to clustering. That is, what new features in
> replication and clustering is available in Yukon.
> TIA.
>
>

High availability at subscribers using database mirroring

I have a mirrored database with transactional replication publishing
to 2 databases on different servers.
The amount of data written to the principal is fairly low.
The requirement is that at least one of the subscribers must be
available at all times with a latency of no greater than five minutes.
I thus have redundancy at the publisher via mirroring and at the
subscribers via multiple databases containing the same data, but I
can't see how to achieve redundancy at the distributor, since the
distribution database cannot be mirrored.
What is the recommended method of making the distributor highly
available? At this site there is reluctance to pursue SQL clustering
and to rename servers as described in BOL. At the moment it looks like
a need a set of scripts to completely set up replication from scratch
if the machine where the distributor DB lives goes offline.
Or, will Katmai offer new features to make all of the replication
components as highly available as the publisher can now be via
mirroring?
Hi Garry,
I understand that you would like to implement a high availability SQL
Server replication. You implemented Database mirroring for your principal
server and multiple databases with same data at the subscribers, but you
would like to know how to implement redundancy at the distributor since the
distribution database cannot be mirrored.
If I have misunderstood, please let me know.
I recommend that you can create a failover cluster for your distributer.
Deploy the distribution database to the virtual server instance. Also, for
your scenario, I think that it is better to use failover cluster for your
publisher since it just need two servers, but to implement a high
availability database mirroring, it needs three servers, principal server,
witness server and mirror server.
For how to setup a SQL Server 2005 failover cluster, you may refer to:
How to: Create a New SQL Server 2005 Failover Cluster (Setup)
http://msdn2.microsoft.com/en-us/library/ms179530.aspx
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Hiding data in replicated tables

Hello,
I have a merge replication setup between two sql 2000 servers. It works
fine, but there is some data in one table
that I wish to hide - at the subscriber database.
This data is in 4 columns in one table. It would be best if I could exclude
the table altogether from the replication process
but this cannot be done because the table is referenced by a foreign key
constraint with another table required in the replication process.
Also I cannot vertically filter out the columns because the columns are not
nullable. I did try selecting out the data by using the 'where' clause
and trying to eliminate the data that I wish to hide from the result set,
however this did not work.
Am not sure how I can go about hiding this data because it should not be
seen at the subscriber.
Any help will be appreciated.
Thanks,
Marise
How about hiding the data by using a view on the subscriber?
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
I am not too sure what you mean by hiding data using a view on the
subscriber...please explain some more how this can be done.
Because this is data hiding at the database level. i.e. anyone who logs
into the database and runs a select query should not be able to see these
four columns in this one table.
Thanks,
Marise
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u4Xlhg$dFHA.3712@.TK2MSFTNGP12.phx.gbl...
> How about hiding the data by using a view on the subscriber?
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Nothing special here - as long as they don't have select rights on the
table, the only way they'll be able to get to see any data is through the
view.
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Thanks for the response. The replicated database sits away from our location
and the user name that we use to do replication has been assigned to us - we
do not have the capability to alter database permissions. I wanted to find a
way to restrict the data itself being copied over at all.
Please let me know if there is a way.
Thanks,
Marise
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u2kqR4AfFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Nothing special here - as long as they don't have select rights on the
> table, the only way they'll be able to get to see any data is through the
> view.
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>