Replicating mirrored dbs: February 2012

Saturday, February 25, 2012

Replication

Hello,
Whats's the utility / advantages of the "Transactional publication with
updatable subscriptions"
thanks for your help!!!
nico
"pralnico" <pralnico@.discussions.microsoft.com> wrote in message
news:1C221973-1029-4380-8C24-A8C55817567F@.microsoft.com...
> Hello,
> Whats's the utility / advantages of the "Transactional publication with
> updatable subscriptions"
> thanks for your help!!!
>
Transactional replication starts with a snapshot. Thereafter only the
changes are replicated (which will typically mean less network traffic /
more frequent updates / less latency between different copies of the
database).
Updateable subscriptions means that the destination servers (subscribers)
can update the data on their own SQL Servers. SQL server will update it
locally and at the same time on the Publisher database, so that the changes
can then be replicated out to the other subscribers. This is done via a
distributed transaction.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.775 / Virus Database: 522 - Release Date: 08/10/2004

Replication

when using snapshots in replication, these files get copied to the hard drive. Can they be automatically deleted after the snapshot is posted?
Thanks
Niles,
if you have anonymous subscribers then they'll stay around until the publication's subscriber's timeout value. If you have standard subscribers, these files will be removed once they have all initialized. For transactional, this is when the distribution a
gent of each subscriber has run. The agent which removes the snapshot files is the distribution cleanup agent, which runs every 10 mins. You could run this immediately (manually) after your last initialization or you could schedule it to run more frequent
ly.
HTH,
Paul Ibison
|||What is the difference between anonymous subscriptions and standard and can it be switched easily
Thanks
|||An anonymous subscription is a type of pull subscription for which detailed
information about the subscription and the Subscriber is not stored. The
Subscriber is responsible for keeping an anonymous subscription
synchronized, and they are particularly used if Subscribers use the Internet
to access publications.
sp_helpsubscription will tell you if the subscription is anonymous or
http://msdn.microsoft.com/library/de...repl3_4dym.asp
Typically, the Subscriber is not explicitly named at the Publisher when
using anonymous subscriptions. So to change from a named subscription to an
anonymous one is not so simple and you'd drop and recreate the subscription.
To see if any of this is relevant to your case have a look at the
allow_anonymous value of sp_helppublication.
HTH,
Paul Ibison

Replication

Hello all,
I am setup a replication from DB_A(publisher) to DB_C(subscriber), but I got
a problem after replication created
Example:
Table1 in DB_A, i tried to add a new data field. but DB_C can't see new data
field after snopshot done
Is any method can synchronizate new scheme and new table to subscriber(DB_C)
automatically?
Thanks in advanced.
Schema changes are replicated in sql server 2005 (there are some
restrictions)
In sql server 2000 you can add/drop columns using system stored procedures
(sp_addmergecolumn and sp_dropmergecolumn) so they get replicated.
If this doesnt answer your question, please LMK
MC
"beachboy" <stanley@.javacatz.com> wrote in message
news:OxM2RVWLGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hello all,
> I am setup a replication from DB_A(publisher) to DB_C(subscriber), but I
> got
> a problem after replication created
> Example:
> Table1 in DB_A, i tried to add a new data field. but DB_C can't see new
> data
> field after snopshot done
> Is any method can synchronizate new scheme and new table to
> subscriber(DB_C)
> automatically?
> Thanks in advanced.
>
>
|||Assuming we're talking about SQL 2000, the correct stored procedures to call
are sp_repladdcolumn and sp_repldropcolumn.
Using these procs, there is no need to create a snapshot for the existing
subscribers - the column changes will occur as you synchronize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Urgh.
Thank you for correcting me, it serves me right for not checking before
posting.
My apologies beachboy.
MC
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Assuming we're talking about SQL 2000, the correct stored procedures to
> call are sp_repladdcolumn and sp_repldropcolumn.
> Using these procs, there is no need to create a snapshot for the existing
> subscribers - the column changes will occur as you synchronize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Thank you for your informations
Sorry that I am not a professional of sql server
Yes. I am using SQL 2000 standard edition. How to I call those procedure
(sp_repladdcolumn and sp_repldropcolumn)?
from DB_A or from DB_C?
How about if new table added?
Thanks in advanced.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> glsD:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.g bl...
> Assuming we're talking about SQL 2000, the correct stored procedures to
> call are sp_repladdcolumn and sp_repldropcolumn.
> Using these procs, there is no need to create a snapshot for the existing
> subscribers - the column changes will occur as you synchronize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You should execute the procedures on publisher. When you synch databases,
changes will be propagated to the subscriber.
Adding new table to replication means you must generate new snapshot and
re-initialize subscriptions.
MC
"beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Thank you for your informations
> Sorry that I am not a professional of sql server
> Yes. I am using SQL 2000 standard edition. How to I call those procedure
> (sp_repladdcolumn and sp_repldropcolumn)?
> from DB_A or from DB_C?
> How about if new table added?
> Thanks in advanced.
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
> glsD:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.g bl...
>
|||oic
for new scheme modification: execute store procedure, and corrected scheme
will send at next push
for new table on database: set a new "Push New Publcation" and then
re-initialize subscription
but do I need to remove the old snopshot?
Thanks.
"MC" <marko_culo#@.#yahoo#.#com#> glsD:eS2rOAYLGHA.536@.TK2MSFTNGP09.phx.gb l...
> You should execute the procedures on publisher. When you synch databases,
> changes will be propagated to the subscriber.
> Adding new table to replication means you must generate new snapshot and
> re-initialize subscriptions.
> MC
> "beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
> news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
>
|||Adding a new table doesn't require a reinitialization. The new snapshot will
contain just the new table (transactional) or all articles (merge) but in
either case synchronising will just propagate the new table and not all the
other articles.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||There is a difference between adding an article (table) to the existing
replication (publication) and adding a new publication to the setup. If you
add the article, run the snapshot and then synch if you left the option to
recreate objects. If you dont recreate objects then youll need to do some
more work. LMK if thats the case.
MC
"beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
news:uZZu0GZLGHA.1124@.TK2MSFTNGP10.phx.gbl...
> oic
> for new scheme modification: execute store procedure, and corrected scheme
> will send at next push
> for new table on database: set a new "Push New Publcation" and then
> re-initialize subscription
> but do I need to remove the old snopshot?
> Thanks.
>
> "MC" <marko_culo#@.#yahoo#.#com#>
> glsD:eS2rOAYLGHA.536@.TK2MSFTNGP09.phx.gb l...
>
|||Jim,
I believe that SQL Server 2005 Enterprise Edition has Oracle replication as
a feature. I have not investigated this (no need) so I do not know the
details.
RLF
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:4F65866C-59DD-4907-B7AE-60114AAF57F6@.microsoft.com...
> Hello,
> Is it possible to have replication between SQL Server and Oracle DB?
> Thanks,
>

replication

wot should be the ans of the below scenario..
You are the administrator of a SQL Server 2000 computer. You want to set up snapshot replication on the server. The server will serve as Publisher and Distributor for a minimum of 40/50 Subscribers. Currently, you want to publish 3GB/4GB of data, but the data is expected to grow over time. Subscribers will receive a new snapshot each month. You want to minimize the workload on the Publisher/Distributor. Which two actions should you take to configure snapshot replication? (Each correct answer presents part of the solution. Choose two)
a. Store the snapshot in the default folder on the Publisher/Distributor.
b. Store the snapshot in an alternative folder on the Publisher/Distributor.
c. Store the snapshot in a shared folder on a file server.
d. Create pull subscriptions.
e. Create push subscriptions.I would store the snapshot on a file server
and create pull subscriptions
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"rahul" <anonymous@.discussions.microsoft.com> wrote in message
news:A26AAAD3-6275-4C3D-90D9-F97CEB0FE429@.microsoft.com...
> wot should be the ans of the below scenario...
> You are the administrator of a SQL Server 2000 computer. You want to set
up snapshot replication on the server. The server will serve as Publisher
and Distributor for a minimum of 40/50 Subscribers. Currently, you want to
publish 3GB/4GB of data, but the data is expected to grow over time.
Subscribers will receive a new snapshot each month. You want to minimize the
workload on the Publisher/Distributor. Which two actions should you take to
configure snapshot replication? (Each correct answer presents part of the
solution. Choose two)
>
> a. Store the snapshot in the default folder on the Publisher/Distributor.
> b. Store the snapshot in an alternative folder on the
Publisher/Distributor.
> c. Store the snapshot in a shared folder on a file server.
> d. Create pull subscriptions.
> e. Create push subscriptions.
>|||Although I am not a replication expert, I would choose c and d.
Pull subscriptions ( by default) cause the distribution process to run at
the subscriber ( and not the publisher) which offloads work from the
publisher... ( As a side note, SQL 2000 allows a push subscription to have
the subscriber do his own work as well, it is called remote activation..)
Storing the snapshot files somewhere OTHER than the publisher/distributor
simply means that the publisher does NOT have to do IO during the initial
sync with the subscriber..
Hope this help.s
"rahul" <anonymous@.discussions.microsoft.com> wrote in message
news:A26AAAD3-6275-4C3D-90D9-F97CEB0FE429@.microsoft.com...
> wot should be the ans of the below scenario...
> You are the administrator of a SQL Server 2000 computer. You want to set
up snapshot replication on the server. The server will serve as Publisher
and Distributor for a minimum of 40/50 Subscribers. Currently, you want to
publish 3GB/4GB of data, but the data is expected to grow over time.
Subscribers will receive a new snapshot each month. You want to minimize the
workload on the Publisher/Distributor. Which two actions should you take to
configure snapshot replication? (Each correct answer presents part of the
solution. Choose two)
>
> a. Store the snapshot in the default folder on the Publisher/Distributor.
> b. Store the snapshot in an alternative folder on the
Publisher/Distributor.
> c. Store the snapshot in a shared folder on a file server.
> d. Create pull subscriptions.
> e. Create push subscriptions.
>

Replication

when using snapshots in replication, these files get copied to the hard drive. Can they be automatically deleted after the snapshot is posted
ThanksAn anonymous subscription is a type of pull subscription for which detailed
information about the subscription and the Subscriber is not stored. The
Subscriber is responsible for keeping an anonymous subscription
synchronized, and they are particularly used if Subscribers use the Internet
to access publications.
sp_helpsubscription will tell you if the subscription is anonymous or
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_repl3_4dym.asp
Typically, the Subscriber is not explicitly named at the Publisher when
using anonymous subscriptions. So to change from a named subscription to an
anonymous one is not so simple and you'd drop and recreate the subscription.
To see if any of this is relevant to your case have a look at the
allow_anonymous value of sp_helppublication.
HTH,
Paul Ibison

Replication

Hi,
Am trying to configure replication in SQL server 6.5. But when I to configure replication menu and select Install publishing, it gives the following error
Replication cannot be installed as @.@.SERVERNAME is NUL
Has anybody faced this kind of problem? BTW, my server name has Hyphen, but I guess that shouldn't be an issue as I had read in one of the article that in 6.5, this problem is taken care of. Could there be any other issue
Thanks very much in advanc
GYKDo you get a NULL if you run the following command?
SELECT @.@.SERVERNAME
GO
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:78C27465-53D5-4DDF-8A9C-1E8A8C436364@.microsoft.com...
Hi,
Am trying to configure replication in SQL server 6.5. But when I to
configure replication menu and select Install publishing, it gives the
following error;
Replication cannot be installed as @.@.SERVERNAME is NULL
Has anybody faced this kind of problem? BTW, my server name has Hyphen, but
I guess that shouldn't be an issue as I had read in one of the article that
in 6.5, this problem is taken care of. Could there be any other issue?
Thanks very much in advance
GYK|||YES
I get NULL when I run SELECT @.@.SERVERNAME
Thanks
GYK

replication

hey anybody knows where i will get detail information on
SQLServer 2000 Replication?
i want to go through all replication types?
Please let me know
thanks and regards
hrishikeshCheck
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replover_694n.asp.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"hrishikesh" <musaleh@.mahindrabt.com> wrote in message
news:094901c36df2$58d6c810$a301280a@.phx.gbl...
> hey anybody knows where i will get detail information on
> SQLServer 2000 Replication?
> i want to go through all replication types?
> Please let me know
> thanks and regards
> hrishikesh
>

Replication

Hi everybody,
Could someone explain me how to use replication in sql server with access as
frontend?This is such a large topic, I would suggest you begin by reading and
following the directions in Books onLine ( the SQL Server Help files..)
THere is enough information there to get you started...
"Nicollo Machiaveli" <machiaveli280278@.hotmail.com> wrote in message
news:eR%23IffTcDHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi everybody,
> Could someone explain me how to use replication in sql server with access
as
> frontend?
>

Replication

Hello all,
I am setup a replication from DB_A(publisher) to DB_C(subscriber), but I got
a problem after replication created
Example:
Table1 in DB_A, i tried to add a new data field. but DB_C can't see new data
field after snopshot done
Is any method can synchronizate new scheme and new table to subscriber(DB_C)
automatically?
Thanks in advanced.Schema changes are replicated in sql server 2005 (there are some
restrictions)
In sql server 2000 you can add/drop columns using system stored procedures
(sp_addmergecolumn and sp_dropmergecolumn) so they get replicated.
If this doesnt answer your question, please LMK
MC
"beachboy" <stanley@.javacatz.com> wrote in message
news:OxM2RVWLGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hello all,
> I am setup a replication from DB_A(publisher) to DB_C(subscriber), but I
> got
> a problem after replication created
> Example:
> Table1 in DB_A, i tried to add a new data field. but DB_C can't see new
> data
> field after snopshot done
> Is any method can synchronizate new scheme and new table to
> subscriber(DB_C)
> automatically?
> Thanks in advanced.
>
>|||Assuming we're talking about SQL 2000, the correct stored procedures to call
are sp_repladdcolumn and sp_repldropcolumn.
Using these procs, there is no need to create a snapshot for the existing
subscribers - the column changes will occur as you synchronize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Urgh.
Thank you for correcting me, it serves me right for not checking before
posting.
My apologies beachboy.
MC
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Assuming we're talking about SQL 2000, the correct stored procedures to
> call are sp_repladdcolumn and sp_repldropcolumn.
> Using these procs, there is no need to create a snapshot for the existing
> subscribers - the column changes will occur as you synchronize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||Thank you for your informations
Sorry that I am not a professional of sql server
Yes. I am using SQL 2000 standard edition. How to I call those procedure
(sp_repladdcolumn and sp_repldropcolumn)'
from DB_A or from DB_C?
How about if new table added?
Thanks in advanced.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> glsD:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.g
bl...
> Assuming we're talking about SQL 2000, the correct stored procedures to
> call are sp_repladdcolumn and sp_repldropcolumn.
> Using these procs, there is no need to create a snapshot for the existing
> subscribers - the column changes will occur as you synchronize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||You should execute the procedures on publisher. When you synch databases,
changes will be propagated to the subscriber.
Adding new table to replication means you must generate new snapshot and
re-initialize subscriptions.
MC
"beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Thank you for your informations
> Sorry that I am not a professional of sql server
> Yes. I am using SQL 2000 standard edition. How to I call those procedure
> (sp_repladdcolumn and sp_repldropcolumn)'
> from DB_A or from DB_C?
> How about if new table added?
> Thanks in advanced.
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
> glsD:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>|||oic
for new scheme modification: execute store procedure, and corrected scheme
will send at next push
for new table on database: set a new "Push New Publcation" and then
re-initialize subscription
but do I need to remove the old snopshot?
Thanks.
"MC" <marko_culo#@.#yahoo#.#com#> glsD:eS2rOAYLGHA.536@.TK2MSFTNGP09.phx.gbl...[vbco
l=seagreen]
> You should execute the procedures on publisher. When you synch databases,
> changes will be propagated to the subscriber.
> Adding new table to replication means you must generate new snapshot and
> re-initialize subscriptions.
> MC
> "beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
> news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
>[/vbcol]|||Adding a new table doesn't require a reinitialization. The new snapshot will
contain just the new table (transactional) or all articles (merge) but in
either case synchronising will just propagate the new table and not all the
other articles.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||There is a difference between adding an article (table) to the existing
replication (publication) and adding a new publication to the setup. If you
add the article, run the snapshot and then synch if you left the option to
recreate objects. If you dont recreate objects then youll need to do some
more work. LMK if thats the case.
MC
"beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
news:uZZu0GZLGHA.1124@.TK2MSFTNGP10.phx.gbl...
> oic
> for new scheme modification: execute store procedure, and corrected scheme
> will send at next push
> for new table on database: set a new "Push New Publcation" and then
> re-initialize subscription
> but do I need to remove the old snopshot?
> Thanks.
>
> "MC" <marko_culo#@.#yahoo#.#com#>
> glsD:eS2rOAYLGHA.536@.TK2MSFTNGP09.phx.gbl...
>|||Jim,
I believe that SQL Server 2005 Enterprise Edition has Oracle replication as
a feature. I have not investigated this (no need) so I do not know the
details.
RLF
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:4F65866C-59DD-4907-B7AE-60114AAF57F6@.microsoft.com...
> Hello,
> Is it possible to have replication between SQL Server and Oracle DB?
> Thanks,
>

Replication

Hi
I have one replication database and I need to move this database another
disk, but I dont wan't to remove the replication, my question is
How I can to move this database without remove the replication?"Valero" <Valero@.discussions.microsoft.com> wrote in message
news:D1B0C9DB-EBF3-41AE-8914-A5A45F37C5AC@.microsoft.com...
> Hi
> I have one replication database and I need to move this database another
> disk, but I dont wan't to remove the replication, my question is
> How I can to move this database without remove the replication?
>
Are you talking subscriber or publisher?
If you're talking subscriber, I believe you can sp_detachdb and sp_attachdb
(it's been awhile since I've had to do this.)
If you're talking the publisher, that's trickier.
What I've done is stop SQL Server, move the files, start sql server, ignore
the errors, and update the appropriate rows in the master DB, and then
restart SQL Server.

>

replication

wot should be the ans of the below scenario...
You are the administrator of a SQL Server 2000 computer. You want to set up
snapshot replication on the server. The server will serve as Publisher and D
istributor for a minimum of 40/50 Subscribers. Currently, you want to publis
h 3GB/4GB of data, but the
data is expected to grow over time. Subscribers will receive a new snapshot
each month. You want to minimize the workload on the Publisher/Distributor.
Which two actions should you take to configure snapshot replication? (Each c
orrect answer presents part
of the solution. Choose two)
a. Store the snapshot in the default folder on the Publisher/Distributor.
b. Store the snapshot in an alternative folder on the Publisher/Distributor.
c. Store the snapshot in a shared folder on a file server.
d. Create pull subscriptions.
e. Create push subscriptions.I would store the snapshot on a file server
and create pull subscriptions
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"rahul" <anonymous@.discussions.microsoft.com> wrote in message
news:A26AAAD3-6275-4C3D-90D9-F97CEB0FE429@.microsoft.com...

quote:

> wot should be the ans of the below scenario...
> You are the administrator of a SQL Server 2000 computer. You want to set

up snapshot replication on the server. The server will serve as Publisher
and Distributor for a minimum of 40/50 Subscribers. Currently, you want to
publish 3GB/4GB of data, but the data is expected to grow over time.
Subscribers will receive a new snapshot each month. You want to minimize the
workload on the Publisher/Distributor. Which two actions should you take to
configure snapshot replication? (Each correct answer presents part of the
solution. Choose two)

quote:

>
> a. Store the snapshot in the default folder on the Publisher/Distributor.
> b. Store the snapshot in an alternative folder on the

Publisher/Distributor.

quote:

> c. Store the snapshot in a shared folder on a file server.
> d. Create pull subscriptions.
> e. Create push subscriptions.
>

|||Although I am not a replication expert, I would choose c and d.
Pull subscriptions ( by default) cause the distribution process to run at
the subscriber ( and not the publisher) which offloads work from the
publisher... ( As a side note, SQL 2000 allows a push subscription to have
the subscriber do his own work as well, it is called remote activation..)
Storing the snapshot files somewhere OTHER than the publisher/distributor
simply means that the publisher does NOT have to do IO during the initial
sync with the subscriber..
Hope this help.s
"rahul" <anonymous@.discussions.microsoft.com> wrote in message
news:A26AAAD3-6275-4C3D-90D9-F97CEB0FE429@.microsoft.com...

quote:

> wot should be the ans of the below scenario...
> You are the administrator of a SQL Server 2000 computer. You want to set

quote:

>
> a. Store the snapshot in the default folder on the Publisher/Distributor.
> b. Store the snapshot in an alternative folder on the

Publisher/Distributor.

quote:

> c. Store the snapshot in a shared folder on a file server.
> d. Create pull subscriptions.
> e. Create push subscriptions.
>

Replication

Hello,
Whats's the utility / advantages of the "Transactional publication with
updatable subscriptions"
thanks for your help!!!
nico"pralnico" <pralnico@.discussions.microsoft.com> wrote in message
news:1C221973-1029-4380-8C24-A8C55817567F@.microsoft.com...
> Hello,
> Whats's the utility / advantages of the "Transactional publication with
> updatable subscriptions"
> thanks for your help!!!
>
Transactional replication starts with a snapshot. Thereafter only the
changes are replicated (which will typically mean less network traffic /
more frequent updates / less latency between different copies of the
database).
Updateable subscriptions means that the destination servers (subscribers)
can update the data on their own SQL Servers. SQL server will update it
locally and at the same time on the Publisher database, so that the changes
can then be replicated out to the other subscribers. This is done via a
distributed transaction.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.775 / Virus Database: 522 - Release Date: 08/10/2004

Replication

when using snapshots in replication, these files get copied to the hard driv
e. Can they be automatically deleted after the snapshot is posted?
ThanksNiles,
if you have anonymous subscribers then they'll stay around until the publica
tion's subscriber's timeout value. If you have standard subscribers, these f
iles will be removed once they have all initialized. For transactional, this
is when the distribution a
gent of each subscriber has run. The agent which removes the snapshot files
is the distribution cleanup agent, which runs every 10 mins. You could run t
his immediately (manually) after your last initialization or you could sched
ule it to run more frequent
ly.
HTH,
Paul Ibison|||What is the difference between anonymous subscriptions and standard and can
it be switched easily
Thanks|||An anonymous subscription is a type of pull subscription for which detailed
information about the subscription and the Subscriber is not stored. The
Subscriber is responsible for keeping an anonymous subscription
synchronized, and they are particularly used if Subscribers use the Internet
to access publications.
sp_helpsubscription will tell you if the subscription is anonymous or
l3_4dym.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
l3_4dym.asp
Typically, the Subscriber is not explicitly named at the Publisher when
using anonymous subscriptions. So to change from a named subscription to an
anonymous one is not so simple and you'd drop and recreate the subscription.
To see if any of this is relevant to your case have a look at the
allow_anonymous value of sp_helppublication.
HTH,
Paul Ibison

Replication

Hi
Is there any step by step guide available for replication in sql 2000
server database?
thanx
GopiSee if this helps: http://www.mssqlserver.com/replication/primer_p1.asp
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gopi" <gopigopi@.hotmail.com> wrote in message
news:%2340cUPtsFHA.3180@.TK2MSFTNGP10.phx.gbl...
Hi
Is there any step by step guide available for replication in sql 2000
server database?
thanx
Gopi|||Hi
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
"Gopi" <gopigopi@.hotmail.com> wrote in message
news:%2340cUPtsFHA.3180@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is there any step by step guide available for replication in sql 2000
> server database?
> thanx
> Gopi
>|||Thanx
but i try the same, it wont work properly i dont know why.
regards
Gopi
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:eUCBg6tsFHA.1448@.TK2MSFTNGP10.phx.gbl...
> See if this helps: http://www.mssqlserver.com/replication/primer_p1.asp
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Gopi" <gopigopi@.hotmail.com> wrote in message
> news:%2340cUPtsFHA.3180@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is there any step by step guide available for replication in sql 2000
> server database?
> thanx
> Gopi
>
>|||Thanx
i try the same thing but it doesnt work, is there anything i have to do
more? like more configuration other then wizard?
Regards
Gopi
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eX5I5$tsFHA.3732@.TK2MSFTNGP11.phx.gbl...
> Hi
> http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
> Replica (Step by step)
>
>
>
> "Gopi" <gopigopi@.hotmail.com> wrote in message
> news:%2340cUPtsFHA.3180@.TK2MSFTNGP10.phx.gbl...
>

replication

Hi,

I posted this in the repl section, but perhaps I should post it here as well. Say for example, with mirroring set to high-safety mode, if the mirror box goes down, replication stops flowing until the server comes back online? Or should replication continue to flow down to the subscribers since the primary server is still online, but the mirror is not.

Thanks

What you mean to say when mirror box goes down?

If the mirror server is unreachable or not available to connect on network then yes Replication will also be affected.

Replication

Hi all:

I am new to Mobile Data Bases. I am using mobile data base 2005. I want to transfer the data from the mobile DB to to the sql 2000 server DB. Presently i am using RDA where i am having a problem when the new records got inserted when the mobile and

sql 2000 server. I saw this as a limitation for the RDA. Will any one suggest me the better option for transfering the data from my mobile database to sql 2000.

I also want to if i used Merge replication, do i need to connect my device always to the internet or when ever i calls syncranize method it will automatically updates both the mobile and sql 2000 database.

Thanks in advance.

Vamsi.

Merge replication will work both ways "off-line", so that when you Synchronize both device and server changes will be synchronized. Be sure to filter your merge publication by userid or similar to avoid conflicts.|||

Thanks for your help ErikEJ.

Will you suggest which will be preferable RDA or Merge Replication for the following process.

I need to pick some columns from a table to client DB. where the client will make the changes. After making the changes they need to update the changes back to the server and clean the client DB.

Thanks

Vamsi

|||

For this process (I do not know all the gritty details, of course) I would suggest the following solution:

Merge Replciation - Use 2 tables, one for the data to be changed (could be filtered on the publication by user and/or date), and another for the changes. You can make this other one an "upload only" table by setting a filter like: "WHERE 1 = 0" on the publication.

Hope this makes sense.

|||

When i am using Merge Repliaction i had the problem when i am taking some columns from the Master table into the client and when i am trying to update an existing record ( record fetched from the master). in the client and tried Synchronize with Master.

The merge process could not enumerate changes at the 'Subscriber'. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200999)

Any one suggest how to over this problem.

In the remaining cases (like insert, delete) my application is working fine.

Thanks

Vamsi

|||Are you only taking some columns from the Master table? Make sure to include the primary key column(s).|||

thanks for your message.

I included Primary key.

The columns that i omitted for replication will allow nulls, even tough i am getting the same error.

The Master database that i am using for reliaction is sql server 2000 and the mobile data base is sql server 2005 mobile database.

Thanks

Vamsi.

Replication

does anyone know if it's possible to run a replication task with a script, and how (is there ans sp_ for this ?)

thanks,

What task are you asking about?|||yeah , sorry for unprecisions. I was talking about merge agent. I'd like to control it "on-demand", instead of a plannification. i'm running sql 2000.|||

dont know the script..but u can use replmerg.exe to run it from command prompt... its in the com folder of sqlserver..u shud know the parameters for the agent which u can get from sysjobsteps(in msdb)

|||

it's an idea. i'm going to try this.

thanks.

Replication

We have two systems one that has a domain setup and the other is a workgroup. We have them connected over a non-trusted internet connection. We are using SQL Server 2005 Enterprise version, for Peer-to-Peer transactional replication, the server that hasa domain, is called EXT-FRMAC1 and the other one is FRMAC_DC. FRMAC_DC can subscribe to EXT-FRMAC1's publication, but EXT-FRMAC1 can't subscribe to FRMAC_DC, and the error that we get on FRMAC_DC, is that FRMAC_DC\admin - username or password does not exist. admin exists on FRMAC_DC, I am not sure if we should try and put it on a domain or not.

Thanks.

Have you tried to setup with a SQL server account instead of Windows account?

Gary

Replication

When replication was taking place and when we tried to enter records in ERP Package i.e Navision we faced the following error.

231,”21s01”,[Microsoft][ODBC SQL Server Driver][SQL Server]Insert Error: Column name or number of supplied values does not match table definition.

Can anybody help me in resolving this.Please mail to me at

ranjit246@.yahoo.co.in

Merge replication requires every table article to contain a uniqueidentifier column with rowguid property set. If one doesn't exist, we add it. This column does have a DEFAULT constraint defined, so if you specify column list when doing your insert, this rowguid column will be transparent to your application.

It's good practice to specify a column list for all insert statements, I'm guessing your application doesn't do this, which is why your app is failing.

|||

Where do i make this change. I am using the DataSouce in VB DataViewGrid.

Please help

Replication

When replication was taking place and when we tried to enter records in ERP Package i.e Navision we faced the following error.

231,”21s01”,[Microsoft][ODBC SQL Server Driver][SQL Server]Insert Error: Column name or number of supplied values does not match table definition.

Can anybody help me in resolving this.Please mail to me at

ranjit246@.yahoo.co.in

It's good practice to specify a column list for all insert statements, I'm guessing your application doesn't do this, which is why your app is failing.

|||

Where do i make this change. I am using the DataSouce in VB DataViewGrid.

Please help

Replication

undefined

Hi,

I have successfully replicated a database (type : Transactional), but when i have executed a Alter scripts to effect the schema of the database it worked. My concern is whether the replication was done successfully or not ?
Can we change the schema of a replicated database by plain Alter scripts ?
Plz let me know....

Thanks
RakeshDepending on what you altered. If you want to alter rules or foreign keys without checking replication, you don't have to do anything to the replication setup. But if you change table schma, you need to either use the replication system procedures to change it or you have to drop all subscriptions and initialize them.|||I am able to alter the table schema by using Replication stored procedures, but my concern is, i am also able to change the table schema by simple alter statements on the primary database, but these changes are not making into secondary database i,e subscriber database. Do we get any error Msg trying to alter a table schema of a replicated database ?|||Yes, you should get an error messge when you try to use an ALTER TABLE command against a replicated table. The ALTER TABLE should fail, but it sounds like it is working for you.

Is it possible that the table you are affecting isn't being replicated (either it was never included in a publication, or that its article has been dropped from the publication)?

-PatP|||Yes, you should get an error messge when you try to use an ALTER TABLE command against a replicated table. The ALTER TABLE should fail, but it sounds like it is working for you.

Is it possible that the table you are affecting isn't being replicated (either it was never included in a publication, or that its article has been dropped from the publication)?

-PatP

In case of merge replication it is not possible to alter repilcated table but for transaction replication it works. Welcome to Microsoft...

Replication

Hello,
is there any How-To for implementing a replication between two msde? The
Knowledgbase-Articel Q324992 dont show the whole thing.
Many thanks in advance
Joerg Reinhardt
I just tried several times and now it is working on a new installed machine.
Thanks
Joerg Reinhardt
"Jrg Reinhardt" <noreply@.dragonconsulting.de> schrieb im Newsbeitrag
news:udmxrVSZFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hello,
> is there any How-To for implementing a replication between two msde? The
> Knowledgbase-Articel Q324992 dont show the whole thing.
> Many thanks in advance
> Joerg Reinhardt
>

Replication

I have a customer who has Access databases at local sites who wants the data
replicated nightly to MS-SQL Server using broadband connection. I have very
limited knowledge of best practices to accomplish this reliably.

Right now they are using some Java app that exports the Access data then
emails the files as CSV. The emails are then manually taken, copied then
imported. Very cumbersome process and VERY unreliable. There's gotaa be a
better way.

I want to recomend that they contract an expert to set this up but really
can't even say for certian what type of person they should look for at this
point.

Any ideas from a 40,000 foot perspective?

Thanks in advance.

- Ben"Ben" <Sorry@.TooManyViruses.com> wrote in message
news:6Eeic.14065$d7.7176@.twister.rdc-kc.rr.com...
> I have a customer who has Access databases at local sites who wants the
data
> replicated nightly to MS-SQL Server using broadband connection.

Clarification: They want data stored in Access at many sites replicated to a
centralized MS-SQL Database via broadband connections (Cable, DSL, etc.).

> I have very limited knowledge of best practices to accomplish this
reliably.
> Right now they are using some Java app that exports the Access data then
> emails the files as CSV. The emails are then manually taken, copied then
> imported. Very cumbersome process and VERY unreliable. There's gotaa be
a
> better way.
> I want to recomend that they contract an expert to set this up but really
> can't even say for certian what type of person they should look for at
this
> point.
> Any ideas from a 40,000 foot perspective?
> Thanks in advance.
> - Ben|||"Ben" <Sorry@.TooManyViruses.com> wrote in message
news:1Geic.14066$d7.8263@.twister.rdc-kc.rr.com...
> "Ben" <Sorry@.TooManyViruses.com> wrote in message
> news:6Eeic.14065$d7.7176@.twister.rdc-kc.rr.com...
> > I have a customer who has Access databases at local sites who wants the
> data
> > replicated nightly to MS-SQL Server using broadband connection.
> Clarification: They want data stored in Access at many sites replicated to
a
> centralized MS-SQL Database via broadband connections (Cable, DSL, etc.).
> > I have very limited knowledge of best practices to accomplish this
> reliably.
> > Right now they are using some Java app that exports the Access data then
> > emails the files as CSV. The emails are then manually taken, copied
then
> > imported. Very cumbersome process and VERY unreliable. There's gotaa
be
> a
> > better way.
> > I want to recomend that they contract an expert to set this up but
really
> > can't even say for certian what type of person they should look for at
> this
> > point.
> > Any ideas from a 40,000 foot perspective?
> > Thanks in advance.
> > - Ben

You might want to post in microsoft.public.sqlserver.replication for more
information, but I believe that Access is only supported as a subscriber
(receives data) not a publisher (sends data).

Alternatives would include setting up linked servers to the Access
databases, and pulling in the data with direct SELECT queries. Or use DTS to
create a generic data transfer package, which is parameterized based on the
Access DB location.

See "sp_addlinkedserver" and "Heterogeneous Subscribers" in Books Online for
more information.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4089b32d_2@.news.bluewin.ch...
> "Ben" <Sorry@.TooManyViruses.com> wrote in message
> news:1Geic.14066$d7.8263@.twister.rdc-kc.rr.com...
> > "Ben" <Sorry@.TooManyViruses.com> wrote in message
> > news:6Eeic.14065$d7.7176@.twister.rdc-kc.rr.com...
> > > I have a customer who has Access databases at local sites who wants
the
> > data
> > > replicated nightly to MS-SQL Server using broadband connection.
> > Clarification: They want data stored in Access at many sites replicated
to
> a
> > centralized MS-SQL Database via broadband connections (Cable, DSL,
etc.).
> > > I have very limited knowledge of best practices to accomplish this
> > reliably.
> > > > Right now they are using some Java app that exports the Access data
then
> > > emails the files as CSV. The emails are then manually taken, copied
> then
> > > imported. Very cumbersome process and VERY unreliable. There's gotaa
> be
> > a
> > > better way.
> > > > I want to recomend that they contract an expert to set this up but
> really
> > > can't even say for certian what type of person they should look for at
> > this
> > > point.
> > > > Any ideas from a 40,000 foot perspective?
> > > > Thanks in advance.
> > > > - Ben
> > > You might want to post in microsoft.public.sqlserver.replication for more
> information, but I believe that Access is only supported as a subscriber
> (receives data) not a publisher (sends data).
> Alternatives would include setting up linked servers to the Access
> databases, and pulling in the data with direct SELECT queries. Or use DTS
to
> create a generic data transfer package, which is parameterized based on
the
> Access DB location.
> See "sp_addlinkedserver" and "Heterogeneous Subscribers" in Books Online
for
> more information.
> Simon

Thanks for the leads.

Replication

Hi guys,
Is it possible to replicate in MSSQL SERVER standard edition and be
supported? Or do I need to buy enterprise edition.

Regards

Jaideepjai (dba_sybase2003@.yahoo.com) writes:
> Hi guys,
> Is it possible to replicate in MSSQL SERVER standard edition and be
> supported? Or do I need to buy enterprise edition.

You can do replicaiton in Standard Edition.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yeah I kind of knew that replication is possible in the standard
edition but will MIcrosoft support it.

Regards

Jaideep|||jai (dba_sybase2003@.yahoo.com) writes:
> Yeah I kind of knew that replication is possible in the standard
> edition but will MIcrosoft support it.

Yes.

Please check Books Online, the book SQL Server Architecture ->
Implementation Details -> Editions of SQL Server 2000 ->
Features Supported by the Editions of SQL Server 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
I am new to sql server replication but I have worked with sybase
replication. At present, the machine housing the sql server has 2 g of
memory with 1.4 g for sql server. How much more (memory), would I need
if I make the same machine work as publisher as well as the
distributor?

Regards

Jaideep|||jai (dba_sybase2003@.yahoo.com) writes:
> Erland,
> I am new to sql server replication but I have worked with sybase
> replication. At present, the machine housing the sql server has 2 g of
> memory with 1.4 g for sql server. How much more (memory), would I need
> if I make the same machine work as publisher as well as the
> distributor?

You will have to ask the friendly people in
microsoft.public.sqlserver.replication about that. My own experience
of replication is very thin. My gut feeling is that it would depend a
lot of the data volume you intend to replicate.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"jai" <dba_sybase2003@.yahoo.com> wrote in message
news:1124737698.551148.233410@.z14g2000cwz.googlegr oups.com...
> Erland,
> I am new to sql server replication but I have worked with sybase
> replication. At present, the machine housing the sql server has 2 g of
> memory with 1.4 g for sql server. How much more (memory), would I need
> if I make the same machine work as publisher as well as the
> distributor?

Howmuch data do you expect to replicate at any time?

Basically all that happens is the log reader puts rows into a distribution
DB which then gets used to distribute them to the subscribers.

You probably have enough RAM since you can't get much more in Standard
version anyway.

> Regards
> Jaideep

Replication

I want to recomend that they contract an expert to set this up but really
can't even say for certian what type of person they should look for at this
point.

Any ideas from a 40,000 foot perspective?

Thanks in advance.

Clarification: They want data stored in Access at many sites replicated to a
centralized MS-SQL Database via broadband connections (Cable, DSL, etc.).

You might want to post in microsoft.public.sqlserver.replication for more
information, but I believe that Access is only supported as a subscriber
(receives data) not a publisher (sends data).

See "sp_addlinkedserver" and "Heterogeneous Subscribers" in Books Online for
more information.

Thanks for the leads.|||I have more than 7 years of Experience with Replication if you want I can
Help u out in this. Please email me nehapatel75@.yahoo.com

replication

Hi everybody,

Could you please advise me what would be the best method to synchronize
MSSQL data in the following scenario:

SiteA (live site) -- T1 link -- SiteB (redundant site)

Each site consists of web server and MSSQL 2005 server. SiteA is the
site that my customers normally use when accessing my store. SiteB is
the backup site to which customers will be directed, should something
happen to Internet connectivity of siteA. When the failover occurs,
client request are pointing to my redundant web servers within a minute
(this happens automatically, as I am using the hosted DNS service). I
was wondering, what is the best method of keeping the database at siteB
up to date with all the transactions that took place at siteA? I do not
want my customers to access the redundant database if it does not
contain all the transactions. What would you advise?

Thanks,
ALThe best place to ask this is microsoft.public.sqlserver.replication.

I am no replication expert, but your requirement sounds like it needs
Database Mirroring. I am guessing that a T1 would be a bit slow for
synchronous mode, also known as high-safety mode. "Under high-safety
mode, when a session begins, the mirror server synchronizes the mirror
database with the principal database as quickly as possible. Once the
databases are synchronized a committed transaction is committed on
both partners, at the cost of increased transaction latency."

That would leave asynchronous operation mode, also known as
high-performance mode. "The mirror server attempts to keep up with
the log records sent by the principal server. The mirror database
might lag somewhat behind the principal database, though, typically,
the gap between the databases is small. However, the gap can become
substantial if the principal server is under a heavy work load or the
system of the mirror server is over loaded."

"In high-performance mode, as soon as the principal server sends a log
record to the mirror server, the principal server sends a confirmation
to the client, without waiting for an acknowledgement from the mirror
server. This means that transactions commit without waiting for the
mirror server to write the log to disk. Such asynchronous operation
permits the principal server to run with minimum transaction latency,
at the risk of some potential data loss."

The quotes are straight from BOL.

Roy Harvey
Beacon Falls, CT

On Sat, 29 Sep 2007 13:38:48 -0400, "aleu@.vp.pl" <aleu@.vp.plwrote:

Quote:

Originally Posted by

>Hi everybody,
>
>Could you please advise me what would be the best method to synchronize
>MSSQL data in the following scenario:
>
>SiteA (live site) -- T1 link -- SiteB (redundant site)
>
>Each site consists of web server and MSSQL 2005 server. SiteA is the
>site that my customers normally use when accessing my store. SiteB is
>the backup site to which customers will be directed, should something
>happen to Internet connectivity of siteA. When the failover occurs,
>client request are pointing to my redundant web servers within a minute
>(this happens automatically, as I am using the hosted DNS service). I
>was wondering, what is the best method of keeping the database at siteB
>up to date with all the transactions that took place at siteA? I do not
>want my customers to access the redundant database if it does not
>contain all the transactions. What would you advise?
>
>Thanks,
>AL

|||Roy Harvey (SQL Server MVP) wrote:

Quote:

Originally Posted by

The best place to ask this is microsoft.public.sqlserver.replication.
>
I am no replication expert, but your requirement sounds like it needs
Database Mirroring. I am guessing that a T1 would be a bit slow for
synchronous mode, also known as high-safety mode. "Under high-safety
mode, when a session begins, the mirror server synchronizes the mirror
database with the principal database as quickly as possible. Once the
databases are synchronized a committed transaction is committed on
both partners, at the cost of increased transaction latency."
>
That would leave asynchronous operation mode, also known as
high-performance mode. "The mirror server attempts to keep up with
the log records sent by the principal server. The mirror database
might lag somewhat behind the principal database, though, typically,
the gap between the databases is small. However, the gap can become
substantial if the principal server is under a heavy work load or the
system of the mirror server is over loaded."
>
"In high-performance mode, as soon as the principal server sends a log
record to the mirror server, the principal server sends a confirmation
to the client, without waiting for an acknowledgement from the mirror
server. This means that transactions commit without waiting for the
mirror server to write the log to disk. Such asynchronous operation
permits the principal server to run with minimum transaction latency,
at the risk of some potential data loss."
>
The quotes are straight from BOL.

Roy,

Thanks for the provided information. Is there any information on how
much bandwidth is required to perform the synchronous mode successfully
(I was considering purchasing second T1 link for redundancy anyway)?
Would this be sufficient? What happens in both synchronous and
asynchronous cases, when you loose the connectivity between the two
databases lets say for 10 minutes?

Could you please provide me with some links on MSSQL 2005 mirroring
options/requirements/best practices?

Thanks,
AL|||On Sat, 29 Sep 2007 22:46:44 -0400, "aleu@.vp.pl" <aleu@.vp.plwrote:

Quote:

Originally Posted by

>Thanks for the provided information. Is there any information on how
>much bandwidth is required to perform the synchronous mode successfully
>(I was considering purchasing second T1 link for redundancy anyway)?
>Would this be sufficient? What happens in both synchronous and
>asynchronous cases, when you loose the connectivity between the two
>databases lets say for 10 minutes?
>
>Could you please provide me with some links on MSSQL 2005 mirroring
>options/requirements/best practices?
>
>Thanks,
>AL

I don't have the hands-on experience with mirroring to have a good
feel for bandwidth requirements, but the major variable is the level
and nature of database activity that only you know. Reading a bit
more from some googled items I am more and more thinking you would be
restricted to asynchronous. If you take a look at your transaction
logs they should give you some idea how much data has to be pushed
over the link.

I suggest googling sql server 2005 mirroring and reading up. And
hoping someone with hands-on experience joins the discussion!

Roy Harvey
Beacon Falls, CT|||Roy Harvey (SQL Server MVP) wrote:

Quote:

Originally Posted by

If you loose connectivity for ten minutes it would have two impacts.
One, once the connection is re-established there is an additional
delay while the backlog is cleared. Two, you could not clear the
transactions from the log on the source system until after the
connection is re-established and the backlog cleared.
>
I don't have the hands-on experience with mirroring to have a good
feel for bandwidth requirements, but the major variable is the level
and nature of database activity that only you know. Reading a bit
more from some googled items I am more and more thinking you would be
restricted to asynchronous. If you take a look at your transaction
logs they should give you some idea how much data has to be pushed
over the link.
>
I suggest googling sql server 2005 mirroring and reading up. And
hoping someone with hands-on experience joins the discussion!
>

Thanks Roy. It is time to do some serious reading.

Regards,
AL

Replication

I have replication configured in an sql 2005 environment. I have
executed the wizard twice but each time the following maintenance jobs
are missing
Reinitialize subscriptions having data validation failures
Agent history clean up: distribution
Replication monitoring refresher for distribution.
Replication agents checkup
Distribution clean up: distribution
Expired subscription clean up
If someone can tell me what most be done to have these jobs created
or why the wizard failed to create them it would be a big help.What do you set up exactly? Distributor, Publisher and Subscriber? Also what
Replication method do you setup? Merge, Transactional, Snapshot?
--
Ekrem Önsoy
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:1191870323.081389.186990@.r29g2000hsg.googlegroups.com...
>I have replication configured in an sql 2005 environment. I have
> executed the wizard twice but each time the following maintenance jobs
> are missing
>
> Reinitialize subscriptions having data validation failures
> Agent history clean up: distribution
> Replication monitoring refresher for distribution.
> Replication agents checkup
> Distribution clean up: distribution
> Expired subscription clean up
> If someone can tell me what most be done to have these jobs created
> or why the wizard failed to create them it would be a big help.
>|||On Oct 8, 4:22 pm, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> What do you set up exactly? Distributor, Publisher and Subscriber? Also w=hat
> Replication method do you setup? Merge, Transactional, Snapshot?
> --
> Ekrem =D6nsoy
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:1191870323.081389.186990@.r29g2000hsg.googlegroups.com...
>
> >I have replication configured in an sql 2005 environment. I have
> > executed the wizard twice but each time the following maintenance jobs
> > are missing
> > Reinitialize subscriptions having data validation failures
> > Agent history clean up: distribution
> > Replication monitoring refresher for distribution.
> > Replication agents checkup
> > Distribution clean up: distribution
> > Expired subscription clean up
> > If someone can tell me what most be done to have these jobs created
> > or why the wizard failed to create them it would be a big help.- Hide q=uoted text -
> - Show quoted text -
I have transactional replication configured using a remote distributor
to 'push' articles to a subscriber with ALL jobs configured to run at
the distributor.|||Hello,
What version of SQL 2005 you are using. As there was bug prior SP1. try
increasing the size of your model db try configuring again.
Thanks
Ajay Rengunthwar
MCDBA,MCTS
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:1191876823.867493.221310@.50g2000hsm.googlegroups.com...
On Oct 8, 4:22 pm, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> What do you set up exactly? Distributor, Publisher and Subscriber? Also
> what
> Replication method do you setup? Merge, Transactional, Snapshot?
> --
> Ekrem Önsoy
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:1191870323.081389.186990@.r29g2000hsg.googlegroups.com...
>
> >I have replication configured in an sql 2005 environment. I have
> > executed the wizard twice but each time the following maintenance jobs
> > are missing
> > Reinitialize subscriptions having data validation failures
> > Agent history clean up: distribution
> > Replication monitoring refresher for distribution.
> > Replication agents checkup
> > Distribution clean up: distribution
> > Expired subscription clean up
> > If someone can tell me what most be done to have these jobs created
> > or why the wizard failed to create them it would be a big help.- Hide
> > quoted text -
> - Show quoted text -
I have transactional replication configured using a remote distributor
to 'push' articles to a subscriber with ALL jobs configured to run at
the distributor.

Replication

Hello,
Whats's the utility / advantages of the "Transactional publication with
updatable subscriptions"
thanks for your help!!!
nico"pralnico" <pralnico@.discussions.microsoft.com> wrote in message
news:1C221973-1029-4380-8C24-A8C55817567F@.microsoft.com...
> Hello,
> Whats's the utility / advantages of the "Transactional publication with
> updatable subscriptions"
> thanks for your help!!!
>
Transactional replication starts with a snapshot. Thereafter only the
changes are replicated (which will typically mean less network traffic /
more frequent updates / less latency between different copies of the
database).
Updateable subscriptions means that the destination servers (subscribers)
can update the data on their own SQL Servers. SQL server will update it
locally and at the same time on the Publisher database, so that the changes
can then be replicated out to the other subscribers. This is done via a
distributed transaction.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.775 / Virus Database: 522 - Release Date: 08/10/2004

Replication

Hello all,
I am setup a replication from DB_A(publisher) to DB_C(subscriber), but I got
a problem after replication created
Example:
Table1 in DB_A, i tried to add a new data field. but DB_C can't see new data
field after snopshot done
Is any method can synchronizate new scheme and new table to subscriber(DB_C)
automatically?
Thanks in advanced.Schema changes are replicated in sql server 2005 (there are some
restrictions)
In sql server 2000 you can add/drop columns using system stored procedures
(sp_addmergecolumn and sp_dropmergecolumn) so they get replicated.
If this doesnt answer your question, please LMK
MC
"beachboy" <stanley@.javacatz.com> wrote in message
news:OxM2RVWLGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hello all,
> I am setup a replication from DB_A(publisher) to DB_C(subscriber), but I
> got
> a problem after replication created
> Example:
> Table1 in DB_A, i tried to add a new data field. but DB_C can't see new
> data
> field after snopshot done
> Is any method can synchronizate new scheme and new table to
> subscriber(DB_C)
> automatically?
> Thanks in advanced.
>
>|||Assuming we're talking about SQL 2000, the correct stored procedures to call
are sp_repladdcolumn and sp_repldropcolumn.
Using these procs, there is no need to create a snapshot for the existing
subscribers - the column changes will occur as you synchronize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Urgh.
Thank you for correcting me, it serves me right for not checking before
posting.
My apologies beachboy.
MC
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Assuming we're talking about SQL 2000, the correct stored procedures to
> call are sp_repladdcolumn and sp_repldropcolumn.
> Using these procs, there is no need to create a snapshot for the existing
> subscribers - the column changes will occur as you synchronize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||Thank you for your informations
Sorry that I am not a professional of sql server
Yes. I am using SQL 2000 standard edition. How to I call those procedure
(sp_repladdcolumn and sp_repldropcolumn)'
from DB_A or from DB_C?
How about if new table added?
Thanks in advanced.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Assuming we're talking about SQL 2000, the correct stored procedures to
> call are sp_repladdcolumn and sp_repldropcolumn.
> Using these procs, there is no need to create a snapshot for the existing
> subscribers - the column changes will occur as you synchronize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||You should execute the procedures on publisher. When you synch databases,
changes will be propagated to the subscriber.
Adding new table to replication means you must generate new snapshot and
re-initialize subscriptions.
MC
"beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Thank you for your informations
> Sorry that I am not a professional of sql server
> Yes. I am using SQL 2000 standard edition. How to I call those procedure
> (sp_repladdcolumn and sp_repldropcolumn)'
> from DB_A or from DB_C?
> How about if new table added?
> Thanks in advanced.
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Assuming we're talking about SQL 2000, the correct stored procedures to
>> call are sp_repladdcolumn and sp_repldropcolumn.
>> Using these procs, there is no need to create a snapshot for the existing
>> subscribers - the column changes will occur as you synchronize.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>> (recommended sql server 2000 replication book:
>> http://www.nwsu.com/0974973602p.html)
>>
>|||oic
for new scheme modification: execute store procedure, and corrected scheme
will send at next push
for new table on database: set a new "Push New Publcation" and then
re-initialize subscription
but do I need to remove the old snopshot?
Thanks.
"MC" <marko_culo#@.#yahoo#.#com#> ¼¶¼g©ó¶l¥ó·s»D:eS2rOAYLGHA.536@.TK2MSFTNGP09.phx.gbl...
> You should execute the procedures on publisher. When you synch databases,
> changes will be propagated to the subscriber.
> Adding new table to replication means you must generate new snapshot and
> re-initialize subscriptions.
> MC
> "beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
> news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
>> Thank you for your informations
>> Sorry that I am not a professional of sql server
>> Yes. I am using SQL 2000 standard edition. How to I call those procedure
>> (sp_repladdcolumn and sp_repldropcolumn)'
>> from DB_A or from DB_C?
>> How about if new table added?
>> Thanks in advanced.
>>
>> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Assuming we're talking about SQL 2000, the correct stored procedures to
>> call are sp_repladdcolumn and sp_repldropcolumn.
>> Using these procs, there is no need to create a snapshot for the
>> existing subscribers - the column changes will occur as you synchronize.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>> (recommended sql server 2000 replication book:
>> http://www.nwsu.com/0974973602p.html)
>>
>>
>|||Adding a new table doesn't require a reinitialization. The new snapshot will
contain just the new table (transactional) or all articles (merge) but in
either case synchronising will just propagate the new table and not all the
other articles.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||There is a difference between adding an article (table) to the existing
replication (publication) and adding a new publication to the setup. If you
add the article, run the snapshot and then synch if you left the option to
recreate objects. If you dont recreate objects then youll need to do some
more work. LMK if thats the case.
MC
"beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
news:uZZu0GZLGHA.1124@.TK2MSFTNGP10.phx.gbl...
> oic
> for new scheme modification: execute store procedure, and corrected scheme
> will send at next push
> for new table on database: set a new "Push New Publcation" and then
> re-initialize subscription
> but do I need to remove the old snopshot?
> Thanks.
>
> "MC" <marko_culo#@.#yahoo#.#com#>
> ¼¶¼g©ó¶l¥ó·s»D:eS2rOAYLGHA.536@.TK2MSFTNGP09.phx.gbl...
>> You should execute the procedures on publisher. When you synch databases,
>> changes will be propagated to the subscriber.
>> Adding new table to replication means you must generate new snapshot and
>> re-initialize subscriptions.
>> MC
>> "beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
>> news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
>> Thank you for your informations
>> Sorry that I am not a professional of sql server
>> Yes. I am using SQL 2000 standard edition. How to I call those procedure
>> (sp_repladdcolumn and sp_repldropcolumn)'
>> from DB_A or from DB_C?
>> How about if new table added?
>> Thanks in advanced.
>>
>> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
>> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Assuming we're talking about SQL 2000, the correct stored procedures to
>> call are sp_repladdcolumn and sp_repldropcolumn.
>> Using these procs, there is no need to create a snapshot for the
>> existing subscribers - the column changes will occur as you
>> synchronize.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>> (recommended sql server 2000 replication book:
>> http://www.nwsu.com/0974973602p.html)
>>
>>
>>
>

Replication

database replication: if I replicate a database and I update a record on
Server_1 at the sametime I update the same record on Server_2 what would
happen ?

Thanks
TomThat answer will depend on what type of replication you have set up. You
haven't said what type of replication you are using. Nor have you said what
role these 2 servers play in your replication environment. More detail
information is needed.

"Tom Gao" <tomgaomail@.optushome.com.au> wrote in message
news:41c013a0$0$1125$afc38c87@.news.optusnet.com.au ...
> database replication: if I replicate a database and I update a record on
> Server_1 at the sametime I update the same record on Server_2 what would
> happen ?
> Thanks
> Tom

replication

What is meant by replication? where should we use this concept? please give me valuble informationThis is useful for yooer reference

http://databases.about.com/cs/sqlserver/a/aa041303a.htm|||Thankyou very much for this link Mr.vssp|||Thanks vinod

Replication

hello
i have to merge data to sql server via gprs in my application developed in
vb 6.0.
can any one help me how can i send data to sl server via gprs
Thx a lotNaz,
If you had a local instance of MSDE/SQL Server running on the local
machine/device able to connect via GPRS, you could, feasibly do a replicatio
n
or DTS over the connection to your other server/device.
I think to help though, we need more information on what devices,
connections, etc that you have.
James.
"Nazeedah" wrote:

> hello
> i have to merge data to sql server via gprs in my application developed in
> vb 6.0.
> can any one help me how can i send data to sl server via gprs
> Thx a lot
>|||Hello
Thx for your reply and concern
Well the application developed is on VB 6.0 and i have to merge the data to
sql server 2000.
The device used is a tablet PC
Please help me of how shouls i process
Regards
Nazeedah
"James Vickers" wrote:
[vbcol=seagreen]
> Naz,
> If you had a local instance of MSDE/SQL Server running on the local
> machine/device able to connect via GPRS, you could, feasibly do a replicat
ion
> or DTS over the connection to your other server/device.
> I think to help though, we need more information on what devices,
> connections, etc that you have.
> James.
> "Nazeedah" wrote:
>

replicating with Active directory

Is they any feature that allows replication between Active Directory an SQL
server. I know I can write an assembly to do it but I'm checking on easier
ways first.
--
Regards,
Gary BlakelyHi
You can not replicate Active Directory to SQL Server, but you can interogate
the active directory using the ADSI adapter see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/active_directory_service_interfaces_adsi.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/joining_heterogeneous_data.asp
If you want to have more resilience with your AD then you can replicated to
another Active Directory servers see
http://msdn2.microsoft.com/en-us/library/ms677921.aspx
John
"GaryDean" wrote:
> Is they any feature that allows replication between Active Directory an SQL
> server. I know I can write an assembly to do it but I'm checking on easier
> ways first.
>
> --
> Regards,
> Gary Blakely
>
>|||Hi Gary,
Did you mean that you want to setup a replication between two different
domains?
If you have to set up replication across two non-trusted domains or
workgroups by using Windows authentication, you must configure pass-through
authentication. Configure a local Windows account on both the Publisher and
the Subscriber that has the same name and password. After you configure
this account, use the account to start the SQL Server Agent service on the
Publisher for push subscriptions and on the Subscriber for pull
subscriptions. Make sure that this account is configured according to the
"Setting up Windows Services Accounts" topic in SQL Server Books Online.
If the user tries to make a network connection to a remote computer that is
in a non-trusted domain, the logon proceeds as if the user is connecting to
an account on the remote computer. The remote computer authenticates the
logon credentials against its directory database. If the account is not
defined in the directory database, but the guest account on the remote
computer is enabled and the guest account has no password set, the user
logs on with guest permissions. If the guest account is not enabled, the
logon is not successful.
For more information, please refer to:
HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted
Domains or Across the Internet
http://support.microsoft.com/kb/321822/en-us
Please feel free to let me know if you have any other questions or
concerns.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
======================================================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.
======================================================|||Hi,
Happy New Year!
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================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.
======================================================

Replicating with Access in Hilary's book

On page 50, you discuss replicating SQL Server to Access. Two questions:
1. While you discuss all 3 replication types, it sounds like you are
recommending Merge as the solution?
2. In the hint, it says in part, "all transactions occuring on the Merge
Subscriber [Access] will be "published" or merged to the SQL Server
database." Do you mean the transactions are merged on the SQL Server's
schedule (both directions?) or via some user interaction on the Access side?
1) while you can use snapshot, transactional, or merge replication; what I
was trying to get at was that many people want the access database to
publish to sql server, and I was suggesting that you could set up the access
database as a subscriber and if all the changes originate there it could act
as the publisher.
2) All transactions will be merged with the publisher, but they all
originate with the subcriber/access database. the synchronization will be
kicked off each time the push agent runs - I don't believe you can do a
pull.
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
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:%239ZFAV2cFHA.1044@.TK2MSFTNGP10.phx.gbl...
> On page 50, you discuss replicating SQL Server to Access. Two questions:
> 1. While you discuss all 3 replication types, it sounds like you are
> recommending Merge as the solution?
> 2. In the hint, it says in part, "all transactions occuring on the Merge
> Subscriber [Access] will be "published" or merged to the SQL Server
> database." Do you mean the transactions are merged on the SQL Server's
> schedule (both directions?) or via some user interaction on the Access
> side?
>

Replicating very large articles but need to avoid locks from snap-

I have a very large and very active table which I want to replicate to
another server. How can I set-up a Publication for this table and avoid the
locking during the initial snap-shot in SQL 2005? SQL 2000 allowed
configuring replication without generating a snap-shot - assumed data was
already present. I used this technique and would update unique columns on
the source or primary key to send over complete row delete & inserts to
finally achieve sync'. I'm looking for a similiar work-around in SQL 2005
but don't see it. I'm aware of initializing from a backup but that seems
extreme for just a ~5 of 400 tables I want to replicate. Any ideas how to
skin this cat would be appreciated. Thanks. -CqlBoy
The locking which occurs in SQL 2005 is much less than the default locking
which would occur in SQL 2000.
In SQL 2005 you have the no-sync option as well. To do this create your
publication and then create your subscription as per normal.
Then when you get to the initialize Subscriptions dialog uncheck initialize.
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:47464A31-3AD8-4A5A-9558-3009CE84A498@.microsoft.com...
>I have a very large and very active table which I want to replicate to
> another server. How can I set-up a Publication for this table and avoid
> the
> locking during the initial snap-shot in SQL 2005? SQL 2000 allowed
> configuring replication without generating a snap-shot - assumed data was
> already present. I used this technique and would update unique columns on
> the source or primary key to send over complete row delete & inserts to
> finally achieve sync'. I'm looking for a similiar work-around in SQL 2005
> but don't see it. I'm aware of initializing from a backup but that seems
> extreme for just a ~5 of 400 tables I want to replicate. Any ideas how to
> skin this cat would be appreciated. Thanks. -CqlBoy
|||Is it also possible to force at the Publisher like in SQL 2000 to send a pair
of DELETE/INSERT statements for an entire row by performing an UPDATE on a
primary key or column that was unique? This was an issue in SQL Server 2000
when the PK was an identiy column, I couldn't update the PK and force the
DELETE/INSERT but their was a trace flag that would trigger this behavior on
any column, thus making it possible to sync' an entire row.
Thanks.
-Blake
"Hilary Cotter" wrote:

> The locking which occurs in SQL 2005 is much less than the default locking
> which would occur in SQL 2000.
> In SQL 2005 you have the no-sync option as well. To do this create your
> publication and then create your subscription as per normal.
> Then when you get to the initialize Subscriptions dialog uncheck initialize.
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:47464A31-3AD8-4A5A-9558-3009CE84A498@.microsoft.com...
>
>
|||I think this is what you are referring to in SQL 2000. It is my
understanding that in SQL 2005, deferred updates are now replicated as an
update not a delete/insert pair, however I can't seem to find any official
documentation supporting it.
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:CC9DE155-8328-4CBF-8BF5-EE68900A59B1@.microsoft.com...[vbcol=seagreen]
> Is it also possible to force at the Publisher like in SQL 2000 to send a
> pair
> of DELETE/INSERT statements for an entire row by performing an UPDATE on a
> primary key or column that was unique? This was an issue in SQL Server
> 2000
> when the PK was an identiy column, I couldn't update the PK and force the
> DELETE/INSERT but their was a trace flag that would trigger this behavior
> on
> any column, thus making it possible to sync' an entire row.
> Thanks.
> -Blake
> "Hilary Cotter" wrote:
|||Hi Blake, being the person responsible for most of snapshot processing in
transactional\snapshot replication, I must admit that it is a bit difficult
for me to read your posts. That said, in the interest of product
improvement, I must ask what sort of problems you have experienced in
SQL2000 (I can tell locking being one of them) that causes you to develop
such an intense aversion against using replication snapshot processing at
all.
As Hilary had mentioned, locking during snapshot generation for
transactional replication is much reduced by default in SQL2005, and you can
further reduce it by using the new 'database snapshot' sync_method if you
are running Enterprise Edition. In addition, I had also put in quite a few
performance improvements for snapshot processing in SQL2005 which should
hopefully make replication snapshot processing less painful to use. As such,
I would really appreciate if you can give replication snapshot processing a
try in SQL2005.
Thanks much,
-Raymond
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:CC9DE155-8328-4CBF-8BF5-EE68900A59B1@.microsoft.com...[vbcol=seagreen]
> Is it also possible to force at the Publisher like in SQL 2000 to send a
> pair
> of DELETE/INSERT statements for an entire row by performing an UPDATE on a
> primary key or column that was unique? This was an issue in SQL Server
> 2000
> when the PK was an identiy column, I couldn't update the PK and force the
> DELETE/INSERT but their was a trace flag that would trigger this behavior
> on
> any column, thus making it possible to sync' an entire row.
> Thanks.
> -Blake
> "Hilary Cotter" wrote:
|||Raymond, thank you for the response and tip regarding snapshot processing.
I need to investigate/experiment more with this. I am admittingly not
familiar yet with all of SQL 2005's offerings. Also, let me add, I'm not at
all averse to replication and used SQL 2000 Transactional replication
extensively at my former employer, Experian Corp.
Key things noted from my replication experience is this:
1) Taking an initial snap-shot against several large and important
order-related tables incurring heavy insert/update/delete activity
effectively took the company down until the snap-shot completed. This could
be hours, something upper management would never tolerate. Heaven forbid if
we lost sync' !
2) Publications containing hot articles presented significant loads to the
disk IO subsystem, something that very few DBA's are aware of or understand.
For example, if your publication contained articles responsible for much of
the server IO you need to be aware that this resource drain will effectively
double via the Distributor when replicated. This was a surprize to me when I
first discovered it but it makes perfect sense in hindsight.
3) The last issue came from the Distributor clean up job. Whenever this
executed, it exercised a massive IO spike against its raid group, thus
affecting everything associated with those disks. Yep, I tinkered with the
Clean-Up job schedule and tweaked the agent profiles but could never seem to
alleviate these massive spikes. I never made it this far, but I was
seriously contemplating customizing the code in the Distributor Clean-up
proc' to break up and spread the IO demand over a longer interval, anything
to avoid or reduce this sharp massive hits against the disks.
I learned and became more sensitized to these issues when tasked to
re-architect SQL Server on our EMC Clariion CX500 and CX700 SAN. I captured
file level IO stats using the little understood/known SQL Server function
fn_virtualstats as a source, sampling @. 1 minute intervals. Querying against
one weeks data was very enlightening.
I am aware of moving the Distributor to its own dedicated box/disks but
could never convince management to do so. BTW... it was a heavy bang against
the SAN cache and it seemed we had evidence that the Distributor Clean-Up job
cause global issues via the SAN. Evidence we out grew our SAN, I guess.
I apoligize for this lengthy response but the key answers I am looking for
is how to initialize replication for very large and critical, order-related
articles without taking the company down. Secondly, is their a practical
means of determing the additional burden to the TempDB ? Log ? ... and the
underlying disk subsystem. i.e. can I handle the load? I actually have a
rough idea on this last point but if you have anything to share I'd love to
hear it.
Thanks again for taking the time to respond.
Blake Colson a.k.a CqlBoy
Lead DBA of Operations
RealtyTrac, Inc.
Irvine, CA
"Raymond Mak [MSFT]" wrote:

> Hi Blake, being the person responsible for most of snapshot processing in
> transactional\snapshot replication, I must admit that it is a bit difficult
> for me to read your posts. That said, in the interest of product
> improvement, I must ask what sort of problems you have experienced in
> SQL2000 (I can tell locking being one of them) that causes you to develop
> such an intense aversion against using replication snapshot processing at
> all.
> As Hilary had mentioned, locking during snapshot generation for
> transactional replication is much reduced by default in SQL2005, and you can
> further reduce it by using the new 'database snapshot' sync_method if you
> are running Enterprise Edition. In addition, I had also put in quite a few
> performance improvements for snapshot processing in SQL2005 which should
> hopefully make replication snapshot processing less painful to use. As such,
> I would really appreciate if you can give replication snapshot processing a
> try in SQL2005.
> Thanks much,
> -Raymond
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:CC9DE155-8328-4CBF-8BF5-EE68900A59B1@.microsoft.com...
>
>
|||Blake, I am probably not reading you previous post (the one before you
latest response) correctly but it seems to me that doing a (full table) bulk
update on the pk or uq will incur rather significant resource\locking
contention at the publisher database, and the logreader\distribution agent
will need to transfer almost twice the amount of data (delete\insert +
command formatting overhead) thereby putting further strain on the network
and the cleanup agent. That seems to be a bit of an extreme measure to avoid
replication snapshot processing. And as I have mentioned before, locking
overhead for snapshot generation is much reduced in SQL2005 by default and
you can use sync_method = 'database snapshot' to reduce that further. Paul
Ibison also has a nice article up on his website
(http://www.replicationanswers.com/BCPPartitioning.asp) talking about an
enhancement in SQL2005 that allows a large table to be bulk-copied by the
snapshot agent in parallel. That said, it is not my intention to be pushy
about this and there are indeed cases where all the enhancement in SQL2005
snapshot processing will be inadequate to scale (it is not a pretty sight
but I have seen people managed 100GBs snapshot on SQL2000). I am merely ...
intrigued... by your comments and I very much appreciate your (extremely
detailed) feedback.
-Ryamond
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:51FEF6A7-F5E5-4D54-BB9E-542EE39D5010@.microsoft.com...[vbcol=seagreen]
> Raymond, thank you for the response and tip regarding snapshot
> processing.
> I need to investigate/experiment more with this. I am admittingly not
> familiar yet with all of SQL 2005's offerings. Also, let me add, I'm not
> at
> all averse to replication and used SQL 2000 Transactional replication
> extensively at my former employer, Experian Corp.
> Key things noted from my replication experience is this:
> 1) Taking an initial snap-shot against several large and important
> order-related tables incurring heavy insert/update/delete activity
> effectively took the company down until the snap-shot completed. This
> could
> be hours, something upper management would never tolerate. Heaven forbid
> if
> we lost sync' !
> 2) Publications containing hot articles presented significant loads to
> the
> disk IO subsystem, something that very few DBA's are aware of or
> understand.
> For example, if your publication contained articles responsible for much
> of
> the server IO you need to be aware that this resource drain will
> effectively
> double via the Distributor when replicated. This was a surprize to me
> when I
> first discovered it but it makes perfect sense in hindsight.
> 3) The last issue came from the Distributor clean up job. Whenever this
> executed, it exercised a massive IO spike against its raid group, thus
> affecting everything associated with those disks. Yep, I tinkered with
> the
> Clean-Up job schedule and tweaked the agent profiles but could never seem
> to
> alleviate these massive spikes. I never made it this far, but I was
> seriously contemplating customizing the code in the Distributor Clean-up
> proc' to break up and spread the IO demand over a longer interval,
> anything
> to avoid or reduce this sharp massive hits against the disks.
> I learned and became more sensitized to these issues when tasked to
> re-architect SQL Server on our EMC Clariion CX500 and CX700 SAN. I
> captured
> file level IO stats using the little understood/known SQL Server function
> fn_virtualstats as a source, sampling @. 1 minute intervals. Querying
> against
> one weeks data was very enlightening.
> I am aware of moving the Distributor to its own dedicated box/disks but
> could never convince management to do so. BTW... it was a heavy bang
> against
> the SAN cache and it seemed we had evidence that the Distributor Clean-Up
> job
> cause global issues via the SAN. Evidence we out grew our SAN, I guess.
> I apoligize for this lengthy response but the key answers I am looking for
> is how to initialize replication for very large and critical,
> order-related
> articles without taking the company down. Secondly, is their a practical
> means of determing the additional burden to the TempDB ? Log ? ... and
> the
> underlying disk subsystem. i.e. can I handle the load? I actually have a
> rough idea on this last point but if you have anything to share I'd love
> to
> hear it.
> Thanks again for taking the time to respond.
> Blake Colson a.k.a CqlBoy
> Lead DBA of Operations
> RealtyTrac, Inc.
> Irvine, CA
>
> "Raymond Mak [MSFT]" wrote:
|||Sort of a (perhaps not so useful) data point, I have seen from internal
testing that the SQL2005 snapshot agent is able to pull 100MB(ytes)/s on an
admittedly high end server locally (8-way 3.2Ghz Intel proc [P4 based?] &
8GB RAM & 3disk RAID0). Granted that the data we used is artificially
generated but still.
-Raymond
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:uc4Q3wSiHHA.1708@.TK2MSFTNGP03.phx.gbl...
> Blake, I am probably not reading you previous post (the one before you
> latest response) correctly but it seems to me that doing a (full table)
> bulk update on the pk or uq will incur rather significant resource\locking
> contention at the publisher database, and the logreader\distribution agent
> will need to transfer almost twice the amount of data (delete\insert +
> command formatting overhead) thereby putting further strain on the network
> and the cleanup agent. That seems to be a bit of an extreme measure to
> avoid replication snapshot processing. And as I have mentioned before,
> locking overhead for snapshot generation is much reduced in SQL2005 by
> default and you can use sync_method = 'database snapshot' to reduce that
> further. Paul Ibison also has a nice article up on his website
> (http://www.replicationanswers.com/BCPPartitioning.asp) talking about an
> enhancement in SQL2005 that allows a large table to be bulk-copied by the
> snapshot agent in parallel. That said, it is not my intention to be pushy
> about this and there are indeed cases where all the enhancement in SQL2005
> snapshot processing will be inadequate to scale (it is not a pretty sight
> but I have seen people managed 100GBs snapshot on SQL2000). I am merely
> ... intrigued... by your comments and I very much appreciate your
> (extremely detailed) feedback.
> -Ryamond
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:51FEF6A7-F5E5-4D54-BB9E-542EE39D5010@.microsoft.com...
>

Saturday, February 25, 2012

Blog Archive

About Me