Hi
Is it possible to replicate just some records in a table and can the filter
be implemented (I am trying to avoid a situation that employees will have
the full version database while they are away from the office [they only
need te records that are assigned to them])
Thank you,
Shmuel Shulman
SBS Technologies LTD
See "replication, filtering options" in BOL.
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:uB8E%2378hFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is it possible to replicate just some records in a table and can the
> filter be implemented (I am trying to avoid a situation that employees
> will have the full version database while they are away from the office
> [they only need te records that are assigned to them])
> Thank you,
> Shmuel Shulman
> SBS Technologies LTD
>
|||You can filter replication horizontally or vertically using a filter
you create.
Example:
You have a table that has a column "User"
In the merge agent parameters use option -Hosname [Dave] ( or whatever
value works)
Then in the filter clause use:
User = hostname()
That particular subscriber will only get records where User = "Dave"
This is for merge and I'm not sure about the other types of
replication.
|||Thanks all for your help
Shmuel
<seanbell68@.gmail.com> wrote in message
news:1121305341.878967.300120@.o13g2000cwo.googlegr oups.com...
> You can filter replication horizontally or vertically using a filter
> you create.
> Example:
> You have a table that has a column "User"
> In the merge agent parameters use option -Hosname [Dave] ( or whatever
> value works)
> Then in the filter clause use:
> User = hostname()
> That particular subscriber will only get records where User = "Dave"
> This is for merge and I'm not sure about the other types of
> replication.
>
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Friday, March 30, 2012
Partial database copy - kind of replication
Hi,
I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
location, what has only a 128 k line for Internet (even that in not working
always). The source DB is on SQLSeerver 2k too. Only some tables from the
given DB are important, but I can not make any structural change (column
addition) on the tables. The full database is pretty big (> 500 MBytes), so
I can not make a snapshot replication.
My first idea was to create a second DB, where I would make a shadow table
for every original table: record by record I would store the primary key of
the original record, and a kind of "CRC" from the rest of the data. When I
have to send the update, I would compare the stored CRC and a newly
generated one, and send only the records, where this CRC is not match. (I
hope it is understandable)
My questions are:
1. What simplier solutions you have
2. If none, what algorithm should I use for the "CRC", what insure that I
will recognise the change of the record? The tables holds all kind of
fields, even binary :(.
Thanks:
PeterPeter,
Did you look into merge replication? It seems to me that it might be a good
answer to your problem. You can choose what tables to replicate, and it will
be sending only changes to the remote server. If you need you can also apply
filters to your tables, you can filter rows as well as columns.
HTH, Igor
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
> working always). The source DB is on SQLSeerver 2k too. Only some tables
> from the given DB are important, but I can not make any structural change
> (column addition) on the tables. The full database is pretty big (> 500
> MBytes), so I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
> of the original record, and a kind of "CRC" from the rest of the data.
> When I have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary :(.
> Thanks:
> Peter
>|||I would use transactional replication for this.
If you want to do some sort of CRC comparison you should look at checksum.
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
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
working
> always). The source DB is on SQLSeerver 2k too. Only some tables from the
> given DB are important, but I can not make any structural change (column
> addition) on the tables. The full database is pretty big (> 500 MBytes),
so
> I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
of
> the original record, and a kind of "CRC" from the rest of the data. When I
> have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary :(.
> Thanks:
> Peter
>
I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
location, what has only a 128 k line for Internet (even that in not working
always). The source DB is on SQLSeerver 2k too. Only some tables from the
given DB are important, but I can not make any structural change (column
addition) on the tables. The full database is pretty big (> 500 MBytes), so
I can not make a snapshot replication.
My first idea was to create a second DB, where I would make a shadow table
for every original table: record by record I would store the primary key of
the original record, and a kind of "CRC" from the rest of the data. When I
have to send the update, I would compare the stored CRC and a newly
generated one, and send only the records, where this CRC is not match. (I
hope it is understandable)
My questions are:
1. What simplier solutions you have
2. If none, what algorithm should I use for the "CRC", what insure that I
will recognise the change of the record? The tables holds all kind of
fields, even binary :(.
Thanks:
PeterPeter,
Did you look into merge replication? It seems to me that it might be a good
answer to your problem. You can choose what tables to replicate, and it will
be sending only changes to the remote server. If you need you can also apply
filters to your tables, you can filter rows as well as columns.
HTH, Igor
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
> working always). The source DB is on SQLSeerver 2k too. Only some tables
> from the given DB are important, but I can not make any structural change
> (column addition) on the tables. The full database is pretty big (> 500
> MBytes), so I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
> of the original record, and a kind of "CRC" from the rest of the data.
> When I have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary :(.
> Thanks:
> Peter
>|||I would use transactional replication for this.
If you want to do some sort of CRC comparison you should look at checksum.
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
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
working
> always). The source DB is on SQLSeerver 2k too. Only some tables from the
> given DB are important, but I can not make any structural change (column
> addition) on the tables. The full database is pretty big (> 500 MBytes),
so
> I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
of
> the original record, and a kind of "CRC" from the rest of the data. When I
> have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary :(.
> Thanks:
> Peter
>
Partial database copy - kind of replication
Hi,
I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
location, what has only a 128 k line for Internet (even that in not working
always). The source DB is on SQLSeerver 2k too. Only some tables from the
given DB are important, but I can not make any structural change (column
addition) on the tables. The full database is pretty big (> 500 MBytes), so
I can not make a snapshot replication.
My first idea was to create a second DB, where I would make a shadow table
for every original table: record by record I would store the primary key of
the original record, and a kind of "CRC" from the rest of the data. When I
have to send the update, I would compare the stored CRC and a newly
generated one, and send only the records, where this CRC is not match. (I
hope it is understandable)
My questions are:
1. What simplier solutions you have
2. If none, what algorithm should I use for the "CRC", what insure that I
will recognise the change of the record? The tables holds all kind of
fields, even binary
.
Thanks:
Peter
Peter,
Did you look into merge replication? It seems to me that it might be a good
answer to your problem. You can choose what tables to replicate, and it will
be sending only changes to the remote server. If you need you can also apply
filters to your tables, you can filter rows as well as columns.
HTH, Igor
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
> working always). The source DB is on SQLSeerver 2k too. Only some tables
> from the given DB are important, but I can not make any structural change
> (column addition) on the tables. The full database is pretty big (> 500
> MBytes), so I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
> of the original record, and a kind of "CRC" from the rest of the data.
> When I have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary.
> Thanks:
> Peter
>
|||I would use transactional replication for this.
If you want to do some sort of CRC comparison you should look at checksum.
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
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
working
> always). The source DB is on SQLSeerver 2k too. Only some tables from the
> given DB are important, but I can not make any structural change (column
> addition) on the tables. The full database is pretty big (> 500 MBytes),
so
> I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
of
> the original record, and a kind of "CRC" from the rest of the data. When I
> have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary.
> Thanks:
> Peter
>
|||Unless the dataflow is in both directionns, and involves
autonomy or conflicts, I would recommend transactional
over merge, as it is significvantly faster and less
resource-intensive.
If you want a non-replication version, then
binary_checksums can be used although there is some
discussion as to whether these can always be relied upon
100%. Redgate do a DataCompare tool to do this type of
thing also.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
location, what has only a 128 k line for Internet (even that in not working
always). The source DB is on SQLSeerver 2k too. Only some tables from the
given DB are important, but I can not make any structural change (column
addition) on the tables. The full database is pretty big (> 500 MBytes), so
I can not make a snapshot replication.
My first idea was to create a second DB, where I would make a shadow table
for every original table: record by record I would store the primary key of
the original record, and a kind of "CRC" from the rest of the data. When I
have to send the update, I would compare the stored CRC and a newly
generated one, and send only the records, where this CRC is not match. (I
hope it is understandable)
My questions are:
1. What simplier solutions you have
2. If none, what algorithm should I use for the "CRC", what insure that I
will recognise the change of the record? The tables holds all kind of
fields, even binary
.Thanks:
Peter
Peter,
Did you look into merge replication? It seems to me that it might be a good
answer to your problem. You can choose what tables to replicate, and it will
be sending only changes to the remote server. If you need you can also apply
filters to your tables, you can filter rows as well as columns.
HTH, Igor
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
> working always). The source DB is on SQLSeerver 2k too. Only some tables
> from the given DB are important, but I can not make any structural change
> (column addition) on the tables. The full database is pretty big (> 500
> MBytes), so I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
> of the original record, and a kind of "CRC" from the rest of the data.
> When I have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary
.> Thanks:
> Peter
>
|||I would use transactional replication for this.
If you want to do some sort of CRC comparison you should look at checksum.
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
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
working
> always). The source DB is on SQLSeerver 2k too. Only some tables from the
> given DB are important, but I can not make any structural change (column
> addition) on the tables. The full database is pretty big (> 500 MBytes),
so
> I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
of
> the original record, and a kind of "CRC" from the rest of the data. When I
> have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary
.> Thanks:
> Peter
>
|||Unless the dataflow is in both directionns, and involves
autonomy or conflicts, I would recommend transactional
over merge, as it is significvantly faster and less
resource-intensive.
If you want a non-replication version, then
binary_checksums can be used although there is some
discussion as to whether these can always be relied upon
100%. Redgate do a DataCompare tool to do this type of
thing also.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Partial and Full Replication
Hi,
Is it possible to setup full replication of a database to run nightly
as well as partial replication of only a couple of tables on an hourly
basis?
Thanks,
Jason
Hello Jason,
There might be conflits because there are two replicaitons for the same
tables. You may consider set up a replicaiton of some tables to run nightly
and the rest tables to run on a hourly basis.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: jason.hill@.pearsoned.com.au
| Newsgroups: microsoft.public.sqlserver.replication
| Subject: Partial and Full Replication
| Date: 9 Aug 2005 20:25:54 -0700
| Organization: http://groups.google.com
| Lines: 10
| Message-ID: <1123644354.337678.325910@.g44g2000cwa.googlegroups .com>
| NNTP-Posting-Host: 203.143.94.10
| Mime-Version: 1.0
| Content-Type: text/plain; charset="iso-8859-1"
| X-Trace: posting.google.com 1123644360 22422 127.0.0.1 (10 Aug 2005
03:26:00 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Wed, 10 Aug 2005 03:26:00 +0000 (UTC)
| User-Agent: G2/0.2
| Complaints-To: groups-abuse@.google.com
| Injection-Info: g44g2000cwa.googlegroups.com; posting-host=203.143.94.10;
| posting-account=vUxf9g0AAAC_Tbj8TfB8dQv9VE03ynWf
| Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfee d00.sul.t-online.de!t-onli
ne.de!news.glorb.com!postnews.google.com!g44g2000c wa.googlegroups.com!not-fo
r-mail
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15717
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| Hi,
|
| Is it possible to setup full replication of a database to run nightly
| as well as partial replication of only a couple of tables on an hourly
| basis?
|
| Thanks,
|
| Jason
|
|
|||Jason,
you'll need 2 publications for this - one containing the 2 tables and the
other publication containing the rest of the tables. This is often done to
separate lookup tables from transactional tables. However, if there are
PK-FK relationships across the publications, there can obviously be issues
arising.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the prompt replies. Sounds like 2 publications is the way
to go, splitting the tables out separately, which will work perfectly.
Cheers,
Jason
Is it possible to setup full replication of a database to run nightly
as well as partial replication of only a couple of tables on an hourly
basis?
Thanks,
Jason
Hello Jason,
There might be conflits because there are two replicaitons for the same
tables. You may consider set up a replicaiton of some tables to run nightly
and the rest tables to run on a hourly basis.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: jason.hill@.pearsoned.com.au
| Newsgroups: microsoft.public.sqlserver.replication
| Subject: Partial and Full Replication
| Date: 9 Aug 2005 20:25:54 -0700
| Organization: http://groups.google.com
| Lines: 10
| Message-ID: <1123644354.337678.325910@.g44g2000cwa.googlegroups .com>
| NNTP-Posting-Host: 203.143.94.10
| Mime-Version: 1.0
| Content-Type: text/plain; charset="iso-8859-1"
| X-Trace: posting.google.com 1123644360 22422 127.0.0.1 (10 Aug 2005
03:26:00 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Wed, 10 Aug 2005 03:26:00 +0000 (UTC)
| User-Agent: G2/0.2
| Complaints-To: groups-abuse@.google.com
| Injection-Info: g44g2000cwa.googlegroups.com; posting-host=203.143.94.10;
| posting-account=vUxf9g0AAAC_Tbj8TfB8dQv9VE03ynWf
| Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfee d00.sul.t-online.de!t-onli
ne.de!news.glorb.com!postnews.google.com!g44g2000c wa.googlegroups.com!not-fo
r-mail
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15717
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| Hi,
|
| Is it possible to setup full replication of a database to run nightly
| as well as partial replication of only a couple of tables on an hourly
| basis?
|
| Thanks,
|
| Jason
|
|
|||Jason,
you'll need 2 publications for this - one containing the 2 tables and the
other publication containing the rest of the tables. This is often done to
separate lookup tables from transactional tables. However, if there are
PK-FK relationships across the publications, there can obviously be issues
arising.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the prompt replies. Sounds like 2 publications is the way
to go, splitting the tables out separately, which will work perfectly.
Cheers,
Jason
Wednesday, March 21, 2012
Partitioned View Pruning Issue
I'm
using Sql Server 2000 Standard Edition,
service pack 3. I'm fairly new to
partitioned views and merge replication.
We have a 3-year set of monthly
tables with about 1 million rows per table from some code we inherited. To ease development, I'm trying to create a
local partitioned view. We wanted it to be UPDATE-able and be able to select
data by a date range or by id without incurring very much cost. The problem is
that my SELECT statements correctly prune the unneeded tables while my INSERT, UPDATE ,
and DELETE statements do not.
Here's our basic structure:
CREATE
TABLE [dbo].[Call_2004_01] (
[id] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[callStartedOn] [datetime] NOT
NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF_Call_2004_01_rowguid] DEFAULT (newid()),
CONSTRAINT
[PK_Call_2004_01] PRIMARY KEY CLUSTERED
(
[id]
)
WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_Call_2004_01_id]
CHECK ([id] >= '200401' and [id] < '200402')
) ON
[PRIMARY]
CREATE TABLE [dbo].[Call_2004_02] (
[id] [varchar] (64)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[callStartedOn]
[datetime] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
CONSTRAINT [DF_Call_2004_02_rowguid] DEFAULT (newid()),
CONSTRAINT
[PK_Call_2004_02] PRIMARY KEY CLUSTERED
(
[id]
)
WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_Call_2004_02_id]
CHECK ([id] >= '200402' and [id] < '200403')
) ON
[PRIMARY]
CREATE VIEW
Call
AS
SELECT * FROM
dbo.Call_2004_02
UNION ALL
SELECT * FROM
dbo.Call_2004_01
GO
The ids themselves contain the date information,
so we decided to use the id as the partition key. (I originally had the date and
the id as the primary key and it still didn't work.)
If you
run:
SET STATISTICS IO ON
SELECT * FROM Call WHERE [id] =
'20040104-fake-asdf'
You'll get the exact query plan and IO you would
expect. It only shows the Call_2004_01 table. However, if you run:
SET
STATISTICS IO ON
UPDATE Call SET [callStartedOn] = '2004-01-01 00:00:00.000'
WHERE [id] = '20040104-fake-asdf'
or
SET STATISTICS IO
ON
DELETE FROM Call WHERE [id] = '20040104-fake-asdf'
It scans all of
the tables in the view. If I add an index on top of the primary key, the
STATISTICS IO comes back as 0's for the unused table, but the query plan still
shows execution on them.
To add injury to insult, we have merge
replication set up for these tables. So, when the INSERT, UPDATE, or DELETE statement
occurs on the tables that it isn't supposed to run on, the triggers for those
tables fire. This becomes apparent when you look at the STATISTICS IO, query
plan, and/or the fact that a simple query which should take less than 1 second
is now taking at least 8.
The problem goes away, of course, when you
specify the table name instead of the view.
So, what am I missing here?
At this point, I'm just grasping at straws.
Also, I tried upgrading to service pack 4 just now and that didn't change anything.|||It is a bug in SQL Server 2000 due to auto-parameterization. It has been fixed in SQL Server 2005 so your examples will work fine there. You can make it work in SQL Server 2000 by adding some bogus parameter to the WHERE clause of the UPDATE & DELETE statement.
declare @.p int
SET STATISTICS IO ON
UPDATE Call SET [callStartedOn] = '2004-01-01 00:00:00.000'
WHERE [id] = '20040104-fake-asdf' and @.p is null
go
declare @.p int
SET STATISTICS IO ON
DELETE FROM Call WHERE [id] = '20040104-fake-asdf' and @.p is nullgo
To summarize, the partition pruning will not happen in cases where auto-parameterization of the statement occurs. This typically happens in cases where you use constants/literals in WHERE clause.
|||Thanks. I'll give that a try when I get back to work tomorrow. However, I had originally been trying with a variable when I noticed the problem. Something like
DECLARE @.callId varchar(64)
SET @.callId = '20040104-fake-asdf'
DELETE FROM Call WHERE [id] = @.callId
Maybe the bogus data will help.
|||The bogus data helped some. Now it's behaving the same way it would if I manually created additional indexes on the PK. However, it's still not properly pruning the data out of the view. Here's the query I'm running and the output I'm getting.DECLARE @.p bit
SET @.p = NULL
DECLARE @.callId varchar(64)
SET @.callId = '2004010100281315'
DELETE FROM Call WHERE [id] = @.callId AND @.p IS NULL
STATISTICS IO (I deleted quite a bit):
Table 'Call_2006_12'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
... [All other tables between are the same as the above] ...
Table 'Call_2004_02'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'Call_2004_01'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
(0 row(s) affected)
Table 'sysmergearticles'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'MSmerge_replinfo'. Scan count 22, logical reads 44, physical reads 0, read-ahead reads 0.
Table 'sysmergesubscriptions'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'MSmerge_tombstone'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'MSmerge_contents'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sysmergearticles'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.
(15 row(s) affected)
[the above (starting with the first "Table 'sysmergearticles'") repeats 35 more times]
SHOWPLAN_TEXT Results (again, edited for size):
|--Sequence
|--Clustered Index Delete(OBJECT:([Callbright].[dbo].[Call_2004_01].[PK_Call_2004_01]))
| |--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
| |--Filter(WHERE:(STARTUP EXPR([@.callId]<'200402' AND [@.callId]>='200401')))
| |--Clustered Index Seek(OBJECT:([Callbright].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]=[@.callId]) ORDERED FORWARD)
... [All other tables between are the same as the above] ...
|--Clustered Index Delete(OBJECT:([Callbright].[dbo].[Call_2006_12].[PK_Call_2006_12]))
|--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
|--Filter(WHERE:(STARTUP EXPR([@.callId]<'200701' AND [@.callId]>='200612')))
|--Clustered Index Seek(OBJECT:([Callbright].[dbo].[Call_2006_12].[PK_Call_2006_12]), SEEK:([Call_2006_12].[id]=[@.callId]) ORDERED FORWARD)
Also, I should mention that the Clustered Index Delete takes 2% of the execution time and performs 1 execution while the Clustered Index Seek takes 1% of the execution time and performs 0 executions on the tables that should be pruned.
Any ideas?|||
There are two ways in which the partitions can be eliminated and it depends on the type of query.
1. Partition elimination at compile-time - This will happen usually if you specify the partition key value as a constant in the WHERE clause. You can observe this behavior in the showplan output (estimated or actual execution plan)
2. Partition elimination at run-time - This will happen in cases where the partition key value is not known at compile-time or the plan needs to handle cases where the key could change between executions and so on. In this case, you will not see the partition(s) being eliminated in the showplan output (estimated or actual). You will however see STARTUP EXPR filters in the various branches. You can think of this as short-circuit mechanisms that gets evaluated at run-time and prevents that branch from executing.
So given these different plan strategies, the best way to determine which partition(s) are being eliminated is to look at the SET STATISTICS IO output and look for read counters with zero values to identify the partition(s) that are being eliminated. Another technique is to look at SET STATISTICS PROFILE ON (or SET STATISTICS XML PROFILE ON in SQL Server 2005) output. In this case, you will get the actual execution plan along with the execution statistics. You would specifically look at rows in the SET STATISTICS PROFILE output (In SQL Server 2000) for rows which have EXECUTES = 0 and/or ROWS = 0. This will tell you the partition(s) that were eliminated.
To summarize, from your showplan output the partition elimination is happening fine and it uses mechanism #2 described above. As for the replication tables, my guess is that it is a result of the replication trigger on the table that is propogating the deletes. Hope this helps.
|||I would have thought that if the partition was actually doing any pruning that the merge subscription triggers on the pruned tables wouldn't be firing. Is that wrong?|||Here's my query and SET STATISTICS PROFILE ON output. As you can see, it's still performing 3 executions per "pruned" table (and one of those is a delete). Shouldn't this be all zeros except for the Call_2004_01 table?SET STATISTICS PROFILE ON
DECLARE @.bogusVariable bit
DELETE FROM Call WHERE [id] = '2004010100000000001' AND @.bogusVariable IS NULL
0 1 DELETE FROM Call WHERE [id] = '2004010100000000001' AND @.bogusVariable IS NULL 436 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 0.4912329 NULL NULL DELETE 0 NULL
0 1 |--Sequence 436 2 1 Sequence Sequence NULL NULL 1.0 0.0 3.6000001E-5 56 0.4912329 NULL NULL PLAN_ROW 0 1.0
0 1 |--Clustered Index Delete(OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01])) 436 3 2 Clustered Index Delete Delete OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01]) NULL 1.0 1.0161194E-2 0.000001 56 1.3445652E-2 NULL NULL PLAN_ROW 0 1.0
0 1 | |--Filter(WHERE:(STARTUP EXPR([@.bogusVariable]=NULL))) 436 5 3 Filter Filter WHERE:(STARTUP EXPR([@.bogusVariable]=NULL)) NULL 1.0 0.0 2.7999999E-7 311 3.2833579E-3 [Bmk1006] NULL PLAN_ROW 0 1.0
0 1 | |--Clustered Index Seek(OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]='2004010100000000001') ORDERED FORWARD) 436 6 5 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]='2004010100000000001') ORDERED FORWARD [Bmk1006] 1.0 3.2034749E-3 7.9603E-5 311 3.2830781E-3 [Bmk1006] NULL PLAN_ROW 0 1.0
0 1 |--Clustered Index Delete(OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02])) 436 9 2 Clustered Index Delete Delete OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02]) NULL 1.0 1.0161523E-2 0.000001 56 1.3445981E-2 NULL NULL PLAN_ROW 0 1.0
0 1 | |--Filter(WHERE:(STARTUP EXPR([@.bogusVariable]=NULL))) 436 11 9 Filter Filter WHERE:(STARTUP EXPR([@.bogusVariable]=NULL)) NULL 1.0 0.0 2.7999999E-7 307 3.2833579E-3 [Bmk1008] NULL PLAN_ROW 0 1.0
0 1 | |--Filter(WHERE:(STARTUP EXPR(0))) 436 12 11 Filter Filter WHERE:(STARTUP EXPR(0)) NULL 1.0 3.2034749E-3 7.9603E-5 307 3.2830781E-3 [Bmk1008] NULL PLAN_ROW 0 1.0
0 0 | |--Clustered Index Seek(OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02]), SEEK:([Call_2004_02].[id]='2004010100000000001') ORDERED FORWARD) 436 13 12 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02]), SEEK:([Call_2004_02].[id]='2004010100000000001') ORDERED FORWARD [Bmk1008] 1.0 3.2034749E-3 7.9603E-5 307 3.2830781E-3 [Bmk1008] NULL PLAN_ROW 0 1.0
... [The rest of the tables are here and mirror the above statement] ...
0 1 |--Clustered Index Delete(OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12])) 436 281 2 Clustered Index Delete Delete OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12]) NULL 1.0 1.6756756E-2 0.000001 56 2.0041214E-2 NULL NULL PLAN_ROW 0 1.0
0 1 |--Filter(WHERE:(STARTUP EXPR([@.bogusVariable]=NULL))) 436 283 281 Filter Filter WHERE:(STARTUP EXPR([@.bogusVariable]=NULL)) NULL 1.0 0.0 2.7999999E-7 324 3.2833579E-3 [Bmk1552] NULL PLAN_ROW 0 1.0
0 1 |--Filter(WHERE:(STARTUP EXPR(0))) 436 284 283 Filter Filter WHERE:(STARTUP EXPR(0)) NULL 1.0 3.2034749E-3 7.9603E-5 324 3.2830781E-3 [Bmk1552] NULL PLAN_ROW 0 1.0
0 0 |--Clustered Index Seek(OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12]), SEEK:([Call_2006_12].[id]='2004010100000000001') ORDERED FORWARD) 436 285 284 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12]), SEEK:([Call_2006_12].[id]='2004010100000000001') ORDERED FORWARD [Bmk1552] 1.0 3.2034749E-3 7.9603E-5 324 3.2830781E-3 [Bmk1552] NULL PLAN_ROW 0 1.0
After this, there's a bunch of stuff for merge replication which really means the triggers on the "pruned" tables fired. To me, this indicates some kind of a pruning error (the table definitions are wrong, sql server 2000 is wrong, etc.). Am I wrong in thinking that? Does Sql Server 2000 execute insert/update/delete statements this way by design?|||
Don't look at the DELETE operator executes only. Start with the clustered index seek for each partition and you can see how many rows it produces & the number of executes. This way you can figure out if a parent operator (delete in this case) did any work. I tried your example with some sample data and following DELETE statement:
-- I added one row in each partition:
DECLARE @.p bit
DELETE FROM Call WHERE [id] = '20040104-fake-asdf' and @.p is null
-- set statistics io output
/*
Table 'Call_2004_01'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'Call_2004_02'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
*/
-- set statistics profile partial output
/*
Rows Executes StmtText
- -- -
1 1 DELETE FROM Call WHERE [id] = '20040104-fake-asdf' COLLATE SQL_Latin1_General_CP1_CI_AS and @.p is null
1 1 |--Sequence
0 1 |--Clustered Index Delete(OBJECT:([MyTestDB].[dbo].[Call_2004_02].[PK_Call_2004_02]))
0 1 | |--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
0 1 | |--Filter(WHERE:(STARTUP EXPR(0)))
0 0 | |--Clustered Index Seek(OBJECT:([MyTestDB].[dbo].[Call_2004_02].[PK_Call_2004_02]), SEEK:([Call_2004_02].[id]='20040104-fake-asdf') ORDERED FORWARD)
1 1 |--Clustered Index Delete(OBJECT:([MyTestDB].[dbo].[Call_2004_01].[PK_Call_2004_01]))
1 1 |--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
1 1 |--Clustered Index Seek(OBJECT:([MyTestDB].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]='20040104-fake-asdf') ORDERED FORWARD)
*/
Now, zero rows went through the delete operators but we will still fire the triggers on the partition tables. The triggers should be intelligent enough to perform no work in this case. This is also no different than doing DELETE t WHERE 1=0 which will still fire the trigger. This may seem kind of strange but this is how triggers work. There is one exception in case of distributed partitioned views where the triggers may not fire on the remote tables. Hope this helps explain the behavior you are seeing.
And you should start a different thread in the replication newsgroup about their trigger behavior in this case.
|||I guess I figured that the view would prevent any execution on a table that didn't meet the constraints, but I can see how your example is related to this situation. The subscription triggers are smart enough not to perform any inserts, but they do perform enough work to cause problems. I'd considered modifying them to make them do even less work (where it would check the Inserted and Deleted tables for any rows before executing), but maintenance on those triggers seemed painful since they're auto-generated, so instead, I added INSTEAD OF triggers to the view that prune the tables the way I expected them to be pruned.
Thanks.
Monday, February 20, 2012
Parameterized filters on publication?
Does anyone know if you can use any other parameters in the row filters for merge replication besides the functions SUSER_NAME() and HOST_NAME()?
I would like to create a publication for a couple thousand mobile databases to replicate with one SQL Database but filter what data they get based on some parameters. Do I have to hard code WHERE statements into static filters and create a publication for every user (seems a little ridiculous)?
Is there a proper way to do this using the SUSER_NAME and give each user a different connection name that will filter data properly?
Thanks,
Patrick Kafka
sorry, i'm not following you. Can you give an example of what exactly you want to filter on and what parameters you're referring to, or how you envision your filter to look like?|||You can achieve what you wish using the SUSER_SNAME filter.
Lets say you have a table: customers (id int, Name varchar(20)
and it has the following values:
(1, ;'Steve')
(2, 'Bill')
(3, 'Peter')
(4, 'Jay')
Lets say your filter is: subset_filterclause='Name=SUSER_SNAME()'
With this filter, when a mobile user comes in (say Bill), he will send value Bill to SUSER_SNAME and hence get row with id=2.
Similarly for Peter, the row with id=3 will be sent.
Is that what you want to achieve?
Parameterized filters on publication?
Does anyone know if you can use any other parameters in the row filters for merge replication besides the functions SUSER_NAME() and HOST_NAME()?
I would like to create a publication for a couple thousand mobile databases to replicate with one SQL Database but filter what data they get based on some parameters. Do I have to hard code WHERE statements into static filters and create a publication for every user (seems a little ridiculous)?
Is there a proper way to do this using the SUSER_NAME and give each user a different connection name that will filter data properly?
Thanks,
Patrick Kafka
sorry, i'm not following you. Can you give an example of what exactly you want to filter on and what parameters you're referring to, or how you envision your filter to look like?|||You can achieve what you wish using the SUSER_SNAME filter.
Lets say you have a table: customers (id int, Name varchar(20)
and it has the following values:
(1, ;'Steve')
(2, 'Bill')
(3, 'Peter')
(4, 'Jay')
Lets say your filter is: subset_filterclause='Name=SUSER_SNAME()'
With this filter, when a mobile user comes in (say Bill), he will send value Bill to SUSER_SNAME and hence get row with id=2.
Similarly for Peter, the row with id=3 will be sent.
Is that what you want to achieve?
Parameterized filtering Question - Merge Replication
Hi everyone~!,
I would like to ask some help from you guys regarding parameterized filtering for merge replication from SQL server 2005 as publisher and sql server 2005 Compact Edition as subscriber.
I search through the forum for similar problems but i still could not get it to work. I could not find more detailed solution on the web to help me solve the problem. My question are below, i really hope i can find some help here. Thank you.
1. As far as i know, the only way to specify parameterized filtering is to use SUSER_SNAME and SHOST_NAME. I do not want to use sqlserver login name for filtering, so SUSER_SNAME is already out. I also do not intent to use MachineName so again SHOST_NAME cannot be used. But from my reading, SHOST_NAME can be overwritten. Is the hostname overwrite function done in my C# application codes?
2. If i overwrite the hostname, will the machinename on that particular pc changed affecting the pc network connectivity?
3. From this article http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1070402&SiteID=1.
run the following sql inside SQL Server,
DECLARE @.publication AS sysname;
DECLARE @.article AS sysname;
SET @.publication = N'HASSystem';
SET @.article = N'Audit';
USE [HASSystem]
EXEC sp_changemergearticle
@.publication = @.publication,
@.article = @.article,
@.property = N'subset_filterclause',
@.value = N'AuditorID = CONVERT(int,HOST_NAME())',
@.force_invalidate_snapshot = 1,
@.force_reinit_subscription = 1;
GO
This was done manually in SQL server mgmt studio, right? Can i do it on my c# application on the client apps side?
Thank you everyone in advance!! I really need help on this.
Have a nice day.
Regards,
Ben
The Host_Name can be overwritten in your application code, using RMO.
The network connectivity part is unaffected.
Changing the Host_Name value via RMO only sends your specified value to the publisher, it does not change the machines actual hostname.
Parameterized filtering Question - Merge Replication
Hi everyone~!,
I would like to ask some help from you guys regarding parameterized filtering for merge replication from SQL server 2005 as publisher and sql server 2005 Compact Edition as subscriber.
I search through the forum for similar problems but i still could not get it to work. I could not find more detailed solution on the web to help me solve the problem. My question are below, i really hope i can find some help here. Thank you.
1. As far as i know, the only way to specify parameterized filtering is to use SUSER_SNAME and SHOST_NAME. I do not want to use sqlserver login name for filtering, so SUSER_SNAME is already out. I also do not intent to use MachineName so again SHOST_NAME cannot be used. But from my reading, SHOST_NAME can be overwritten. Is the hostname overwrite function done in my C# application codes?
2. If i overwrite the hostname, will the machinename on that particular pc changed affecting the pc network connectivity?
3. From this article http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1070402&SiteID=1.
run the following sql inside SQL Server,
DECLARE @.publication AS sysname;
DECLARE @.article AS sysname;
SET @.publication = N'HASSystem';
SET @.article = N'Audit';
USE [HASSystem]
EXEC sp_changemergearticle
@.publication = @.publication,
@.article = @.article,
@.property = N'subset_filterclause',
@.value = N'AuditorID = CONVERT(int,HOST_NAME())',
@.force_invalidate_snapshot = 1,
@.force_reinit_subscription = 1;
GO
This was done manually in SQL server mgmt studio, right? Can i do it on my c# application on the client apps side?
Thank you everyone in advance!! I really need help on this.
Have a nice day.
Regards,
Ben
The Host_Name can be overwritten in your application code, using RMO.
The network connectivity part is unaffected.
Changing the Host_Name value via RMO only sends your specified value to the publisher, it does not change the machines actual hostname.
Subscribe to:
Posts (Atom)