Hi,
In a merge replicaton, how can I configure it so that the update statements
will not be replicated to subscriber database?
Thanks.
Pingx
This can't be done. On the subscriber side you can have permissions checked
for DML originating on the subscriber and then selectively modify
permissions of the PAL account so it can't perform this DML.
http://www.zetainteractive.com - Shift Happens!
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
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:9DD5A341-4608-4EC2-A30C-6F4896F5E8A4@.microsoft.com...
> Hi,
> In a merge replicaton, how can I configure it so that the update
> statements
> will not be replicated to subscriber database?
> Thanks.
> Pingx
Showing posts with label merge. Show all posts
Showing posts with label merge. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
How MERGE few databases to big one
Hi All,
I have to merge few different databases to one big common database
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
Regards
Actually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables in
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign keys
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>
|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
Regards,
anxcomp
|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring over
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
..ndf associated as well - <filegroups>)... that is, physically move the files
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
I have to merge few different databases to one big common database
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
Regards
Actually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables in
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign keys
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>
|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
Regards,
anxcomp
|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring over
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
..ndf associated as well - <filegroups>)... that is, physically move the files
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
How MERGE few databases to big one
Hi All,
I have to merge few different databases to one big common database :)
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
--
RegardsActually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables in
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign keys
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database :)
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
--
Regards,
anxcomp|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring over
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
.ndf associated as well - <filegroups>)... that is, physically move the files
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>sql
I have to merge few different databases to one big common database :)
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
--
RegardsActually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables in
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign keys
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database :)
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
--
Regards,
anxcomp|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring over
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
.ndf associated as well - <filegroups>)... that is, physically move the files
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>sql
How MERGE few databases to big one
Hi All,
I have to merge few different databases to one big common database
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
RegardsActually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables i
n
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign key
s
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
Regards,
anxcomp|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring ove
r
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
.ndf associated as well - <filegroups> )... that is, physically move the fil
es
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
I have to merge few different databases to one big common database
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
RegardsActually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables i
n
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign key
s
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
Regards,
anxcomp|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring ove
r
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
.ndf associated as well - <filegroups> )... that is, physically move the fil
es
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
Monday, March 12, 2012
How long on Hilary's Merge book? :=)
Reading this pretty incredible book on SQL2000 Transactional and Snapshot
replication and wondering when Volume 2 is coming out?
I can't give you an answer on that. Right now we are looking at March/05.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:%23sOYjNd0EHA.2040@.tk2msftngp13.phx.gbl...
> Reading this pretty incredible book on SQL2000 Transactional and Snapshot
> replication and wondering when Volume 2 is coming out?
>
replication and wondering when Volume 2 is coming out?
I can't give you an answer on that. Right now we are looking at March/05.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:%23sOYjNd0EHA.2040@.tk2msftngp13.phx.gbl...
> Reading this pretty incredible book on SQL2000 Transactional and Snapshot
> replication and wondering when Volume 2 is coming out?
>
Friday, March 9, 2012
How is the row filter clause and join filter should be....
Hi,
I am new to replication, I have the following tables in my
database, and I would like to replicate to other
subscriber using Merge Replication:
tblProduct (Product master)
ProdID (PK)
ProdGrp (FK to Product grouping)
ProdDesc
tblProdGrp (Product grouping)
ProdGrp (PK)
ProdGrpDesc
tblCustomer (Customer master)
CustID (PK)
BranchID (FK to branch master)
OfferGrp (FK to trade offer grouping)
CustName
tblBranch (Branch master)
BranchID (PK)
BranchName
tblTradeOfferGroup (Trade offer grouping)
OfferGrp (PK)
OfferGrpDesc
tblTradeOffer (Trade offer)
OfferID (PK)
OfferGrp (FK to Trade offer grouping)
StartEffDate
EndEffDate
tblTradeOfferProduct (Trade offer's product)
SeqID (PK)
ProdGrp (PK/FK)
OfferID (PK/FK)
OfferQty
*Note: SeqID + ProdGrp + OfferID is unique
I need all rows from tblProduct, tblProdGrp, tblBranch,
tblTradeOfferGroup, tblTradeOffer, tblTradeOfferProduct to
replicate to subscriber, but only single branch's customer
in tblCustomer at subscriber. How should I configure the
row filter and join filter in my Merge Replication?
Currently, I configure as following:-
Row Filter:
tblCustomer row filter tblCustomer.BranchID = '001'
tblProduct <publish all rows>
tblProdGrp <publish all rows>
tblBranch <publish all rows>
tblTradeOfferGroup <publish all rows>
tblTradeOffer <publish all rows>
tblTradeOfferProduct <publish all rows>
Join Filter:
Filtered table Table to filter
tblTradeOfferGroup tblTradeOffer
tblTradeOffer.OfferGrp = tblTradeOfferGroup.OfferGrp
tblTradeOffer tblTradeOfferProduct
tblTradeOfferProduct.OfferID = tblTradeOffer.OfferID
But, tblTradeOfferProduct not replicated over. A conflict
occurs saying FOREIGN KEY constraint etc. The weird case
is, when I synchorise again, the rows publisher's
tblTradeOfferProduct are deleted.
Please advice.
Thank you.
HKM
I think, replicating tblTradeOfferProduct should fix your problem.
Since tblTradeOfferProduct has relations to tblTradeOffer (and I believe to
tblProduct too ) it is better to replicate this table.
Otherwise yuo have to declare all those relations as "NOT FOR REPLICATION"
Since you dont have "NOT FOR REPLICATION" whenever that are constraint
violatins you will see merge failing with constraint violations.
And once some entries fail to propagate to the subscriber, in the next
merge, compensating actions (deletes for all the failed inserts) are made
and hence you will see that those rows vanish from the database.
You can either set the constraints to "NOT FOR REPLICATION" or replicate the
tblTradeOfferProduct table too. One of them should fix the problem
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"HKM" <anonymous@.discussions.microsoft.com> wrote in message
news:04e801c49a0e$f4c9ebf0$a601280a@.phx.gbl...
> Hi,
> I am new to replication, I have the following tables in my
> database, and I would like to replicate to other
> subscriber using Merge Replication:
>
> tblProduct (Product master)
> --
> ProdID (PK)
> ProdGrp (FK to Product grouping)
> ProdDesc
> tblProdGrp (Product grouping)
> --
> ProdGrp (PK)
> ProdGrpDesc
> tblCustomer (Customer master)
> --
> CustID (PK)
> BranchID (FK to branch master)
> OfferGrp (FK to trade offer grouping)
> CustName
> tblBranch (Branch master)
> --
> BranchID (PK)
> BranchName
> tblTradeOfferGroup (Trade offer grouping)
> --
> OfferGrp (PK)
> OfferGrpDesc
> tblTradeOffer (Trade offer)
> --
> OfferID (PK)
> OfferGrp (FK to Trade offer grouping)
> StartEffDate
> EndEffDate
> tblTradeOfferProduct (Trade offer's product)
> --
> SeqID (PK)
> ProdGrp (PK/FK)
> OfferID (PK/FK)
> OfferQty
> *Note: SeqID + ProdGrp + OfferID is unique
> I need all rows from tblProduct, tblProdGrp, tblBranch,
> tblTradeOfferGroup, tblTradeOffer, tblTradeOfferProduct to
> replicate to subscriber, but only single branch's customer
> in tblCustomer at subscriber. How should I configure the
> row filter and join filter in my Merge Replication?
> Currently, I configure as following:-
> Row Filter:
> tblCustomer row filter tblCustomer.BranchID = '001'
> tblProduct <publish all rows>
> tblProdGrp <publish all rows>
> tblBranch <publish all rows>
> tblTradeOfferGroup <publish all rows>
> tblTradeOffer <publish all rows>
> tblTradeOfferProduct <publish all rows>
> Join Filter:
> Filtered table Table to filter
> tblTradeOfferGroup tblTradeOffer
> tblTradeOffer.OfferGrp = tblTradeOfferGroup.OfferGrp
> tblTradeOffer tblTradeOfferProduct
> tblTradeOfferProduct.OfferID = tblTradeOffer.OfferID
> But, tblTradeOfferProduct not replicated over. A conflict
> occurs saying FOREIGN KEY constraint etc. The weird case
> is, when I synchorise again, the rows publisher's
> tblTradeOfferProduct are deleted.
> Please advice.
> Thank you.
> HKM
>
I am new to replication, I have the following tables in my
database, and I would like to replicate to other
subscriber using Merge Replication:
tblProduct (Product master)
ProdID (PK)
ProdGrp (FK to Product grouping)
ProdDesc
tblProdGrp (Product grouping)
ProdGrp (PK)
ProdGrpDesc
tblCustomer (Customer master)
CustID (PK)
BranchID (FK to branch master)
OfferGrp (FK to trade offer grouping)
CustName
tblBranch (Branch master)
BranchID (PK)
BranchName
tblTradeOfferGroup (Trade offer grouping)
OfferGrp (PK)
OfferGrpDesc
tblTradeOffer (Trade offer)
OfferID (PK)
OfferGrp (FK to Trade offer grouping)
StartEffDate
EndEffDate
tblTradeOfferProduct (Trade offer's product)
SeqID (PK)
ProdGrp (PK/FK)
OfferID (PK/FK)
OfferQty
*Note: SeqID + ProdGrp + OfferID is unique
I need all rows from tblProduct, tblProdGrp, tblBranch,
tblTradeOfferGroup, tblTradeOffer, tblTradeOfferProduct to
replicate to subscriber, but only single branch's customer
in tblCustomer at subscriber. How should I configure the
row filter and join filter in my Merge Replication?
Currently, I configure as following:-
Row Filter:
tblCustomer row filter tblCustomer.BranchID = '001'
tblProduct <publish all rows>
tblProdGrp <publish all rows>
tblBranch <publish all rows>
tblTradeOfferGroup <publish all rows>
tblTradeOffer <publish all rows>
tblTradeOfferProduct <publish all rows>
Join Filter:
Filtered table Table to filter
tblTradeOfferGroup tblTradeOffer
tblTradeOffer.OfferGrp = tblTradeOfferGroup.OfferGrp
tblTradeOffer tblTradeOfferProduct
tblTradeOfferProduct.OfferID = tblTradeOffer.OfferID
But, tblTradeOfferProduct not replicated over. A conflict
occurs saying FOREIGN KEY constraint etc. The weird case
is, when I synchorise again, the rows publisher's
tblTradeOfferProduct are deleted.
Please advice.
Thank you.
HKM
I think, replicating tblTradeOfferProduct should fix your problem.
Since tblTradeOfferProduct has relations to tblTradeOffer (and I believe to
tblProduct too ) it is better to replicate this table.
Otherwise yuo have to declare all those relations as "NOT FOR REPLICATION"
Since you dont have "NOT FOR REPLICATION" whenever that are constraint
violatins you will see merge failing with constraint violations.
And once some entries fail to propagate to the subscriber, in the next
merge, compensating actions (deletes for all the failed inserts) are made
and hence you will see that those rows vanish from the database.
You can either set the constraints to "NOT FOR REPLICATION" or replicate the
tblTradeOfferProduct table too. One of them should fix the problem
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"HKM" <anonymous@.discussions.microsoft.com> wrote in message
news:04e801c49a0e$f4c9ebf0$a601280a@.phx.gbl...
> Hi,
> I am new to replication, I have the following tables in my
> database, and I would like to replicate to other
> subscriber using Merge Replication:
>
> tblProduct (Product master)
> --
> ProdID (PK)
> ProdGrp (FK to Product grouping)
> ProdDesc
> tblProdGrp (Product grouping)
> --
> ProdGrp (PK)
> ProdGrpDesc
> tblCustomer (Customer master)
> --
> CustID (PK)
> BranchID (FK to branch master)
> OfferGrp (FK to trade offer grouping)
> CustName
> tblBranch (Branch master)
> --
> BranchID (PK)
> BranchName
> tblTradeOfferGroup (Trade offer grouping)
> --
> OfferGrp (PK)
> OfferGrpDesc
> tblTradeOffer (Trade offer)
> --
> OfferID (PK)
> OfferGrp (FK to Trade offer grouping)
> StartEffDate
> EndEffDate
> tblTradeOfferProduct (Trade offer's product)
> --
> SeqID (PK)
> ProdGrp (PK/FK)
> OfferID (PK/FK)
> OfferQty
> *Note: SeqID + ProdGrp + OfferID is unique
> I need all rows from tblProduct, tblProdGrp, tblBranch,
> tblTradeOfferGroup, tblTradeOffer, tblTradeOfferProduct to
> replicate to subscriber, but only single branch's customer
> in tblCustomer at subscriber. How should I configure the
> row filter and join filter in my Merge Replication?
> Currently, I configure as following:-
> Row Filter:
> tblCustomer row filter tblCustomer.BranchID = '001'
> tblProduct <publish all rows>
> tblProdGrp <publish all rows>
> tblBranch <publish all rows>
> tblTradeOfferGroup <publish all rows>
> tblTradeOffer <publish all rows>
> tblTradeOfferProduct <publish all rows>
> Join Filter:
> Filtered table Table to filter
> tblTradeOfferGroup tblTradeOffer
> tblTradeOffer.OfferGrp = tblTradeOfferGroup.OfferGrp
> tblTradeOffer tblTradeOfferProduct
> tblTradeOfferProduct.OfferID = tblTradeOffer.OfferID
> But, tblTradeOfferProduct not replicated over. A conflict
> occurs saying FOREIGN KEY constraint etc. The weird case
> is, when I synchorise again, the rows publisher's
> tblTradeOfferProduct are deleted.
> Please advice.
> Thank you.
> HKM
>
Wednesday, March 7, 2012
How is the data secured over the air in Merge Replication ?
Dear ppl,
I have a question for you. One of our client has asked us how is the data secured over the air during Replication?
I have read that Merge Replication uses TLS (Transport Layer Security) protocol to secure the data over the air. But I was wondering if it is all done automatically ? Or do we need to install certificates like for SSL.
We have a Windows Mobile 5.0 application using SQL Mobile that runs over 50 devices and they all synchronise with a single Publisher SQL Server 2005 using GPRS connection. We haven't got any SSL certificates installed on the server.
Now how can i make use of TLS in my application to secure my data over the air (using TLS)?
Regards
Nabeel Farid
thanx for the response Greg.
I didn't exactly understand what you mean. But how can I enable encryption at the protocol level?
Regards
|||You can do this in SQL Server Configuration Manager. Search books online for "encryption protocols", you should find a topic named "Encrypting Connections to SQL Server".
|||You should also look at the Mobile Telecommunications carrier supplying the over the air connection and see what security options they have available. For instance, if there is a dedicated connection like a VPN or whether your are using a public network, the security options will differ.How is the data secured over the air in Merge Replication ?
Dear ppl,
I have a question for you. One of our client has asked us how is the data secured over the air during Replication?
I have read that Merge Replication uses TLS (Transport Layer Security) protocol to secure the data over the air. But I was wondering if it is all done automatically ? Or do we need to install certificates like for SSL.
We have a Windows Mobile 5.0 application using SQL Mobile that runs over 50 devices and they all synchronise with a single Publisher SQL Server 2005 using GPRS connection. We haven't got any SSL certificates installed on the server.
Now how can i make use of TLS in my application to secure my data over the air (using TLS)?
Regards
Nabeel Farid
thanx for the response Greg.
I didn't exactly understand what you mean. But how can I enable encryption at the protocol level?
Regards
|||You can do this in SQL Server Configuration Manager. Search books online for "encryption protocols", you should find a topic named "Encrypting Connections to SQL Server".
|||You should also look at the Mobile Telecommunications carrier supplying the over the air connection and see what security options they have available. For instance, if there is a dedicated connection like a VPN or whether your are using a public network, the security options will differ.
Subscribe to:
Posts (Atom)