Hi,
I want to replicate all Updates, Inserts and Deletes (possibly indexed
view changes) to a whole database from sql server2000 (live server) to
sql server2005 (reporting server), but I only want to do it every
night to at an interval that I choose. (Note I don't want to replicate
the whole database each time, just initially).
Is it easy to use replication between sql Server 2000
(publisher/distributor) and sql server 2005 (Subscriber)?
I want to use Merge replication, mainly because I can set it to only be
scheduled once a night (as opposed to Transactional where changes would
be sent for every complete transaction)... not interested in merging
any data, just sending all the changes across.
Are there any issues with this idea?
If this is the right way to do it? Or should I be using Data
Transformation Services?
If I am to use Data Transformation services, is there a way to
schedule/automate this task on both sides?
- The Bermused<tech101@.gmail.com> wrote in message
news:1149812382.243609.307830@.i40g2000cwc.googlegroups.com...
> Hi,
> I want to replicate all Updates, Inserts and Deletes (possibly indexed
> view changes) to a whole database from sql server2000 (live server) to
> sql server2005 (reporting server), but I only want to do it every
> night to at an interval that I choose. (Note I don't want to replicate
> the whole database each time, just initially).
> Is it easy to use replication between sql Server 2000
> (publisher/distributor) and sql server 2005 (Subscriber)?
I don't think you'll have issues here.
I wouldn't use Merge. I'd use transactional, but set it up to run
non-continously.
Another option btw, is to log-ship. This may ni fact work even better in
this case if you're not doing reports at night.
> I want to use Merge replication, mainly because I can set it to only be
> scheduled once a night (as opposed to Transactional where changes would
> be sent for every complete transaction)... not interested in merging
> any data, just sending all the changes across.
> Are there any issues with this idea?
> If this is the right way to do it? Or should I be using Data
> Transformation Services?
> If I am to use Data Transformation services, is there a way to
> schedule/automate this task on both sides?
> - The Bermused
>|||Thanks Greg.
I think that the transacitonal solution is a good way to go. With the
distributor being on the same box as the publisher initially, and when
we have more resources then move the distributor to a box in the same
network/domain as the publisher.
Have tried setting this replication up, but run into authentication
problems.
I think the problem is that the Publishing/Distributor sqlserver2000
(Prod/Live) machine is in a different domain to the subscribing
sqlserver2005 (Reporting) machine. Also there is no trust set up
between the domains, so an AD account will not work.
I have heard that impersonation may work in that:
* an agent that SQL Server2005(Subscriber) uses (one of many found in
Services) can run under a specified system account
* I supply the credentials of this account during the (transactional)
replication wizard on the SQL Server2000(Publisher)
This may work but, I am not sure which agent/service to apply the
account to - and I don't want to break things!
By default, services run under a local system account... not sure which
account is default though...
I can set up an SqlServer service to run under a particular account,
however, as mentioned, I am not sure which service to use.
Does this style of solution sound like it is the right way to go?
Also, would log shipping be a less technical/problematic solution for
this? Would it consume less resources on the live machine? And would
it be an incremental type solution that would not clog up the network?
Cheers,
Adam
Greg D. Moore (Strider) wrote:
> <tech101@.gmail.com> wrote in message
> news:1149812382.243609.307830@.i40g2000cwc.googlegroups.com...
> > Hi,
> > I want to replicate all Updates, Inserts and Deletes (possibly indexed
> > view changes) to a whole database from sql server2000 (live server) to
> > sql server2005 (reporting server), but I only want to do it every
> > night to at an interval that I choose. (Note I don't want to replicate
> > the whole database each time, just initially).
> > Is it easy to use replication between sql Server 2000
> > (publisher/distributor) and sql server 2005 (Subscriber)?
> I don't think you'll have issues here.
> I wouldn't use Merge. I'd use transactional, but set it up to run
> non-continously.
> Another option btw, is to log-ship. This may ni fact work even better in
> this case if you're not doing reports at night.
>
> > I want to use Merge replication, mainly because I can set it to only be
> > scheduled once a night (as opposed to Transactional where changes would
> > be sent for every complete transaction)... not interested in merging
> > any data, just sending all the changes across.
> >
> > Are there any issues with this idea?
> > If this is the right way to do it? Or should I be using Data
> > Transformation Services?
> >
> > If I am to use Data Transformation services, is there a way to
> > schedule/automate this task on both sides?
> >
> > - The Bermused
> >
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Wednesday, March 28, 2012
Friday, March 9, 2012
replication of views that reference other views
hi,
in a merge replication I use a view (V1) that references another view (V2)
in its select statement.
when running the merge agent to initialize the subscriber I get this error:
The schema script '\\SSS-SERVER\ReplData\unc\SSS-
SERVER_E5K_714_FS_E5K_714_FS_Replikat\200404072145
58\V1_667.sch' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
Unable to replicate a view or function because the referenced objects or
columns are not present on the Subscriber.
(Source: SSS1 (Agent); Error number: 0)
Invalid object name 'dbo.V2'.
(Source: SSS1 (Data source); Error number: 208)
I think the error occurs, because V2 is not yet present on the subscriber
when the agent tries to create V1. (when i check the views created on the
subscriber after the agent fails, V2 is missing)
so how can I tell the agent the order of creating objects?
or is there any other solution?
thanks for your help
thomas
Thomas,
your dependencies are out of sync. Run sp_refreshview on the 2 views then
they'll be replicates in the correct order.
Regards,
Paul Ibison
|||thanks paul,
i tried sp_refreshview on all my views, but i still have the same problem.
thomas
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OAYVRyjHEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Thomas,
> your dependencies are out of sync. Run sp_refreshview on the 2 views then
> they'll be replicates in the correct order.
> Regards,
> Paul Ibison
>
|||use a presnapshot script that precreates the views on the subscriber.
"Thomas Schnauer" <thomas@.schoenauer.at> wrote in message
news:4076c17c$0$30734$91cee783@.newsreader02.highwa y.telekom.at...
> thanks paul,
> i tried sp_refreshview on all my views, but i still have the same problem.
> thomas
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:OAYVRyjHEHA.1944@.TK2MSFTNGP11.phx.gbl...
then
>
|||Thomas,
Although my reply may not apply to you, I am posting some documentation that
may help others.
Contrary to table articles, order of views in publication is seen to be
controlled differently. Maybe this behaviour has been fixed in a later
service pack or hotfix that I am not aware of, so sorry if that is the case.
Unless it is too late or at all feasible, try this workaround:
1. Create a publication for the views.
2. Add one view and generate a snapshot (this step is key to successful
implementation of this workaround).
3. Continue step 2 until all the views have been added. The order in which
you add your views will determine the order in which the snapshot will apply
them to the subscribers.
4. Apply the snapshot. This should work fine now.
Now, the tricky part here is that if you have to change your views and
dependencies change, you might be in trouble again. For example, View2
depends on View1. You add View1, gen snapshot, add View2, gen snapshot and
finally apply snapshot. Later you create View3 and change View2 to include
reference to View3. If you now add View3 to the publication and reinitialise
your subscriptions, the snapshot will try to apply View1, View2 and View3 in
that order instead of the order View1, View3, View2 as required. Obviously,
the agent will fail with the same message you have posted because View3 does
not exist at the subscriber yet and View2 is being applied.
So, the bottom line is: do not use publications with views as articles if
you can have the above scenario. But if your design is not going to change
at all (very unlikely?), then go ahead with the above workaround.
Alternatively, use pre-snapshot scripts, as also suggested by Hilary. Of
course, the scripts should contain the views in the proper order.
Hope the above helps,
Raj Moloye
|||Thomas,
the order of running it is crucial - run sp_refreshview on the simple view
then run it on the view that depends on it. This dependency should show up
using sp_depends. Provided the dependency is recognised, then the order of
application in the snapshot should be correct. I have run this successfully
before. However, if it doesn't work then Hilary's advice (postscripts) or
sp_addscriptexec will do the same thing.
Regards,
Paul Ibison
|||views can also be replicated to all subscribers using sp_addscriptexec
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eMlshkkHEHA.3576@.TK2MSFTNGP10.phx.gbl...
> use a presnapshot script that precreates the views on the subscriber.
> "Thomas Schnauer" <thomas@.schoenauer.at> wrote in message
> news:4076c17c$0$30734$91cee783@.newsreader02.highwa y.telekom.at...
problem.
> then
>
|||Paul,
you were right. I re-ordered my script so that the simple views were
refreshed first - now replication works fine.
thanks to all of you for your support
Thomas
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OH$2dhlHEHA.3840@.TK2MSFTNGP11.phx.gbl...
> Thomas,
> the order of running it is crucial - run sp_refreshview on the simple view
> then run it on the view that depends on it. This dependency should show up
> using sp_depends. Provided the dependency is recognised, then the order of
> application in the snapshot should be correct. I have run this
successfully
> before. However, if it doesn't work then Hilary's advice (postscripts) or
> sp_addscriptexec will do the same thing.
> Regards,
> Paul Ibison
>
in a merge replication I use a view (V1) that references another view (V2)
in its select statement.
when running the merge agent to initialize the subscriber I get this error:
The schema script '\\SSS-SERVER\ReplData\unc\SSS-
SERVER_E5K_714_FS_E5K_714_FS_Replikat\200404072145
58\V1_667.sch' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
Unable to replicate a view or function because the referenced objects or
columns are not present on the Subscriber.
(Source: SSS1 (Agent); Error number: 0)
Invalid object name 'dbo.V2'.
(Source: SSS1 (Data source); Error number: 208)
I think the error occurs, because V2 is not yet present on the subscriber
when the agent tries to create V1. (when i check the views created on the
subscriber after the agent fails, V2 is missing)
so how can I tell the agent the order of creating objects?
or is there any other solution?
thanks for your help
thomas
Thomas,
your dependencies are out of sync. Run sp_refreshview on the 2 views then
they'll be replicates in the correct order.
Regards,
Paul Ibison
|||thanks paul,
i tried sp_refreshview on all my views, but i still have the same problem.
thomas
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OAYVRyjHEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Thomas,
> your dependencies are out of sync. Run sp_refreshview on the 2 views then
> they'll be replicates in the correct order.
> Regards,
> Paul Ibison
>
|||use a presnapshot script that precreates the views on the subscriber.
"Thomas Schnauer" <thomas@.schoenauer.at> wrote in message
news:4076c17c$0$30734$91cee783@.newsreader02.highwa y.telekom.at...
> thanks paul,
> i tried sp_refreshview on all my views, but i still have the same problem.
> thomas
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:OAYVRyjHEHA.1944@.TK2MSFTNGP11.phx.gbl...
then
>
|||Thomas,
Although my reply may not apply to you, I am posting some documentation that
may help others.
Contrary to table articles, order of views in publication is seen to be
controlled differently. Maybe this behaviour has been fixed in a later
service pack or hotfix that I am not aware of, so sorry if that is the case.
Unless it is too late or at all feasible, try this workaround:
1. Create a publication for the views.
2. Add one view and generate a snapshot (this step is key to successful
implementation of this workaround).
3. Continue step 2 until all the views have been added. The order in which
you add your views will determine the order in which the snapshot will apply
them to the subscribers.
4. Apply the snapshot. This should work fine now.
Now, the tricky part here is that if you have to change your views and
dependencies change, you might be in trouble again. For example, View2
depends on View1. You add View1, gen snapshot, add View2, gen snapshot and
finally apply snapshot. Later you create View3 and change View2 to include
reference to View3. If you now add View3 to the publication and reinitialise
your subscriptions, the snapshot will try to apply View1, View2 and View3 in
that order instead of the order View1, View3, View2 as required. Obviously,
the agent will fail with the same message you have posted because View3 does
not exist at the subscriber yet and View2 is being applied.
So, the bottom line is: do not use publications with views as articles if
you can have the above scenario. But if your design is not going to change
at all (very unlikely?), then go ahead with the above workaround.
Alternatively, use pre-snapshot scripts, as also suggested by Hilary. Of
course, the scripts should contain the views in the proper order.
Hope the above helps,
Raj Moloye
|||Thomas,
the order of running it is crucial - run sp_refreshview on the simple view
then run it on the view that depends on it. This dependency should show up
using sp_depends. Provided the dependency is recognised, then the order of
application in the snapshot should be correct. I have run this successfully
before. However, if it doesn't work then Hilary's advice (postscripts) or
sp_addscriptexec will do the same thing.
Regards,
Paul Ibison
|||views can also be replicated to all subscribers using sp_addscriptexec
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eMlshkkHEHA.3576@.TK2MSFTNGP10.phx.gbl...
> use a presnapshot script that precreates the views on the subscriber.
> "Thomas Schnauer" <thomas@.schoenauer.at> wrote in message
> news:4076c17c$0$30734$91cee783@.newsreader02.highwa y.telekom.at...
problem.
> then
>
|||Paul,
you were right. I re-ordered my script so that the simple views were
refreshed first - now replication works fine.
thanks to all of you for your support
Thomas
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OH$2dhlHEHA.3840@.TK2MSFTNGP11.phx.gbl...
> Thomas,
> the order of running it is crucial - run sp_refreshview on the simple view
> then run it on the view that depends on it. This dependency should show up
> using sp_depends. Provided the dependency is recognised, then the order of
> application in the snapshot should be correct. I have run this
successfully
> before. However, if it doesn't work then Hilary's advice (postscripts) or
> sp_addscriptexec will do the same thing.
> Regards,
> Paul Ibison
>
replication of a view
Is it possible to replicate a view, and have it published to a table instead of a view?I am not sure exactly what you mean.
Do you want the records in the view to be appended to an existing table or do you want to create a new table?
TO append use INSERT INTO
To create a new table from the view:
Inserting Rows Using SELECT INTO
The SELECT INTO statement creates a new table and populates it with the result set of the SELECT. The structure of the new table is defined by the attributes of the expressions in the select list, for example:
SELECT Shippers.*, Link.Address, Link.City,
Link.Region, Link.PostalCode
INTO NewShippers
FROM Shippers
JOIN LinkServer.DB.dbo.Shippers AS Link
ON (Shippers.ShipperID = Link.ShipperID)
SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table containing data selected from a linked server.
Do you want the records in the view to be appended to an existing table or do you want to create a new table?
TO append use INSERT INTO
To create a new table from the view:
Inserting Rows Using SELECT INTO
The SELECT INTO statement creates a new table and populates it with the result set of the SELECT. The structure of the new table is defined by the attributes of the expressions in the select list, for example:
SELECT Shippers.*, Link.Address, Link.City,
Link.Region, Link.PostalCode
INTO NewShippers
FROM Shippers
JOIN LinkServer.DB.dbo.Shippers AS Link
ON (Shippers.ShipperID = Link.ShipperID)
SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table containing data selected from a linked server.
Wednesday, March 7, 2012
Replication Montor 2005
Is there any way to configure repl. monitor in 2005 to view all the publications that go
via a distributor in one place? (as opposed to having to link to each publisher to see the replictaion status).
we have 7 publishers going throigh one distributor and in 2000 could see them all on the distributor...now have to connect to each publisher & look on each...
Thanks
DesX
This is a design change in SQL 2005 (largely based on customer feedback), and there's no way to accomplish what you want with the given tools.
However if you want to build your own tool, you can. SQL Monitor is built around proc sp_replmonitorpublication, which has parameter @.publisher. If NULL, you get everything back for the given distributor.
replication monitor group
Hi,
I am part of the replmonitor role of a distribution
database. I am able to view the agents of the distribution
database but i am unable to start or stop any agent. the
books online says that i should be able to. Reference
Replication/administering and monitoring replication/Tools
for administering and monitoring replication/Replication
Monitor: "You can use Replication Monitor
to: ....Administer agents and subscriptions including
starting and stopping agents and reinitializing
subscriptions".
what did i miss?
Thankx for any help
do a search in BOL for replmonitor and look in Role Requirements. It states that this role can only view. It further states that you can't change anything unless you are also a member of the sysadmin role.
Likewise you can't start or stop agents unless you are a member of the sysadmin role, although this is left unstated in BOL.
In the quote you gave, it does say you can stop and start replication agents using replication monitor, but what is left unsaid is that you need the correct permissions to do so. If you do search on BOL for replmonitor and look in the Replication Monitor
section it does say however "however, the user will not ba ale to administer replication." Starting and stopping agents falls under the umbrella of administering replication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"mona" wrote:
> Hi,
> I am part of the replmonitor role of a distribution
> database. I am able to view the agents of the distribution
> database but i am unable to start or stop any agent. the
> books online says that i should be able to. Reference
> Replication/administering and monitoring replication/Tools
> for administering and monitoring replication/Replication
> Monitor: "You can use Replication Monitor
> to: ....Administer agents and subscriptions including
> starting and stopping agents and reinitializing
> subscriptions".
> what did i miss?
> Thankx for any help
>
I am part of the replmonitor role of a distribution
database. I am able to view the agents of the distribution
database but i am unable to start or stop any agent. the
books online says that i should be able to. Reference
Replication/administering and monitoring replication/Tools
for administering and monitoring replication/Replication
Monitor: "You can use Replication Monitor
to: ....Administer agents and subscriptions including
starting and stopping agents and reinitializing
subscriptions".
what did i miss?
Thankx for any help
do a search in BOL for replmonitor and look in Role Requirements. It states that this role can only view. It further states that you can't change anything unless you are also a member of the sysadmin role.
Likewise you can't start or stop agents unless you are a member of the sysadmin role, although this is left unstated in BOL.
In the quote you gave, it does say you can stop and start replication agents using replication monitor, but what is left unsaid is that you need the correct permissions to do so. If you do search on BOL for replmonitor and look in the Replication Monitor
section it does say however "however, the user will not ba ale to administer replication." Starting and stopping agents falls under the umbrella of administering replication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"mona" wrote:
> Hi,
> I am part of the replmonitor role of a distribution
> database. I am able to view the agents of the distribution
> database but i am unable to start or stop any agent. the
> books online says that i should be able to. Reference
> Replication/administering and monitoring replication/Tools
> for administering and monitoring replication/Replication
> Monitor: "You can use Replication Monitor
> to: ....Administer agents and subscriptions including
> starting and stopping agents and reinitializing
> subscriptions".
> what did i miss?
> Thankx for any help
>
Replication Monitor
Hi,
I need to create a replica of a database. I dont have the replication
monitor. But under tools-->replication--> View replication monitor group .
Can i create a replica db from this monitor Group?
Thanks
pmud
hi,
i got the solution .. The server was not configured for replication. i went
to tolld and then configure publisher, subscribers and distributors.. and
thenw alked through the wizard.. on completion of the wizard.. it showed up
the replication monitor.
Thanks
pmud
"pmud" wrote:
> Hi,
> I need to create a replica of a database. I dont have the replication
> monitor. But under tools-->replication--> View replication monitor group .
> Can i create a replica db from this monitor Group?
> Thanks
> --
> pmud
I need to create a replica of a database. I dont have the replication
monitor. But under tools-->replication--> View replication monitor group .
Can i create a replica db from this monitor Group?
Thanks
pmud
hi,
i got the solution .. The server was not configured for replication. i went
to tolld and then configure publisher, subscribers and distributors.. and
thenw alked through the wizard.. on completion of the wizard.. it showed up
the replication monitor.
Thanks
pmud
"pmud" wrote:
> Hi,
> I need to create a replica of a database. I dont have the replication
> monitor. But under tools-->replication--> View replication monitor group .
> Can i create a replica db from this monitor Group?
> Thanks
> --
> pmud
Tuesday, February 21, 2012
Replication is very Slow
When we try to replicate the database from one server to another server it is very very slow...
Then we assumed that the Indexed View may cause this issue.. But we didn't check by removing the index & replicated it..
Can any one suggest me for the following Qs..
Is it Indexed View really bad on Replication(Main Table)?
What is the alternate & best solution for Indexed Views?
Is Covered Index solve the Indexed View issue?
Is Convered Index wont create the Replication issue?
Is it good idea to disable the Index on View on Replication and re-create the index?
Pls guide me on this issue..
We are Using SQL SERVER 2000 Enterprise Edition
Modifying Indexed views are slow in general, and not necessarily a replication issue. When modifying indexed views, you're basically doing two writes for every operation - one for the view, one for the underlying tables, plus double the index maintenance.
You can read some basic information about replication and indexed views here - http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx.
You didn't say exactly what is slow, so what you should do is compare performance impact of ins/upd/del statements to the indexed view at the publisher database, with and without replication.
Subscribe to:
Comments (Atom)