Hi all,
Here's a situation I have been challenged with....
I will have up to 6 remote databases and I want them all to update a
single master database in a company with multiple production plants.
The remote databases will just have active work in a plant and only the
work that is in that particular plant. I would like the master
database to have all information from the plants. Ideally, the remote
plants will update the master database once an hour (consisting of new
inserts of orders coming into the plant and updated records as work
moves through the plant). Once work ships from a plant, I want to purge
it off the remote plant database, yet keep it on the master.
It seems with replication, if I delete records at a plant, it will
delete on the master, which is not what I want. The master is
basically the database customer service will use and needs active
status on work in the plants and would contain a couple of years of
information. The remote plant databases have just the data they need
and our optimized. A remote database has 20 tables, with multiple
tables with 1 to 20 million records actively inserted/udated each day.
The schema of the master is identical to the plants and all keys at
each plant are guaranteed unique.
Any ideas would be greatly appreciated, as I am currently contemplating
using bcp in an automated fashion and anticipating a nightmare of
keeping referential integrity.
Regards
What I think you need to do is the following.
1) create a filter on your publications which filter by plant id. Hopefully
you have a column to do this with. Then when you create you publication and
get to the specify articles dialog click on the browse button to the right
of your tables. Select the snapshot tab, and select the option to delete the
data which matches the row filter.
2) In the table article properties dialog, click on the commands tab and
replace the delete command with the word NONE - this will prevent deletes
from being replicated
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
<davidkrainess@.yahoo.com> wrote in message
news:1131135473.722037.278030@.g44g2000cwa.googlegr oups.com...
> Hi all,
> Here's a situation I have been challenged with....
>
> I will have up to 6 remote databases and I want them all to update a
> single master database in a company with multiple production plants.
> The remote databases will just have active work in a plant and only the
> work that is in that particular plant. I would like the master
> database to have all information from the plants. Ideally, the remote
> plants will update the master database once an hour (consisting of new
> inserts of orders coming into the plant and updated records as work
> moves through the plant). Once work ships from a plant, I want to purge
> it off the remote plant database, yet keep it on the master.
> It seems with replication, if I delete records at a plant, it will
> delete on the master, which is not what I want. The master is
> basically the database customer service will use and needs active
> status on work in the plants and would contain a couple of years of
> information. The remote plant databases have just the data they need
> and our optimized. A remote database has 20 tables, with multiple
> tables with 1 to 20 million records actively inserted/udated each day.
> The schema of the master is identical to the plants and all keys at
> each plant are guaranteed unique.
> Any ideas would be greatly appreciated, as I am currently contemplating
> using bcp in an automated fashion and anticipating a nightmare of
> keeping referential integrity.
> Regards
>
|||Thanks...
My initial thoughts were a bcp dump, zipping the data, sending it over
the WAN, and unzipping then loading it with update/insert SPs. bcp is
incredibly fast, but this strategy requires a lot of management
applications written to ensure the data gets to the master and there
might be loss of some referential integrity as each bcp command would
dump a table at a different time interval (that would be fixed in the
next hourly update). I am intrigued by the replication strategy
because of it's ease of use.
Assuming I have the distributon on the master, what is the performance
impact on the plant databases, assuming a publishing every hour. I am
concerned that the overhead of a replication scheme is vastly greater
than a bcp dump.
What is going on with the replication and how do people set this up. I
guess I was thinking of each plant's database dumping it's repl data on
a remote share over a WAN on the distribution server. I was thinking
the Master would reside on the same server as the distribution (why not
take advantage of loading the data off of a local disk). bw, I would
rather take slowness on the master than in a production facility.
Also...what's the overhead of the replication dump, is it similar to a
log? Given a WAN is down, would sql recover and recopy the replication
data when the WAN came backup?
Also, is this information in any of the replication books out there
(I.E. enterprise strategies for moving data) with pros and cons of
different methods.
Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts
Monday, March 26, 2012
Replication Design, Comments?
Ok, I think I have a working solution. I've worked the past
few days finding a lot of issues (not replication related) which
caused my headaches with replication, ie the @.@.servername was
null, and when creating a stored procedure snapshot it
would complain about unqualified inserts. Learned a neat
trick from Paul on how to do verbose logging. The developers
are looking at their stored procedures.
Merge Replication will be used.
I found that they had used identity columns as primary Key values.
Changed the columns to be 'not for replication'.
Tommorow I will be finding out if they have any DRI (Foreign Key
constraints that need to be marked 'not for replication'.
So far, I've created a Merge publication for the data, and it
appears to be functional.
I have also setup a snapshot publication for the stored procedures.
In addition to having the unqualifed insert issue I made the
mistake of checking 'all sp' instead of just selecting the
stored procedures they created (i was adding the replication
added routines by mistake).
If down the road they change a stored procedure, can I just
reinitialize this subscription to get the up-to-date routines?
I am replicating these because the standby server will be
the live server in case of the primary going offline.
I set the schedule on this subscription(sp) to only run
on demand.
Both machines are on the same backbone, so Bandwidth isn't
an issue.
Since I just found out about the possbible Foreign Key
issue (already created a snapshot, but not the subscription)
I am assuming that I will need to re-do the publication once
the foreign key constraints are marked 'not for replication'?
Thanks for the help. It has been an educational week.
Dave
You will have to drop the pub in order to change it to "not for replication"
(at least if using EM).
"David Gresham" <gresham@.panix.com> wrote in message
news:d5ruoa$6ms$1@.reader1.panix.com...
.....
> Since I just found out about the possbible Foreign Key
> issue (already created a snapshot, but not the subscription)
> I am assuming that I will need to re-do the publication once
> the foreign key constraints are marked 'not for replication'?
|||David,
on the changes to stored procedure point, I'd recommend using transactional
replication, which'll then give you the posibility of using sp_addscriptexec
for a change to a specific SP - and will avoid the need to reinitialize all
procedures.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
few days finding a lot of issues (not replication related) which
caused my headaches with replication, ie the @.@.servername was
null, and when creating a stored procedure snapshot it
would complain about unqualified inserts. Learned a neat
trick from Paul on how to do verbose logging. The developers
are looking at their stored procedures.
Merge Replication will be used.
I found that they had used identity columns as primary Key values.
Changed the columns to be 'not for replication'.
Tommorow I will be finding out if they have any DRI (Foreign Key
constraints that need to be marked 'not for replication'.
So far, I've created a Merge publication for the data, and it
appears to be functional.
I have also setup a snapshot publication for the stored procedures.
In addition to having the unqualifed insert issue I made the
mistake of checking 'all sp' instead of just selecting the
stored procedures they created (i was adding the replication
added routines by mistake).
If down the road they change a stored procedure, can I just
reinitialize this subscription to get the up-to-date routines?
I am replicating these because the standby server will be
the live server in case of the primary going offline.
I set the schedule on this subscription(sp) to only run
on demand.
Both machines are on the same backbone, so Bandwidth isn't
an issue.
Since I just found out about the possbible Foreign Key
issue (already created a snapshot, but not the subscription)
I am assuming that I will need to re-do the publication once
the foreign key constraints are marked 'not for replication'?
Thanks for the help. It has been an educational week.
Dave
You will have to drop the pub in order to change it to "not for replication"
(at least if using EM).
"David Gresham" <gresham@.panix.com> wrote in message
news:d5ruoa$6ms$1@.reader1.panix.com...
.....
> Since I just found out about the possbible Foreign Key
> issue (already created a snapshot, but not the subscription)
> I am assuming that I will need to re-do the publication once
> the foreign key constraints are marked 'not for replication'?
|||David,
on the changes to stored procedure point, I'd recommend using transactional
replication, which'll then give you the posibility of using sp_addscriptexec
for a change to a specific SP - and will avoid the need to reinitialize all
procedures.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Wednesday, March 21, 2012
replication autogenerated procs - interesting ;2 notation
I was looking to modify how the INSERT happens with regards to replication only to find my solution in the proc itself. When I edit the proc this is what I am displayed in SQL QA or EM:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure "sp_MSins_dboMEETING" @.c1 int,@.c2 int,@.c3 int,@.c4 varchar(250),@.c5 datetime,@.c6 datetime,@.c7 bit,@.c8 char(1),@.c9 datetime,@.c10 bit,@.c11 bit,@.c12 bit,@.c13 datetime,@.c14 smallint,@.c15 datetime,@.c16 smallint,@.c17 binary(8),@.c18 bit,@.c19 bit,@.c20 bit,@.c21 varchar(1000)
AS
BEGIN
insert into "dbo"."MEETING"(
"MEETING_ID", "MEETING_TYPE_ID", "MEETING_STATUS_ID", "TITLE", "START_DATE", "END_DATE", "PUBLISH_IND", "GROUP_IND", "PUBLISH_DATE", "MY_ADVISORS", "SUBMITTED_IND", "ACTIVE_IND", "CREATE_DATE", "CREATED_BY", "LAST_UPDATE_DATE", "LAST_UPDATED_BY", "DATE_INDEXED", "ON_DEMAND_IND", "NOT_REPORTED_IND", "MAJOR_PROJECT_IND", "MAJOR_PROJECT_COMMENT"
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, @.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, @.c13, @.c14, @.c15, @.c16, @.c17, @.c18, @.c19, @.c20, @.c21
)
END
GO
create procedure "sp_MSins_dboMEETING";2 @.c1 int,@.c2 int,@.c3 int,@.c4 varchar(250),@.c5 datetime,@.c6 datetime,@.c7 bit,@.c8 char(1),@.c9 datetime,@.c10 bit,@.c11 bit,@.c12 bit,@.c13 datetime,@.c14 smallint,@.c15 datetime,@.c16 smallint,@.c17 binary(8),@.c18 bit,@.c19 bit,@.c20 bit,@.c21 varchar(1000)
as
if exists ( select * from "dbo"."MEETING"
where "MEETING_ID" = @.c1
)
begin
update "dbo"."MEETING" set "MEETING_TYPE_ID" = @.c2,"MEETING_STATUS_ID" = @.c3,"TITLE" = @.c4,"START_DATE" = @.c5,"END_DATE" = @.c6,"PUBLISH_IND" = @.c7,"GROUP_IND" = @.c8,"PUBLISH_DATE" = @.c9,"MY_ADVISORS" = @.c10,"SUBMITTED_IND" = @.c11,"ACTIVE_IND" = @.c12,"CREATE_DATE" = @.c13,"CREATED_BY" = @.c14,"LAST_UPDATE_DATE" = @.c15,"LAST_UPDATED_BY" = @.c16,"DATE_INDEXED" = @.c17,"ON_DEMAND_IND" = @.c18,"NOT_REPORTED_IND" = @.c19,"MAJOR_PROJECT_IND" = @.c20,"MAJOR_PROJECT_COMMENT" = @.c21
where "MEETING_ID" = @.c1
end
else
begin
insert into "dbo"."MEETING" ( "MEETING_ID","MEETING_TYPE_ID","MEETING_STATUS_ID","TITLE","START_DATE","END_DATE","PUBLISH_IND","GROUP_IND","PUBLISH_DATE","MY_ADVISORS","SUBMITTED_IND","ACTIVE_IND","CREATE_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","DATE_INDEXED","ON_DEMAND_IND","NOT_REPORTED_IND","MAJOR_PROJECT_IND","MAJOR_PROJECT_COMMENT" ) values ( @.c1,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8,@.c9,@.c10,@.c11,@.c12 ,@.c13,@.c14,@.c15,@.c16,@.c17,@.c18,@.c19,@.c20,@.c21 )
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Is the second version listed at the bottom like a comment or can it actually get called? I am going to script out all of these procs and save them, and then remove the first version (listed at the top) and the use the second version since it does what I need. I just thought it was interesting to see two stored procedures in a single definition, never seen the "PROC_NAME";2 notation, have you? If so please tell me what it does, is it just a way to create a second version of the procedure in a comment type fashion or is it used another way?From Books online's Create Procedure reference;number
Is an optional integer used to group procedures of the same name so they can be
dropped together with a single DROP PROCEDURE statement. For example, the
procedures used with an application called orders may be named orderproc;1,
orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
entire group. If the name contains delimited identifiers, the number should
not be included as part of the identifier; use the appropriate delimiter around
procedure_name only.
I'll admit that I have neither used this functionality, nor ever seen it used. It may be that the application specifies which version of the procedure is executed by including the number in the call. And in that case, I wouldn't go dropping or commenting out the code, since it could break your app.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure "sp_MSins_dboMEETING" @.c1 int,@.c2 int,@.c3 int,@.c4 varchar(250),@.c5 datetime,@.c6 datetime,@.c7 bit,@.c8 char(1),@.c9 datetime,@.c10 bit,@.c11 bit,@.c12 bit,@.c13 datetime,@.c14 smallint,@.c15 datetime,@.c16 smallint,@.c17 binary(8),@.c18 bit,@.c19 bit,@.c20 bit,@.c21 varchar(1000)
AS
BEGIN
insert into "dbo"."MEETING"(
"MEETING_ID", "MEETING_TYPE_ID", "MEETING_STATUS_ID", "TITLE", "START_DATE", "END_DATE", "PUBLISH_IND", "GROUP_IND", "PUBLISH_DATE", "MY_ADVISORS", "SUBMITTED_IND", "ACTIVE_IND", "CREATE_DATE", "CREATED_BY", "LAST_UPDATE_DATE", "LAST_UPDATED_BY", "DATE_INDEXED", "ON_DEMAND_IND", "NOT_REPORTED_IND", "MAJOR_PROJECT_IND", "MAJOR_PROJECT_COMMENT"
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, @.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, @.c13, @.c14, @.c15, @.c16, @.c17, @.c18, @.c19, @.c20, @.c21
)
END
GO
create procedure "sp_MSins_dboMEETING";2 @.c1 int,@.c2 int,@.c3 int,@.c4 varchar(250),@.c5 datetime,@.c6 datetime,@.c7 bit,@.c8 char(1),@.c9 datetime,@.c10 bit,@.c11 bit,@.c12 bit,@.c13 datetime,@.c14 smallint,@.c15 datetime,@.c16 smallint,@.c17 binary(8),@.c18 bit,@.c19 bit,@.c20 bit,@.c21 varchar(1000)
as
if exists ( select * from "dbo"."MEETING"
where "MEETING_ID" = @.c1
)
begin
update "dbo"."MEETING" set "MEETING_TYPE_ID" = @.c2,"MEETING_STATUS_ID" = @.c3,"TITLE" = @.c4,"START_DATE" = @.c5,"END_DATE" = @.c6,"PUBLISH_IND" = @.c7,"GROUP_IND" = @.c8,"PUBLISH_DATE" = @.c9,"MY_ADVISORS" = @.c10,"SUBMITTED_IND" = @.c11,"ACTIVE_IND" = @.c12,"CREATE_DATE" = @.c13,"CREATED_BY" = @.c14,"LAST_UPDATE_DATE" = @.c15,"LAST_UPDATED_BY" = @.c16,"DATE_INDEXED" = @.c17,"ON_DEMAND_IND" = @.c18,"NOT_REPORTED_IND" = @.c19,"MAJOR_PROJECT_IND" = @.c20,"MAJOR_PROJECT_COMMENT" = @.c21
where "MEETING_ID" = @.c1
end
else
begin
insert into "dbo"."MEETING" ( "MEETING_ID","MEETING_TYPE_ID","MEETING_STATUS_ID","TITLE","START_DATE","END_DATE","PUBLISH_IND","GROUP_IND","PUBLISH_DATE","MY_ADVISORS","SUBMITTED_IND","ACTIVE_IND","CREATE_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","DATE_INDEXED","ON_DEMAND_IND","NOT_REPORTED_IND","MAJOR_PROJECT_IND","MAJOR_PROJECT_COMMENT" ) values ( @.c1,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8,@.c9,@.c10,@.c11,@.c12 ,@.c13,@.c14,@.c15,@.c16,@.c17,@.c18,@.c19,@.c20,@.c21 )
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Is the second version listed at the bottom like a comment or can it actually get called? I am going to script out all of these procs and save them, and then remove the first version (listed at the top) and the use the second version since it does what I need. I just thought it was interesting to see two stored procedures in a single definition, never seen the "PROC_NAME";2 notation, have you? If so please tell me what it does, is it just a way to create a second version of the procedure in a comment type fashion or is it used another way?From Books online's Create Procedure reference;number
Is an optional integer used to group procedures of the same name so they can be
dropped together with a single DROP PROCEDURE statement. For example, the
procedures used with an application called orders may be named orderproc;1,
orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
entire group. If the name contains delimited identifiers, the number should
not be included as part of the identifier; use the appropriate delimiter around
procedure_name only.
I'll admit that I have neither used this functionality, nor ever seen it used. It may be that the application specifies which version of the procedure is executed by including the number in the call. And in that case, I wouldn't go dropping or commenting out the code, since it could break your app.
Friday, March 9, 2012
Replication - Snapshots
Hi there,
I am looking at setting up a solution that relies on replication of data to
many publications. I believe what I need is merge replication. In this
scenario, many companies will receive data from the master publication but
each company should ONLY receive information for their concern. How do I get
the replication of these 250 companies to "configurably" only receive the
data they need.
Thanks in advance
what you are describing is a filtered publication.
You have two options create a publication which filters rows based on some
hardcoded value, is SalesID=123213, or using a dynamic filter where the
publication detects some property about the subscriber and only sends rows
to that subscriber, ie hostname() or @.@.servername. Dynamic filters work best
with pull subscriptions.
The technology is called horizontal partitioning, as you are creating your
publication you will get to a dialog called Customize the Properties of the
Publication. Select Yes, and then select Horizontally, by filtering the rows
of published data. Enter a filter clause that will filter the rows so that
only the rows you want to end up on the subscriber land there.
It also sounds like what you need is transactional replication
"Murray Foxcroft" <murray.foxcroft@.ast.co.za> wrote in message
news:OplWrAzFEHA.2980@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I am looking at setting up a solution that relies on replication of data
to
> many publications. I believe what I need is merge replication. In this
> scenario, many companies will receive data from the master publication but
> each company should ONLY receive information for their concern. How do I
get
> the replication of these 250 companies to "configurably" only receive the
> data they need.
> Thanks in advance
>
I am looking at setting up a solution that relies on replication of data to
many publications. I believe what I need is merge replication. In this
scenario, many companies will receive data from the master publication but
each company should ONLY receive information for their concern. How do I get
the replication of these 250 companies to "configurably" only receive the
data they need.
Thanks in advance
what you are describing is a filtered publication.
You have two options create a publication which filters rows based on some
hardcoded value, is SalesID=123213, or using a dynamic filter where the
publication detects some property about the subscriber and only sends rows
to that subscriber, ie hostname() or @.@.servername. Dynamic filters work best
with pull subscriptions.
The technology is called horizontal partitioning, as you are creating your
publication you will get to a dialog called Customize the Properties of the
Publication. Select Yes, and then select Horizontally, by filtering the rows
of published data. Enter a filter clause that will filter the rows so that
only the rows you want to end up on the subscriber land there.
It also sounds like what you need is transactional replication
"Murray Foxcroft" <murray.foxcroft@.ast.co.za> wrote in message
news:OplWrAzFEHA.2980@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I am looking at setting up a solution that relies on replication of data
to
> many publications. I believe what I need is merge replication. In this
> scenario, many companies will receive data from the master publication but
> each company should ONLY receive information for their concern. How do I
get
> the replication of these 250 companies to "configurably" only receive the
> data they need.
> Thanks in advance
>
Saturday, February 25, 2012
Replicating to MYSQL
I have been trying to replicate from SQL2K to MySQL and I'm getting an
'Invalid Cursor State' error. After looking around for a solution I
found this old message (http://tinyurl.com/2mb5ln) saying it was a bug
in the distribution agent code and it would be fixed in future service
packs.
Was this ever solved?
My understanding is that they did not.
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
"Fawzib Rojas" <f_rojas@.nospam.spectron.msim.com> wrote in message
news:OHG1g0hdHHA.4468@.TK2MSFTNGP03.phx.gbl...
>I have been trying to replicate from SQL2K to MySQL and I'm getting an
>'Invalid Cursor State' error. After looking around for a solution I found
>this old message (http://tinyurl.com/2mb5ln) saying it was a bug in the
>distribution agent code and it would be fixed in future service packs.
> Was this ever solved?
'Invalid Cursor State' error. After looking around for a solution I
found this old message (http://tinyurl.com/2mb5ln) saying it was a bug
in the distribution agent code and it would be fixed in future service
packs.
Was this ever solved?
My understanding is that they did not.
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
"Fawzib Rojas" <f_rojas@.nospam.spectron.msim.com> wrote in message
news:OHG1g0hdHHA.4468@.TK2MSFTNGP03.phx.gbl...
>I have been trying to replicate from SQL2K to MySQL and I'm getting an
>'Invalid Cursor State' error. After looking around for a solution I found
>this old message (http://tinyurl.com/2mb5ln) saying it was a bug in the
>distribution agent code and it would be fixed in future service packs.
> Was this ever solved?
Monday, February 20, 2012
Replicating Only Objects, no data
I need to find a solution for having the same database objects on two servers without the data, and be able to synchronize them on demand.
Whenever a table changes (alter, create, delete) I need to be able to replicate those changes to the destination server without affecting its data.
If I add, alter or delete a stored procedure, or other objects such as functions, etc. I need to be able to see those changes on the destination.
any ideas.
Replication support replicate DDL changes such as table, SP, and function changes. But replication support either both dml and ddl changes, or dml without tracking ddl changes. As your case, you only need ddl change without dml change, replication does not have parameter settings to support this. But, I am sure you can find workaround, as use View instead of Table, which can not query out data. Add View, stored procedure, and functions as articles in replication.
Hope it will help.
Thanks
Yunjing
Subscribe to:
Posts (Atom)