Replication and Reports

Hi!
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.
In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.

Replication and Reports

Hi!
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.

Replication and Reports

Hi!
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.

Replication

Hi everybody.
I am working on merge replication first time and I ran a
synchronizing processing and get error message:
Cannot insert the value NULL into column 'objid',
table 'ReplicationDB_1.dbo.sysmergeschemaarticles';
column does not allow nulls. INSERT fails.
My snapshots has created successfully.
Any suggestions or advices?
Thank you Mike.
I believe this is a bug. Contact PSS for a hot fix.
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:8bbf01c432aa$26749460$a001280a@.phx.gbl...
> Hi everybody.
> I am working on merge replication first time and I ran a
> synchronizing processing and get error message:
> Cannot insert the value NULL into column 'objid',
> table 'ReplicationDB_1.dbo.sysmergeschemaarticles';
> column does not allow nulls. INSERT fails.
> My snapshots has created successfully.
> Any suggestions or advices?
> Thank you Mike.
>
>

Replicating structure only not data

I have a SQL 2005 database that I am using with a website. This basic website will be sold to other companies and ran on their servers with different URLs. Since, All of these databases will store different data, I'm not sure how I can make updates to original database and replicate those structure changes to the other DBs without changing the data also. Is there a way to automate the replication of structural DB changes without replicating the data along with it?

Thanks,

Kirk

You can enable @.replicate_dll at publication level, which will push the schema change to your subscriber. And you can set all the ins/upd/del commands to NONE for all of your articles, which will ignore all the data changes that took places on the publisher.

Gary

|||

Thanks Gary,

I am not new to SQL 2005 but I have never done any replication. Could you lead me to a good reference that would explain how to do some of the things that you wrote about? Any help is greatly appreciated.

Kirk

|||

I think the following would be a good start

General info on transactional replication

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

Creating create publication - by default @.replicate_ddl is on, so it will replcate ddl (aka - schema changes)

http://msdn2.microsoft.com/en-gb/library/ms147855.aspx
http://msdn2.microsoft.com/en-gb/library/ms188738.aspx

Create article - you want to set @.ins_cmd='NONE', @.upd_cmd='NONE', @.del_cmd='NONE'

http://msdn2.microsoft.com/en-gb/library/ms173857.aspx

Regards,

Gary

|||Thanks Gary, I will take a look at those links.