Hi,
I am having MSSQL 6.5 with batch driven transactions
I am upgrading to 2000 and plan to do some black box
testing.
To test the batch jobs,I am planning to run the job on 6.5
and 2000 and test the rowcounts and records added/updated
based on datetime.(I don't have delete..so checking the
data on the basis of timestamp works)
However my rowcount and timestamp queries take a long time
to run on 6.5.(More than 50 mins as data volume and no of
tables are high)
I can't wait that long as the production server 6.5 has
jobs that run after every 30 mins.Is there a way just to
check whether 6.5 and 2000 data are in sync using some
built in utility or better algortithm(I don't want to
compare using SQL,but comparison at the lower level such
as file etc would do)
Basically do something like what replication or mirroring
does but not actually move the data but just check
Thanks
RakeshTake a look at the sp sp_replcounters... It shows how many records in the
log are awaiting replication...
"ar" <ambewadkarrakesh@.johndeere.com> wrote in message
news:05fb01c344f6$cb8265b0$a501280a@.phx.gbl...
> Hi,
> I am having MSSQL 6.5 with batch driven transactions
> I am upgrading to 2000 and plan to do some black box
> testing.
> To test the batch jobs,I am planning to run the job on 6.5
> and 2000 and test the rowcounts and records added/updated
> based on datetime.(I don't have delete..so checking the
> data on the basis of timestamp works)
> However my rowcount and timestamp queries take a long time
> to run on 6.5.(More than 50 mins as data volume and no of
> tables are high)
> I can't wait that long as the production server 6.5 has
> jobs that run after every 30 mins.Is there a way just to
> check whether 6.5 and 2000 data are in sync using some
> built in utility or better algortithm(I don't want to
> compare using SQL,but comparison at the lower level such
> as file etc would do)
> Basically do something like what replication or mirroring
> does but not actually move the data but just check
> Thanks
> Rakesh
Showing posts with label box. Show all posts
Showing posts with label box. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Replication removal - sysarticles
I had replication on a SQL 7 box and then removed it. I think it was not properly removed as while doing an upgrade of an application that uses the database I got a message saying -
[Microsoft][ODBC SQL server Driver][SQL Server] length of text,ntext,image (179948) to be replicated exceeds configured maximum 65536 - 2147217887 Microsoft OLEDB Provider for ODBC Drivers.
I also notice that the sysarticles table has entries in it for the tables in the database.
How may i resolve this problem?
All help appriciated
set max_repl_size to a larger value and try again.
sp_configure 'max text repl size', 200000
"Rohans" <anonymous@.discussions.microsoft.com> wrote in message
news:90B8EDF5-4EA3-4608-B400-591369278080@.microsoft.com...
> I had replication on a SQL 7 box and then removed it. I think it was not
properly removed as while doing an upgrade of an application that uses the
database I got a message saying -
> [Microsoft][ODBC SQL server Driver][SQL Server] length of text,ntext,image
(179948) to be replicated exceeds configured maximum 65536 - 2147217887
Microsoft OLEDB Provider for ODBC Drivers.
> I also notice that the sysarticles table has entries in it for the tables
in the database.
> How may i resolve this problem?
> All help appriciated
[Microsoft][ODBC SQL server Driver][SQL Server] length of text,ntext,image (179948) to be replicated exceeds configured maximum 65536 - 2147217887 Microsoft OLEDB Provider for ODBC Drivers.
I also notice that the sysarticles table has entries in it for the tables in the database.
How may i resolve this problem?
All help appriciated
set max_repl_size to a larger value and try again.
sp_configure 'max text repl size', 200000
"Rohans" <anonymous@.discussions.microsoft.com> wrote in message
news:90B8EDF5-4EA3-4608-B400-591369278080@.microsoft.com...
> I had replication on a SQL 7 box and then removed it. I think it was not
properly removed as while doing an upgrade of an application that uses the
database I got a message saying -
> [Microsoft][ODBC SQL server Driver][SQL Server] length of text,ntext,image
(179948) to be replicated exceeds configured maximum 65536 - 2147217887
Microsoft OLEDB Provider for ODBC Drivers.
> I also notice that the sysarticles table has entries in it for the tables
in the database.
> How may i resolve this problem?
> All help appriciated
Tuesday, March 20, 2012
Replication performance degrade in unidirectional Direction and lock time out (Update are high t
We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.
1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).
2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..
on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.
can any one can also suggest me server level settings for aviding locking time out.
looking for any experieced solution/suggestion.
Thanks in advance.
Hi adrshen,
Need more info. What performance has degraded? You mean user transactions. What was the response time like before and what is it like now.
With merge replication, it would affect the performance as it uses triggers to capture the changes.
regards
Jag
|||plz read my question again .. I just edit it :)|||If it is unidirectional always from publisher to subscriber, then you should look at download_only_articles. This is a special type of setting on an article to indicate that the subscriber will not do DML and it will be more performant. However, note that the performance of the merge agent will increase, but I am not sure if your subscriber itself will start performing better. You can give it a try.Replication performance degrade in unidirectional Direction (Update are high than inserts)
We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.
1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).
2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..
on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.
can any one can also suggest me server level settings for aviding locking time out.
looking for any experieced solution/suggestion.
Thanks in advance.
Hi adrshen,
Need more info. What performance has degraded? You mean user transactions. What was the response time like before and what is it like now.
With merge replication, it would affect the performance as it uses triggers to capture the changes.
regards
Jag
|||plz read my question again .. I just edit it :)|||If it is unidirectional always from publisher to subscriber, then you should look at download_only_articles. This is a special type of setting on an article to indicate that the subscriber will not do DML and it will be more performant. However, note that the performance of the merge agent will increase, but I am not sure if your subscriber itself will start performing better. You can give it a try.Monday, March 12, 2012
replication over a wan
Howdy kids. We are moving our servers to a new location here shortly. One of
the servers is a replicated reporting box. There is an internal ongoing
debate on if we should move this box or not. I have a 25 gig database. The
two buildings are about 40 miles apart from each other. The replication
configuration is as follows:
Transactional.
Remote Distributor. (on the Subscriber)
NOT immediate updating.
Are there any issues with this type of configuration over a WAN? Any gotchas
that wouldn't be had on a local network that could happen here?
SQL2K SP3
TIA, ChrisR
If your subscriber (and consequently your distributor) goes down your tlog
on your publisher will balloon like a balloon. This ballooning tlog will
cause performance problems on the publisher, and can eat up all your space
on the publisher. I would advise you to use a local distributor - preferably
a distributor on the same box as the publisher.
Also you have to check your effective bandwidth of your WAN. Low bandwidth
can cause log reader performance problems leading to ballooning tlogs.
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
"ChrisR" <bla@.noemail.com> wrote in message
news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
> Howdy kids. We are moving our servers to a new location here shortly. One
of
> the servers is a replicated reporting box. There is an internal ongoing
> debate on if we should move this box or not. I have a 25 gig database. The
> two buildings are about 40 miles apart from each other. The replication
> configuration is as follows:
> Transactional.
> Remote Distributor. (on the Subscriber)
> NOT immediate updating.
> Are there any issues with this type of configuration over a WAN? Any
gotchas
> that wouldn't be had on a local network that could happen here?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Thanks Hillary. If I switch to a Local Distributor, should I switch from a
Pull to Push Subscription as well? It would make sense to me, what do you
think?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
> If your subscriber (and consequently your distributor) goes down your tlog
> on your publisher will balloon like a balloon. This ballooning tlog will
> cause performance problems on the publisher, and can eat up all your space
> on the publisher. I would advise you to use a local distributor -
preferably[vbcol=seagreen]
> a distributor on the same box as the publisher.
> Also you have to check your effective bandwidth of your WAN. Low bandwidth
> can cause log reader performance problems leading to ballooning tlogs.
> --
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
One[vbcol=seagreen]
> of
The
> gotchas
>
|||Your choice of push or pull is irrelevant in this case.
Use push when you have small numbers of subscribers, you want a central
point of administration on your publisher, and your subscribers are normally
online and well connected.
Use pull when you have large numbers of subscribers, you don't care about a
central point of administration because you are using replication monitor
group, and your subscribers are not always connected.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <bla@.noemail.com> wrote in message
news:%23PqR59jCFHA.4052@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks Hillary. If I switch to a Local Distributor, should I switch from a
> Pull to Push Subscription as well? It would make sense to me, what do you
> think?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
tlog[vbcol=seagreen]
space[vbcol=seagreen]
> preferably
bandwidth[vbcol=seagreen]
> One
ongoing[vbcol=seagreen]
> The
replication
>
the servers is a replicated reporting box. There is an internal ongoing
debate on if we should move this box or not. I have a 25 gig database. The
two buildings are about 40 miles apart from each other. The replication
configuration is as follows:
Transactional.
Remote Distributor. (on the Subscriber)
NOT immediate updating.
Are there any issues with this type of configuration over a WAN? Any gotchas
that wouldn't be had on a local network that could happen here?
SQL2K SP3
TIA, ChrisR
If your subscriber (and consequently your distributor) goes down your tlog
on your publisher will balloon like a balloon. This ballooning tlog will
cause performance problems on the publisher, and can eat up all your space
on the publisher. I would advise you to use a local distributor - preferably
a distributor on the same box as the publisher.
Also you have to check your effective bandwidth of your WAN. Low bandwidth
can cause log reader performance problems leading to ballooning tlogs.
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
"ChrisR" <bla@.noemail.com> wrote in message
news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
> Howdy kids. We are moving our servers to a new location here shortly. One
of
> the servers is a replicated reporting box. There is an internal ongoing
> debate on if we should move this box or not. I have a 25 gig database. The
> two buildings are about 40 miles apart from each other. The replication
> configuration is as follows:
> Transactional.
> Remote Distributor. (on the Subscriber)
> NOT immediate updating.
> Are there any issues with this type of configuration over a WAN? Any
gotchas
> that wouldn't be had on a local network that could happen here?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Thanks Hillary. If I switch to a Local Distributor, should I switch from a
Pull to Push Subscription as well? It would make sense to me, what do you
think?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
> If your subscriber (and consequently your distributor) goes down your tlog
> on your publisher will balloon like a balloon. This ballooning tlog will
> cause performance problems on the publisher, and can eat up all your space
> on the publisher. I would advise you to use a local distributor -
preferably[vbcol=seagreen]
> a distributor on the same box as the publisher.
> Also you have to check your effective bandwidth of your WAN. Low bandwidth
> can cause log reader performance problems leading to ballooning tlogs.
> --
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
One[vbcol=seagreen]
> of
The
> gotchas
>
|||Your choice of push or pull is irrelevant in this case.
Use push when you have small numbers of subscribers, you want a central
point of administration on your publisher, and your subscribers are normally
online and well connected.
Use pull when you have large numbers of subscribers, you don't care about a
central point of administration because you are using replication monitor
group, and your subscribers are not always connected.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <bla@.noemail.com> wrote in message
news:%23PqR59jCFHA.4052@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks Hillary. If I switch to a Local Distributor, should I switch from a
> Pull to Push Subscription as well? It would make sense to me, what do you
> think?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
tlog[vbcol=seagreen]
space[vbcol=seagreen]
> preferably
bandwidth[vbcol=seagreen]
> One
ongoing[vbcol=seagreen]
> The
replication
>
Wednesday, March 7, 2012
replication monitor is broken
I have setup transactional replication evertything on one box. but
Publications couldn't show up under computer name from replication monitor
(you can see a red X on that computer name). I try many different ways, but
it still doesn't work.
any one know how to fix it !!!
What is the error message you are getting?
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
"FrankLi" <FrankLi@.discussions.microsoft.com> wrote in message
news:5F19F7E4-8D7A-41DB-B187-642FC9A257B3@.microsoft.com...
>I have setup transactional replication evertything on one box. but
> Publications couldn't show up under computer name from replication monitor
> (you can see a red X on that computer name). I try many different ways,
> but
> it still doesn't work.
> any one know how to fix it !!!
>
|||Hi Hilary,
thanks for your response. this is sql2005, and no error message. I can
connect to publications when I try to connect to distributor, but it will
immediately disconnected.
Frank
"Hilary Cotter" wrote:
> What is the error message you are getting?
> --
> 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
>
> "FrankLi" <FrankLi@.discussions.microsoft.com> wrote in message
> news:5F19F7E4-8D7A-41DB-B187-642FC9A257B3@.microsoft.com...
>
>
Publications couldn't show up under computer name from replication monitor
(you can see a red X on that computer name). I try many different ways, but
it still doesn't work.
any one know how to fix it !!!
What is the error message you are getting?
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
"FrankLi" <FrankLi@.discussions.microsoft.com> wrote in message
news:5F19F7E4-8D7A-41DB-B187-642FC9A257B3@.microsoft.com...
>I have setup transactional replication evertything on one box. but
> Publications couldn't show up under computer name from replication monitor
> (you can see a red X on that computer name). I try many different ways,
> but
> it still doesn't work.
> any one know how to fix it !!!
>
|||Hi Hilary,
thanks for your response. this is sql2005, and no error message. I can
connect to publications when I try to connect to distributor, but it will
immediately disconnected.
Frank
"Hilary Cotter" wrote:
> What is the error message you are getting?
> --
> 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
>
> "FrankLi" <FrankLi@.discussions.microsoft.com> wrote in message
> news:5F19F7E4-8D7A-41DB-B187-642FC9A257B3@.microsoft.com...
>
>
Replication Monitor disappears
All,
I have setup transactional replication in our dev environment with
publisher, distributor and subscriber on same box for testing and it was
running fine for some time. When I was setting up replication I had SQL
Service logged on using local system account and sql agent login was dev
admin account.
Yesterday when we have restarted the server, sql service came fine but SQL
Agent did not start. when I tried to start agent it manually it errored
saying login donot have permissions.
When I have checked Agent login, its using same dev admin login which should
have all permissions. Finally I was able to start the SQL Agent service by
retyping dev admin and password from services.
This time though SQL Agent started Replication monitor disappeared from
Enterprise manager. Any suggesstions why it happened..?
This could be a rights issue. Connect to the problem server using the sa
account and see if it shows up.
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
"SQL Replicatio Guy" <SQL Replicatio Guy@.discussions.microsoft.com> wrote in
message news:F6019804-2776-4532-836D-C51156354A45@.microsoft.com...
> All,
> I have setup transactional replication in our dev environment with
> publisher, distributor and subscriber on same box for testing and it was
> running fine for some time. When I was setting up replication I had SQL
> Service logged on using local system account and sql agent login was dev
> admin account.
> Yesterday when we have restarted the server, sql service came fine but SQL
> Agent did not start. when I tried to start agent it manually it errored
> saying login donot have permissions.
> When I have checked Agent login, its using same dev admin login which
should
> have all permissions. Finally I was able to start the SQL Agent service by
> retyping dev admin and password from services.
> This time though SQL Agent started Replication monitor disappeared from
> Enterprise manager. Any suggesstions why it happened..?
>
>
|||Yes, I am sure its rights issue but not sure why my replication monitor did
not come up after I have started the SQL Agent service with dev admin account.
FYI If it helps-
SQL Agent started only when I have retyped dev admin and password.
Regards
"Hilary Cotter" wrote:
> This could be a rights issue. Connect to the problem server using the sa
> account and see if it shows up.
> --
> 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
> "SQL Replicatio Guy" <SQL Replicatio Guy@.discussions.microsoft.com> wrote in
> message news:F6019804-2776-4532-836D-C51156354A45@.microsoft.com...
> should
>
>
I have setup transactional replication in our dev environment with
publisher, distributor and subscriber on same box for testing and it was
running fine for some time. When I was setting up replication I had SQL
Service logged on using local system account and sql agent login was dev
admin account.
Yesterday when we have restarted the server, sql service came fine but SQL
Agent did not start. when I tried to start agent it manually it errored
saying login donot have permissions.
When I have checked Agent login, its using same dev admin login which should
have all permissions. Finally I was able to start the SQL Agent service by
retyping dev admin and password from services.
This time though SQL Agent started Replication monitor disappeared from
Enterprise manager. Any suggesstions why it happened..?
This could be a rights issue. Connect to the problem server using the sa
account and see if it shows up.
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
"SQL Replicatio Guy" <SQL Replicatio Guy@.discussions.microsoft.com> wrote in
message news:F6019804-2776-4532-836D-C51156354A45@.microsoft.com...
> All,
> I have setup transactional replication in our dev environment with
> publisher, distributor and subscriber on same box for testing and it was
> running fine for some time. When I was setting up replication I had SQL
> Service logged on using local system account and sql agent login was dev
> admin account.
> Yesterday when we have restarted the server, sql service came fine but SQL
> Agent did not start. when I tried to start agent it manually it errored
> saying login donot have permissions.
> When I have checked Agent login, its using same dev admin login which
should
> have all permissions. Finally I was able to start the SQL Agent service by
> retyping dev admin and password from services.
> This time though SQL Agent started Replication monitor disappeared from
> Enterprise manager. Any suggesstions why it happened..?
>
>
|||Yes, I am sure its rights issue but not sure why my replication monitor did
not come up after I have started the SQL Agent service with dev admin account.
FYI If it helps-
SQL Agent started only when I have retyped dev admin and password.
Regards
"Hilary Cotter" wrote:
> This could be a rights issue. Connect to the problem server using the sa
> account and see if it shows up.
> --
> 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
> "SQL Replicatio Guy" <SQL Replicatio Guy@.discussions.microsoft.com> wrote in
> message news:F6019804-2776-4532-836D-C51156354A45@.microsoft.com...
> should
>
>
Subscribe to:
Posts (Atom)