Gurus:
<<Running SQL2K SP4 Standard on WIN2K>>
Have a DTS which scripts Replication from 1 domain to another...creates EVERYTHING on the fly (Distributor, Dist. DB, Subscription DB, ALL Jobs, etc.)
QUESTION - Need to replicate the SAME DB AGAIN (after data modifications) but need to find a flag/tbl entry saying Replication was SUCCESSFUL.
ThanksHi All:
Anyone have an idea about this one for me?
"Something - Doo Economics ...anyone...anyone..."|||"SQL2K SP4"? I don't think that exists; they are only up to SP3a.
You are looking for an internal table that indicates a replication success? What type of replication are you using?
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
DTS Package Error in the Transactional Replication
We have a DTS package doing some transformation in Trasactional Replication.
Now, I want to add a new table as a part of the replication without shutting
down replication. I can add it through sp_addaarticle and sp_addsubscription.
However the DTS can't be modified as one of the component in DTS is not
available in DTS designer.
This is the package that SQL Server creates automatically for you when you
specify DTS as a media for data transformation. Since, I dont have this
table's data driven task in DTS, I am getting following error in Distribution
DB.
Cannot find the Data Driven Query task in the DTS package that matches
command.
Thank you very much.
create a new publication for this article and add it.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:F9F5749E-7B78-4398-A23F-DEE4FB9E74C2@.microsoft.com...
> We have a DTS package doing some transformation in Trasactional
> Replication.
> Now, I want to add a new table as a part of the replication without
> shutting
> down replication. I can add it through sp_addaarticle and
> sp_addsubscription.
> However the DTS can't be modified as one of the component in DTS is not
> available in DTS designer.
> This is the package that SQL Server creates automatically for you when you
> specify DTS as a media for data transformation. Since, I dont have this
> table's data driven task in DTS, I am getting following error in
> Distribution
> DB.
> Cannot find the Data Driven Query task in the DTS package that matches
> command.
> Thank you very much.
>
Now, I want to add a new table as a part of the replication without shutting
down replication. I can add it through sp_addaarticle and sp_addsubscription.
However the DTS can't be modified as one of the component in DTS is not
available in DTS designer.
This is the package that SQL Server creates automatically for you when you
specify DTS as a media for data transformation. Since, I dont have this
table's data driven task in DTS, I am getting following error in Distribution
DB.
Cannot find the Data Driven Query task in the DTS package that matches
command.
Thank you very much.
create a new publication for this article and add it.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:F9F5749E-7B78-4398-A23F-DEE4FB9E74C2@.microsoft.com...
> We have a DTS package doing some transformation in Trasactional
> Replication.
> Now, I want to add a new table as a part of the replication without
> shutting
> down replication. I can add it through sp_addaarticle and
> sp_addsubscription.
> However the DTS can't be modified as one of the component in DTS is not
> available in DTS designer.
> This is the package that SQL Server creates automatically for you when you
> specify DTS as a media for data transformation. Since, I dont have this
> table's data driven task in DTS, I am getting following error in
> Distribution
> DB.
> Cannot find the Data Driven Query task in the DTS package that matches
> command.
> Thank you very much.
>
Wednesday, March 7, 2012
DTS or Replication??
I have different heterogeneous databases (SQL Server as well as Oracle) which need to transfer their data to a central SQL Server database for reporting purposes. Central location needs to be kept up-to-date on a periodic basis, say every night we consoli
date data from different sources. There can be maximum 100 such source databases. Each source can have max 100KB of data.
Now I have to figure out which one is more suitable to my needs: replication or DTS? I am new to both of these approaches. I don't know whether DTS would even make a solution or not? So please advise what would be the better approach. Also I would appreci
ate further links/references that would help me move ahead with either of them.
Thanks
-megha
Megha,
if you select DTS , you'll have potentially a huge task ascertaining which
are the new records, which have been deleted and which have been updated.
So, using DTS practically limits you (in most circumstances) to taking the
complete table from each subscriber. Replication, on the other hand, has a
methodology which takes all this into account. I would recommend merge
replication with your reporting server as a central publisher. You need to
consider if the data is to be partitioned according to subscriber, or if
conflicts can arise and how to treat these conflicts, for which there are
several inbuilt resolution techniques. Have a look at BOL for further
information. I maintain a simple site (www.replicationanswers.com) and on
the links section there are some configuration walkthroughs. Unfortunately
there aren't yet any books to recommend, but keep an eye out for the
imminent release of Hilary Cotter's book.
HTH,
Paul Ibison
|||Unfortunately with SQL 2000 you can't replicate from Oracle to SQL Server
AFAIK. I have gone through Oracle's documentation and Oracle 8 and 9 and
don't see this option.
DTS sounds like the best option, although tracking changes (as Paul
mentions) will be difficult.
I did set something up in the past that did bulk inserts from Oracle to SQL
Server using vbscript which would so an insert select of the form
insert into SQLServer.Database.dbo.tableName
select * from OracleServer.Database.ServerName.SYSDBA.TableName where
PKOracle not in (select PKSQLServer SQLServer.dbo.tablename)
It might be simpler to use a linked server for this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"megha" <anonymous@.discussions.microsoft.com> wrote in message
news:A4C68146-4B9D-4136-8464-9E5271402E64@.microsoft.com...
> I have different heterogeneous databases (SQL Server as well as Oracle)
which need to transfer their data to a central SQL Server database for
reporting purposes. Central location needs to be kept up-to-date on a
periodic basis, say every night we consolidate data from different sources.
There can be maximum 100 such source databases. Each source can have max
100KB of data.
> Now I have to figure out which one is more suitable to my needs:
replication or DTS? I am new to both of these approaches. I don't know
whether DTS would even make a solution or not? So please advise what would
be the better approach. Also I would appreciate further links/references
that would help me move ahead with either of them.
> Thanks
> -megha
|||Thanks Paul for the feedback! I checked out your website and found that you work a lot with ASP.NET as well. I would like an advise here. How would web services fit in to my requirements here? Is there a way I can capture transactions on heterogenous data
base servers and then use web services to send them to a central server? Or is it better to go ahead with replication?
One more question: you suggested merge replication with central publisher. Why central publisher - I thought it would be central subscriber as I have many publishers and all data would need to come to one central server. Also, once arrived at central serv
er data might change but I don't want this data to propogate back to the publishers. Any changes coming from a publisher would overwrite the data at central location. Do I need to use merge replication or transactional replication is the solution? Please
explain.
|||Thank you Hilary for sharing your knowledge with me! I checked out the sample chapter of your upcoming book. There I have got a question for you. Are you saying that for SQL Server (any version) replication we can not have heterogeneous subscribers? I gue
ss thats correct and thats the reason you have mentioned DTS and Linked Servers.
But I did not quite follow the direction you pointed to. How can I use a linked server to an Oracle database that would simulate Oracle - SQL Server replication? Or how would DTS do that? OK I can use either of the two to access Oracle database but how do
I create/access the transaction log which I essentially need at central SQL Server - can you please explain more?
|||Megha,
you'll need to use replication for the SQL subscribers and DTS/linked
servers for Oracle. The data on the central reporting server can be
amalgamated into one table or more easily unioned using views. If you use
views and want to edit the data, you can partition them to enable edits or
use InsteadOf triggers. Webservices would only complicate things here, and
it'll be quite complex as it is :-)
The publisher/subscriber metaphor doesn't strictly apply to merge, and
centralized publishing is easier to manage/maintain.
You can implement unidirectional merge replication using the -EXCHANGETYPE
parameter. Transactional is possible, but merge is much simpler for your
needs.
HTH,
Paul Ibison
|||I had a mistake in my first question. I intend to ask - Are you saying that for SQL Server (any version) replication we can not have heterogeneous publishers?
|||Paul, I am still trying to understand: why central publishing would match my needs better. How I think about it is I do not need any data to flow from central location to different sources and so I would not need to publish anything on central side. Do I
understand it correct that whoever is changing the data can become a publisher and as central location's change of data do not need to go across it should not be a publisher? Am I missing on something here?
How unidirectional merge is simpler than transactional replication? Can you provide some facts? What I knew is that merge brings in additional complications to the process. I would like to know your reasons as I will be implementing it for the first time
and I do not have any convenience preferences so far.
You must have some insights into this, so please explain further.
|||Megha,
this is just my preference purely from the point of view of maintenance.
Remember, the publisher/subscriber metaphor doesn't strictly apply to merge,
meaning that either a publisher or a subscriber can change the data -
there's really no great distinction in this respect. There are some
differences eg the publisher wins conflicts by default, but in your case
this is probably not relevant.
If each remote computer is set up as a transactional publisher, this can be
a lot of maintenance. Each publisher typically is a publisher/distributer.
Each one has it's own set of alerts and jobs. All profile parameters
(timeouts, snapshot size etc), publication settings (duration of
transactions etc) are set on each remote machine separately. Also, backup
and restore for disaster recovery is also much more straightforward for
merge replication.
Regards,
Paul Ibison
|||OK now I can select central publishing topology for replication with unidirectional replication flowing from subscribers/publishers to central publisher. BUT even then I need to keep track of changes at subscribers/publishers so that it can bemerged at ce
ntral location. Won't I need then to define a distributor for each of these subscribers/publishers then? How can I get away with defining no publication settings (transaction duration as you quoted) on each one of them?
Can you explain it more or tell me if there is some book I can refer to in order to understand the merge replication more clearly? BOL does not detail on central publishing with unidirectional replication.
Thanks for your help!
date data from different sources. There can be maximum 100 such source databases. Each source can have max 100KB of data.
Now I have to figure out which one is more suitable to my needs: replication or DTS? I am new to both of these approaches. I don't know whether DTS would even make a solution or not? So please advise what would be the better approach. Also I would appreci
ate further links/references that would help me move ahead with either of them.
Thanks
-megha
Megha,
if you select DTS , you'll have potentially a huge task ascertaining which
are the new records, which have been deleted and which have been updated.
So, using DTS practically limits you (in most circumstances) to taking the
complete table from each subscriber. Replication, on the other hand, has a
methodology which takes all this into account. I would recommend merge
replication with your reporting server as a central publisher. You need to
consider if the data is to be partitioned according to subscriber, or if
conflicts can arise and how to treat these conflicts, for which there are
several inbuilt resolution techniques. Have a look at BOL for further
information. I maintain a simple site (www.replicationanswers.com) and on
the links section there are some configuration walkthroughs. Unfortunately
there aren't yet any books to recommend, but keep an eye out for the
imminent release of Hilary Cotter's book.
HTH,
Paul Ibison
|||Unfortunately with SQL 2000 you can't replicate from Oracle to SQL Server
AFAIK. I have gone through Oracle's documentation and Oracle 8 and 9 and
don't see this option.
DTS sounds like the best option, although tracking changes (as Paul
mentions) will be difficult.
I did set something up in the past that did bulk inserts from Oracle to SQL
Server using vbscript which would so an insert select of the form
insert into SQLServer.Database.dbo.tableName
select * from OracleServer.Database.ServerName.SYSDBA.TableName where
PKOracle not in (select PKSQLServer SQLServer.dbo.tablename)
It might be simpler to use a linked server for this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"megha" <anonymous@.discussions.microsoft.com> wrote in message
news:A4C68146-4B9D-4136-8464-9E5271402E64@.microsoft.com...
> I have different heterogeneous databases (SQL Server as well as Oracle)
which need to transfer their data to a central SQL Server database for
reporting purposes. Central location needs to be kept up-to-date on a
periodic basis, say every night we consolidate data from different sources.
There can be maximum 100 such source databases. Each source can have max
100KB of data.
> Now I have to figure out which one is more suitable to my needs:
replication or DTS? I am new to both of these approaches. I don't know
whether DTS would even make a solution or not? So please advise what would
be the better approach. Also I would appreciate further links/references
that would help me move ahead with either of them.
> Thanks
> -megha
|||Thanks Paul for the feedback! I checked out your website and found that you work a lot with ASP.NET as well. I would like an advise here. How would web services fit in to my requirements here? Is there a way I can capture transactions on heterogenous data
base servers and then use web services to send them to a central server? Or is it better to go ahead with replication?
One more question: you suggested merge replication with central publisher. Why central publisher - I thought it would be central subscriber as I have many publishers and all data would need to come to one central server. Also, once arrived at central serv
er data might change but I don't want this data to propogate back to the publishers. Any changes coming from a publisher would overwrite the data at central location. Do I need to use merge replication or transactional replication is the solution? Please
explain.
|||Thank you Hilary for sharing your knowledge with me! I checked out the sample chapter of your upcoming book. There I have got a question for you. Are you saying that for SQL Server (any version) replication we can not have heterogeneous subscribers? I gue
ss thats correct and thats the reason you have mentioned DTS and Linked Servers.
But I did not quite follow the direction you pointed to. How can I use a linked server to an Oracle database that would simulate Oracle - SQL Server replication? Or how would DTS do that? OK I can use either of the two to access Oracle database but how do
I create/access the transaction log which I essentially need at central SQL Server - can you please explain more?
|||Megha,
you'll need to use replication for the SQL subscribers and DTS/linked
servers for Oracle. The data on the central reporting server can be
amalgamated into one table or more easily unioned using views. If you use
views and want to edit the data, you can partition them to enable edits or
use InsteadOf triggers. Webservices would only complicate things here, and
it'll be quite complex as it is :-)
The publisher/subscriber metaphor doesn't strictly apply to merge, and
centralized publishing is easier to manage/maintain.
You can implement unidirectional merge replication using the -EXCHANGETYPE
parameter. Transactional is possible, but merge is much simpler for your
needs.
HTH,
Paul Ibison
|||I had a mistake in my first question. I intend to ask - Are you saying that for SQL Server (any version) replication we can not have heterogeneous publishers?
|||Paul, I am still trying to understand: why central publishing would match my needs better. How I think about it is I do not need any data to flow from central location to different sources and so I would not need to publish anything on central side. Do I
understand it correct that whoever is changing the data can become a publisher and as central location's change of data do not need to go across it should not be a publisher? Am I missing on something here?
How unidirectional merge is simpler than transactional replication? Can you provide some facts? What I knew is that merge brings in additional complications to the process. I would like to know your reasons as I will be implementing it for the first time
and I do not have any convenience preferences so far.
You must have some insights into this, so please explain further.
|||Megha,
this is just my preference purely from the point of view of maintenance.
Remember, the publisher/subscriber metaphor doesn't strictly apply to merge,
meaning that either a publisher or a subscriber can change the data -
there's really no great distinction in this respect. There are some
differences eg the publisher wins conflicts by default, but in your case
this is probably not relevant.
If each remote computer is set up as a transactional publisher, this can be
a lot of maintenance. Each publisher typically is a publisher/distributer.
Each one has it's own set of alerts and jobs. All profile parameters
(timeouts, snapshot size etc), publication settings (duration of
transactions etc) are set on each remote machine separately. Also, backup
and restore for disaster recovery is also much more straightforward for
merge replication.
Regards,
Paul Ibison
|||OK now I can select central publishing topology for replication with unidirectional replication flowing from subscribers/publishers to central publisher. BUT even then I need to keep track of changes at subscribers/publishers so that it can bemerged at ce
ntral location. Won't I need then to define a distributor for each of these subscribers/publishers then? How can I get away with defining no publication settings (transaction duration as you quoted) on each one of them?
Can you explain it more or tell me if there is some book I can refer to in order to understand the merge replication more clearly? BOL does not detail on central publishing with unidirectional replication.
Thanks for your help!
DTS or Replication?
I need help deciding whether to use DTS or Replication:
We have four SQL Server databases on four separate servers with the same data schema, one for the north east, south west, mid atlantic, and mid west. We also have one "global" database on a separate server. First, and only this one time, we need to copy data from the four regional databases to the global database. Then, at least once a day we will need to copy changed and new data from the four regional SQL databases to the global database.
I have researched DTS and Replication and am still unsure how to proceed. DTS doesn't seem to have a mechanism to copy over only the changed data. Can somebody please advise me as to which method to use?
Thank you.
This sounds like a candidate for transactional replication to me.
Transactional replication bulk copies the data on first use but then only updates are applied, exactly what you are asking for. You will have to be careful about handling updates on the "global" database if that is a requirement, your key must be unique within all of the publication tables. Also, when setting up the subscriptions ensure replication does not try to drop the table first or replicate to seperate databases and join it together with a view. Using this view approach would be slightly less efficient but less prone to people pushing new subscriptions that drop all target tables.
DTS has no mechanism for doing this, you would have to write the logic yourself based on some sort of changed data marker/date flag. Why go to the hassle when SQL Server has a well tested system in place already?
Hope that helps
Nick
DBA
www.comoni.co.uk
Do you know of any texts or resources I can find to teach me how to do this? I am not familiar with how to run replication or create a replication job, or whatever you call it. Thanks
Karen
|||Merge replication is what you need. It is designed for the occasionally connected scenarios.
The following are some resources to get you started:
Planning Merge Replication
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replplan_2ipa.asp
How merge replication works
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/repltypes_30z7.asp
|||Merge replication would not be my choice here. Merge is best suited to 2 way data changes, not one way as in this case. The management overhead is higher with merge than transactional too. It is fine to run the distribution agent for transactional replication only once a day, providing there is space in the distribution database to store that day's changes.
As for documentation on this, books online is not particularly helpful on the basics. I found a quick Google for transactional replication brought up plenty of how-to articles though. It is worth spending the time reading the onlie resources as replication is a reasonably complex process and has a habit of causing problems if not planned correctly.
Nick
|||The main difference between merge and transactional replication is not two-way or one-way. There is a "download-only" option in SQL Server 2005 merge replication to allow you to setup one-way replication and you can setup bi-directional transactional replication. The main difference is how soon you will see the change. Giving you only want to sync once a day, I would go with merge replication.
Here is an overview of different replication types and how to choose between them:
http://msdn2.microsoft.com/en-us/library/ms152531(SQL.90).aspx
Here is an example of using merge reaplication to integrate data from multiple branch offices, which is similar to the original scenario in this post:
http://msdn2.microsoft.com/en-us/library/ms151790.aspx
|||Thank you all for your help! I have enough to move me in the right direction now. I'm sure I'll have problems along the way.....
Friday, February 17, 2012
DTS import new record can be replicated
I had merge replication setup. The replication is work fine if I insert new records
from enterprise manager. But, if use DTS to import new record from Access,
those new records can not be replicated. However, I updated one of those new record content, this updated new record will be replicated.
I appreicate you input.
James Cheng
James,
I have seen this type of behaviour before when if you bulk insert the rows
and choose the defaults, then FIRE_TRIGGERS is false and consequently the
rows are not added to MSmerge_contents. Disabling Fast Load may cause the
triggers to fire on new inserts or run sp_addtabletocontents to include the
rows already existing and then resynchronise.
Regards,
Paul Ibison
|||check out
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"James Cheng" <JamesCheng@.discussions.microsoft.com> wrote in message
news:3FF3AE3C-F66D-468F-92A3-EA3E2F879599@.microsoft.com...
> I had merge replication setup. The replication is work fine if I insert
new records
> from enterprise manager. But, if use DTS to import new record from
Access,
> those new records can not be replicated. However, I updated one of those
new record content, this updated new record will be replicated.
> I appreicate you input.
> James Cheng
from enterprise manager. But, if use DTS to import new record from Access,
those new records can not be replicated. However, I updated one of those new record content, this updated new record will be replicated.
I appreicate you input.
James Cheng
James,
I have seen this type of behaviour before when if you bulk insert the rows
and choose the defaults, then FIRE_TRIGGERS is false and consequently the
rows are not added to MSmerge_contents. Disabling Fast Load may cause the
triggers to fire on new inserts or run sp_addtabletocontents to include the
rows already existing and then resynchronise.
Regards,
Paul Ibison
|||check out
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"James Cheng" <JamesCheng@.discussions.microsoft.com> wrote in message
news:3FF3AE3C-F66D-468F-92A3-EA3E2F879599@.microsoft.com...
> I had merge replication setup. The replication is work fine if I insert
new records
> from enterprise manager. But, if use DTS to import new record from
Access,
> those new records can not be replicated. However, I updated one of those
new record content, this updated new record will be replicated.
> I appreicate you input.
> James Cheng
Subscribe to:
Posts (Atom)