Filter data back to publisher

Hi all,

We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?

Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?

Thanks,

Iain

Hey lain,

You can't selectively edit what data gets uploaded back to the publisher. There's a parameter that you can use in sp_addmergearticle @.subscriber_upload_options which you can set to (0,1,2) NO restrictions, changes are allowed at sub but not uploaded to the publisher , and last option doesn't allow any changed at the subscriber respectively. So you can either upload all the changes or none of the changes, but you can't filter out the data to be uploaded.

There's a workaround but that's not recommended. You can enable the insert triggers on the data you want to upload to the publisher, and then disable the insert triggers on the data you want to filter out. By doing that merge agent won’t detect those changes and they won't be uploaded to the publisher.

hope that helps

bishoyy

|||

Ok, thanks. Looks like if they want to implement their requirement they will just have to become the publisher. I think messing the the triggers will just lead to all sorts of issues and problems that I could well do without!

Thanks for the help.

Filter data back to publisher

Hi all,

We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?

Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?

Thanks,

Iain

Hey lain,

You can't selectively edit what data gets uploaded back to the publisher. There's a parameter that you can use in sp_addmergearticle @.subscriber_upload_options which you can set to (0,1,2) NO restrictions, changes are allowed at sub but not uploaded to the publisher , and last option doesn't allow any changed at the subscriber respectively. So you can either upload all the changes or none of the changes, but you can't filter out the data to be uploaded.

There's a workaround but that's not recommended. You can enable the insert triggers on the data you want to upload to the publisher, and then disable the insert triggers on the data you want to filter out. By doing that merge agent won’t detect those changes and they won't be uploaded to the publisher.

hope that helps

bishoyy

|||

Ok, thanks. Looks like if they want to implement their requirement they will just have to become the publisher. I think messing the the triggers will just lead to all sorts of issues and problems that I could well do without!

Thanks for the help.

File placement on server with 2 instances of SQL

I have inherited a stand alone server with 2 instances of SQL installed.
There are 6 physical disks in the server which have been made into 3 mirrors.
The first mirror has the OS on it. Currently, the 2nd disk has all the
database and transaction log files from both instances of SQL.
I plan to make use of the 3rd disk. My question is: is it better to move the
database and transaction log files from the second instance to the new disk
so that all the files for the first instance are on disk 2 and all the files
for the 2nd instance are on disk 3 OR is it better to keep all the database
files from both instances on disk 2 and move all the log files for both
instances to disk 3?
I'm sure I have read somehwere that in this situation, the disks should be
separated by instance rather than seperating by file type.Seen from the databases' perspective, same or different instances doesn't really make a difference.
As usual, only testing with a realistic workload can give you a definitive answer. My guess, though,
is to have the tlog files separated.
Unless you have some pretty unusual workload. Like only reads during day and modifications during
night. And it doesn't matter that modifications take a bit longer. Then you might consider having db
1 (all db files) on one raid and db 2 (all files) on another. That would mean that the read
operations during day would not compete between db1 and db2.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>I have inherited a stand alone server with 2 instances of SQL installed.
> There are 6 physical disks in the server which have been made into 3 mirrors.
> The first mirror has the OS on it. Currently, the 2nd disk has all the
> database and transaction log files from both instances of SQL.
> I plan to make use of the 3rd disk. My question is: is it better to move the
> database and transaction log files from the second instance to the new disk
> so that all the files for the first instance are on disk 2 and all the files
> for the 2nd instance are on disk 3 OR is it better to keep all the database
> files from both instances on disk 2 and move all the log files for both
> instances to disk 3?
> I'm sure I have read somehwere that in this situation, the disks should be
> separated by instance rather than seperating by file type.
>|||+1 to both of Tibor's sentiments.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e7sqUlyXIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Seen from the databases' perspective, same or different instances doesn't
> really make a difference. As usual, only testing with a realistic workload
> can give you a definitive answer. My guess, though, is to have the tlog
> files separated.
> Unless you have some pretty unusual workload. Like only reads during day
> and modifications during night. And it doesn't matter that modifications
> take a bit longer. Then you might consider having db 1 (all db files) on
> one raid and db 2 (all files) on another. That would mean that the read
> operations during day would not compete between db1 and db2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stu" <Stu@.discussions.microsoft.com> wrote in message
> news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>>I have inherited a stand alone server with 2 instances of SQL installed.
>> There are 6 physical disks in the server which have been made into 3
>> mirrors.
>> The first mirror has the OS on it. Currently, the 2nd disk has all the
>> database and transaction log files from both instances of SQL.
>> I plan to make use of the 3rd disk. My question is: is it better to move
>> the
>> database and transaction log files from the second instance to the new
>> disk
>> so that all the files for the first instance are on disk 2 and all the
>> files
>> for the 2nd instance are on disk 3 OR is it better to keep all the
>> database
>> files from both instances on disk 2 and move all the log files for both
>> instances to disk 3?
>> I'm sure I have read somehwere that in this situation, the disks should
>> be
>> separated by instance rather than seperating by file type.
>|||I also second Tibor's response. Especially the part about the only
difinitive answer will come from testing.
And don't forget there is a third option. That's to put the data from DB1
and the log from DB2 on Disk2 and the log from DB1 and the data from DB2 on
Disk3. That might help in a situation where you have primarily reads on
both databases during the day and updates during the night and almost all
the updates are done to one database.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e7sqUlyXIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Seen from the databases' perspective, same or different instances doesn't
> really make a difference. As usual, only testing with a realistic workload
> can give you a definitive answer. My guess, though, is to have the tlog
> files separated.
> Unless you have some pretty unusual workload. Like only reads during day
> and modifications during night. And it doesn't matter that modifications
> take a bit longer. Then you might consider having db 1 (all db files) on
> one raid and db 2 (all files) on another. That would mean that the read
> operations during day would not compete between db1 and db2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stu" <Stu@.discussions.microsoft.com> wrote in message
> news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>>I have inherited a stand alone server with 2 instances of SQL installed.
>> There are 6 physical disks in the server which have been made into 3
>> mirrors.
>> The first mirror has the OS on it. Currently, the 2nd disk has all the
>> database and transaction log files from both instances of SQL.
>> I plan to make use of the 3rd disk. My question is: is it better to move
>> the
>> database and transaction log files from the second instance to the new
>> disk
>> so that all the files for the first instance are on disk 2 and all the
>> files
>> for the 2nd instance are on disk 3 OR is it better to keep all the
>> database
>> files from both instances on disk 2 and move all the log files for both
>> instances to disk 3?
>> I'm sure I have read somehwere that in this situation, the disks should
>> be
>> separated by instance rather than seperating by file type.
>

File Ownership failing for Domain Accounts

I am having a consistent problem on several instances of SQL 2005 setting
the ownership of database files to a windows user. This is not a login
problem but a file ownership problem. Here are the steps:
1. Right-click on any user database, e.g. AdventureWorks and select
"Properties
2. Select the "Files" page from the right-hand list
3. Click the elipsis "..." button to the far right of the "Owner:" label
4. Click the "Browse..." button in the pop-up "Select Database Owner" window
5. Select any login that maps to a windows or domain account except for
members of the [NT AUTHORITY] group, e.g. [COMPANYDOMAIN\MyName]
6. Click "OK" on the Browse pop-up and then on the Select Database Owner
pop-up
7. Click "OK" on the "Database Properties" dialog to return to the main SQL
Server Management Studio application
8. Repeat steps 1 & 2
9. Notice that the "Owner:" field is now blank
This problem is occuring on multiple instances of SQL Server 2005 at both
SP1 and SP2 patch levels. All of the instances are set to mixed security
mode. All of the windows accounts I have tried are in the local server's
Adminstrators group. Setting ownership to a pure SQL account or a member of
the local [NT AUTHORITY] group works just fine. I have even attempted to
set
the permissions manually using ALTER AUTHORIZATION and the sp_changedbowner
proc. Nothing seems to work and I never get an error message or log.The procedure you describe changes the database owner and is unrelated to
file ownership. IMHO, the functionality is misplaced in the SSMS GUI; it
should be under the 'general' instead of 'files'.
I can't seem to reproduce the problem under SP2. Have you tried closing and
re-opening the object explorer? Is the correct owner reported using the
methods below?
EXEC sp_helpdb
SELECT
name AS database_name,
SUSER_SNAME(owner_sid) AS database_owner
FROM sys.databases
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin D. White" <kevinDwhite@.newsgroup.nospam> wrote in message
news:ekLZFoAaHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I am having a consistent problem on several instances of SQL 2005 setting
>the ownership of database files to a windows user. This is not a login
>problem but a file ownership problem. Here are the steps:
> 1. Right-click on any user database, e.g. AdventureWorks and select
> "Properties
> 2. Select the "Files" page from the right-hand list
> 3. Click the elipsis "..." button to the far right of the "Owner:" label
> 4. Click the "Browse..." button in the pop-up "Select Database Owner"
> window
> 5. Select any login that maps to a windows or domain account except for
> members of the [NT AUTHORITY] group, e.g. [COMPANYDOMAIN\MyName]
> 6. Click "OK" on the Browse pop-up and then on the Select Database Owner
> pop-up
> 7. Click "OK" on the "Database Properties" dialog to return to the main
> SQL Server Management Studio application
> 8. Repeat steps 1 & 2
> 9. Notice that the "Owner:" field is now blank
> This problem is occuring on multiple instances of SQL Server 2005 at both
> SP1 and SP2 patch levels. All of the instances are set to mixed security
> mode. All of the windows accounts I have tried are in the local server's
> Adminstrators group. Setting ownership to a pure SQL account or a member
> of the local [NT AUTHORITY] group works just fine. I have even attempt
ed
> to set the permissions manually using ALTER AUTHORIZATION and the
> sp_changedbowner proc. Nothing seems to work and I never get an error
> message or log.
>|||Kevin
I'm also could not reproduce the problerm under SP'a'.
Have you tried Dan's suggestions?
"Kevin D. White" <kevinDwhite@.newsgroup.nospam> wrote in message
news:ekLZFoAaHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I am having a consistent problem on several instances of SQL 2005 setting
>the ownership of database files to a windows user. This is not a login
>problem but a file ownership problem. Here are the steps:
> 1. Right-click on any user database, e.g. AdventureWorks and select
> "Properties
> 2. Select the "Files" page from the right-hand list
> 3. Click the elipsis "..." button to the far right of the "Owner:" label
> 4. Click the "Browse..." button in the pop-up "Select Database Owner"
> window
> 5. Select any login that maps to a windows or domain account except for
> members of the [NT AUTHORITY] group, e.g. [COMPANYDOMAIN\MyName]
> 6. Click "OK" on the Browse pop-up and then on the Select Database Owner
> pop-up
> 7. Click "OK" on the "Database Properties" dialog to return to the main
> SQL Server Management Studio application
> 8. Repeat steps 1 & 2
> 9. Notice that the "Owner:" field is now blank
> This problem is occuring on multiple instances of SQL Server 2005 at both
> SP1 and SP2 patch levels. All of the instances are set to mixed security
> mode. All of the windows accounts I have tried are in the local server's
> Adminstrators group. Setting ownership to a pure SQL account or a member
> of the local [NT AUTHORITY] group works just fine. I have even attempt
ed
> to set the permissions manually using ALTER AUTHORIZATION and the
> sp_changedbowner proc. Nothing seems to work and I never get an error
> message or log.
>|||Thanks for the helpful suggestion. The SQL you provided does return the
values I expect after changing ownership through the "Files" page in the
database properties dialog. How strange it is then that the Owner field
remains empty even after closing and reopening SQL Management Studio. Also,
I am unable to install diagram support. Any attempt to create a diagram
yields the all too common and annoying "...this database does not have a
valid owner..." error message.
Any idea why one method of discovering ownership returns the correct value
but another method does not? This is very bizarre.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:9074E028-7EAE-4012-B0B5-F24DC8F67206@.microsoft.com...
> The procedure you describe changes the database owner and is unrelated to
> file ownership. IMHO, the functionality is misplaced in the SSMS GUI; it
> should be under the 'general' instead of 'files'.
> I can't seem to reproduce the problem under SP2. Have you tried closing
> and re-opening the object explorer? Is the correct owner reported using
> the methods below?
> EXEC sp_helpdb
> SELECT
> name AS database_name,
> SUSER_SNAME(owner_sid) AS database_owner
> FROM sys.databases
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Kevin D. White" <kevinDwhite@.newsgroup.nospam> wrote in message
> news:ekLZFoAaHHA.2316@.TK2MSFTNGP04.phx.gbl...
>|||I think I found the root cause of the problem. It looks like the admin has
setup just about every instance of SQL Server I can access to start under a
local machine account. This is preventing lookups against Active Directory.
"Kevin D. White" <kevinDwhite@.newsgroup.nospam> wrote in message
news:ekLZFoAaHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I am having a consistent problem on several instances of SQL 2005 setting
>the ownership of database files to a windows user. This is not a login
>problem but a file ownership problem. Here are the steps:
> 1. Right-click on any user database, e.g. AdventureWorks and select
> "Properties
> 2. Select the "Files" page from the right-hand list
> 3. Click the elipsis "..." button to the far right of the "Owner:" label
> 4. Click the "Browse..." button in the pop-up "Select Database Owner"
> window
> 5. Select any login that maps to a windows or domain account except for
> members of the [NT AUTHORITY] group, e.g. [COMPANYDOMAIN\MyName]
> 6. Click "OK" on the Browse pop-up and then on the Select Database Owner
> pop-up
> 7. Click "OK" on the "Database Properties" dialog to return to the main
> SQL Server Management Studio application
> 8. Repeat steps 1 & 2
> 9. Notice that the "Owner:" field is now blank
> This problem is occuring on multiple instances of SQL Server 2005 at both
> SP1 and SP2 patch levels. All of the instances are set to mixed security
> mode. All of the windows accounts I have tried are in the local server's
> Adminstrators group. Setting ownership to a pure SQL account or a member
> of the local [NT AUTHORITY] group works just fine. I have even attempt
ed
> to set the permissions manually using ALTER AUTHORIZATION and the
> sp_changedbowner proc. Nothing seems to work and I never get an error
> message or log.
>|||Hello Kevin,
Thank you for sharing your experience on resolving the issue. It shall
benefit the community.
Also, I have forwarded this issue to the product team and they may want to
check if this behavior is normal or there is any further improment in UI so
that users may get more hint on this kind of problems.
Best 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.|||Good to hear. The thing that *really* tripped me up is the fact the applying
ownership to a domain account through the UI never returned an error or
warning. Combine the lack of an error with the frankly misleading error
message when I attempt to create a diagram and the whole thing becomes this
vicious circle. The diagram support error tells me to set a valid owner, I
go and set what appears to be a valid owner, diagram support returns the
same message..etc.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:45EqxuraHHA.4088@.TK2MSFTNGHUB02.phx.gbl...
> Hello Kevin,
> Thank you for sharing your experience on resolving the issue. It shall
> benefit the community.
> Also, I have forwarded this issue to the product team and they may want to
> check if this behavior is normal or there is any further improment in UI
> so
> that users may get more hint on this kind of problems.
> Best 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.
>|||Hello Kevin,
Thank you for your additional feedback and please rest this has also been
forwarded to the right channel. Your feedback is always valuable for our
product improvement!
Best 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.