Ive implemented merge replication with SQL Server and an Access Database.
SQL Server is the publisher and Access database is the subscriber.
I have ONE table in the schema that can be updated in both the subscriber
side as well as the publisher side. I have an IDENTITY column in this table
and I have set the property "Automatic Identity Range Handling" ON for this
table.
Ive also created a script to create this publisher. Now, heres my problem.
When I create the publisher, I get a warning message thats stated below:
"Warning: only Subscribers running SQL Server 2000 can synchronize with
publication '<database>' because automatic identity ranges are being used."
OK, Ive done some resonable testing on this statement and it SEEMS that
synchronization with Automatic Identity Range Handing enabled DOES work for
Access databases as well even though the warning states otherwise! Im using
JET to connect to access and not the desktop engine. Once the threshold is
reached, and after the merge agent kicks in, the indentity seed is reset to
the appropriate values on both the access and the sql server side.
Appropriate check constraints are also placed.
I just want to make sure if this is correct - and this warning is just
bogus.
Thanks,
Girish
Hi Girish,
From your descriptions, I understood that you would like to know whether it
matters when warning is shown for "automatic identity ranges are being
used". Have I understood you? If there is anything I misunderstood, please
feel free to let me know.
Based on my scope, there are some known issues for us by using automatic
identity ranges. You could have a view on them
BUG: Unable to Change Identity Range of Publisher If You Use Auto Identity
Range
http://support.microsoft.com/default...b;en-us;310540
BUG: Identity Range Not Adjusted on Publisher When Merge Agent Runs
Continuously
http://support.microsoft.com/default...b;en-us;304706
Additionaly, there is a by design issue for automatic identity range based
on the descriptions in the following documents.
PRB: Automatic Identity Range Handling Is Not Correct If the Merge Agent
Runs an Insert Trigger
http://support.microsoft.com/default...b;en-us;324361
I am afraid the above all are why this warning is shown up.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Showing posts with label implemented. Show all posts
Showing posts with label implemented. Show all posts
Monday, March 19, 2012
Wednesday, March 7, 2012
Automate syncing login ids of master db at remote server
Hi All,
I was hoping for some advice about automating syncing of login ids at
remote servers. I have implemented simple log shipping and that seems
to be working fine, any ids created on the database are replicated to
the standby database without issue, however any changes to the master
database logins are not replicated.
I have looked at the DTS "transfer logins task" however there appear to
be some limitations to this.
I have also looked at several scripts (sp_help_revlogin) provided by
MS, http://www.support.microsoft.com/?id=246133, however this process
seems to be manual, and I would like this to be automated, so that
should there be any changes to logins within the master db, they are
replicated to the standby server the next time the task is run.
Similarly I would like to sync any orphaned users at the standy server.
If anyone could please provide further information I would be grateful.
Many Thanks
Andrew
The way I do this in an automated manner is completely unsupported by
Microsoft.
I create a linked server for the standby. Then alter sp_addlogin as well as
sp_changepassword and add in a call to sp_addlogin and sp_changepassword to
the local server passing the same input arguments along. This causes the
logins to be added to both instances as well as handles password changes.
It is unsupported, because I modify system objects.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"astrally2005" <andrewdritchie@.yahoo.com.au> wrote in message
news:1142333250.388701.45050@.j33g2000cwa.googlegro ups.com...
> Hi All,
> I was hoping for some advice about automating syncing of login ids at
> remote servers. I have implemented simple log shipping and that seems
> to be working fine, any ids created on the database are replicated to
> the standby database without issue, however any changes to the master
> database logins are not replicated.
> I have looked at the DTS "transfer logins task" however there appear to
> be some limitations to this.
> I have also looked at several scripts (sp_help_revlogin) provided by
> MS, http://www.support.microsoft.com/?id=246133, however this process
> seems to be manual, and I would like this to be automated, so that
> should there be any changes to logins within the master db, they are
> replicated to the standby server the next time the task is run.
> Similarly I would like to sync any orphaned users at the standy server.
> If anyone could please provide further information I would be grateful.
> Many Thanks
> Andrew
>
|||Mike,
Thanks for your help,
It is a novel approach, and ensures up to date login information.
As I understand whenever a user is created via either an application or
Enterprise manager, the sp_addlogin sp is called.
However what if the remote server is unavailable at the time that a new
user is being created on the primary db?
I am not sure if I have the skills to amend these scripts, particularly
given that they are system objects.
Thanks once again,
Andrew
|||I have continued to search the web for a solution and have found some
scripts provided by Umachandar Jayachandran at
http://www.sqlmag.com/Article/Articl...er_25710.html,
which seem to work, in that the logins ids are located within
sysxlogins table within the master db on the standby server, however
these logins are not visible under users within Enterprise Manager,
does this matter?
CREATE PROCEDURE sp_Syncronize_Logins_from_prod AS
set ANSI_NULLS OFF
set ANSI_WARNINGS OFF
DECLARE @.logins cursor
DECLARE @.name sysname, @.password sysname,
@.dbname sysname, @.language sysname,
@.sid binary(16), @.isntuser bit
SET @.logins = cursor fast_forward FOR
SELECT l.loginname, l.password, l.dbname, l.language, l.sid,
l.isntuser
FROM [server\instance].master.dbo.syslogins AS l
WHERE l.loginname IS NOT NULL
OPEN @.logins
WHILE(1=1)
BEGIN
FETCH @.logins INTO @.name, @.password, @.dbname,
@.language, @.sid, @.isntuser
IF @.@.fetch_status < 0 break
IF is_srvrolemember( 'sysadmin', @.name ) IS NOT NULL
CONTINUE
IF @.isntuser = 0
EXEC sp_addlogin @.name, @.password, @.dbname,
@.language, @.sid, 'skip_encryption'
ELSE
BEGIN
EXEC sp_grantlogin @.name
EXEC sp_defaultdb @.name, @.dbname
EXEC sp_defaultlanguage @.name, @.language
END
END
DEALLOCATE @.logins
|||They should be visible. Enterprise Manager is looking at the sysxlogins
table.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"astrally2005" <andrewdritchie@.yahoo.com.au> wrote in message
news:1142602814.147621.269160@.e56g2000cwe.googlegr oups.com...
>I have continued to search the web for a solution and have found some
> scripts provided by Umachandar Jayachandran at
> http://www.sqlmag.com/Article/Articl...er_25710.html,
> which seem to work, in that the logins ids are located within
> sysxlogins table within the master db on the standby server, however
> these logins are not visible under users within Enterprise Manager,
> does this matter?
> CREATE PROCEDURE sp_Syncronize_Logins_from_prod AS
> set ANSI_NULLS OFF
> set ANSI_WARNINGS OFF
> DECLARE @.logins cursor
> DECLARE @.name sysname, @.password sysname,
> @.dbname sysname, @.language sysname,
> @.sid binary(16), @.isntuser bit
> SET @.logins = cursor fast_forward FOR
> SELECT l.loginname, l.password, l.dbname, l.language, l.sid,
> l.isntuser
> FROM [server\instance].master.dbo.syslogins AS l
> WHERE l.loginname IS NOT NULL
> OPEN @.logins
> WHILE(1=1)
> BEGIN
> FETCH @.logins INTO @.name, @.password, @.dbname,
> @.language, @.sid, @.isntuser
> IF @.@.fetch_status < 0 break
> IF is_srvrolemember( 'sysadmin', @.name ) IS NOT NULL
> CONTINUE
> IF @.isntuser = 0
> EXEC sp_addlogin @.name, @.password, @.dbname,
> @.language, @.sid, 'skip_encryption'
> ELSE
> BEGIN
> EXEC sp_grantlogin @.name
> EXEC sp_defaultdb @.name, @.dbname
> EXEC sp_defaultlanguage @.name, @.language
> END
> END
> DEALLOCATE @.logins
>
I was hoping for some advice about automating syncing of login ids at
remote servers. I have implemented simple log shipping and that seems
to be working fine, any ids created on the database are replicated to
the standby database without issue, however any changes to the master
database logins are not replicated.
I have looked at the DTS "transfer logins task" however there appear to
be some limitations to this.
I have also looked at several scripts (sp_help_revlogin) provided by
MS, http://www.support.microsoft.com/?id=246133, however this process
seems to be manual, and I would like this to be automated, so that
should there be any changes to logins within the master db, they are
replicated to the standby server the next time the task is run.
Similarly I would like to sync any orphaned users at the standy server.
If anyone could please provide further information I would be grateful.
Many Thanks
Andrew
The way I do this in an automated manner is completely unsupported by
Microsoft.
I create a linked server for the standby. Then alter sp_addlogin as well as
sp_changepassword and add in a call to sp_addlogin and sp_changepassword to
the local server passing the same input arguments along. This causes the
logins to be added to both instances as well as handles password changes.
It is unsupported, because I modify system objects.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"astrally2005" <andrewdritchie@.yahoo.com.au> wrote in message
news:1142333250.388701.45050@.j33g2000cwa.googlegro ups.com...
> Hi All,
> I was hoping for some advice about automating syncing of login ids at
> remote servers. I have implemented simple log shipping and that seems
> to be working fine, any ids created on the database are replicated to
> the standby database without issue, however any changes to the master
> database logins are not replicated.
> I have looked at the DTS "transfer logins task" however there appear to
> be some limitations to this.
> I have also looked at several scripts (sp_help_revlogin) provided by
> MS, http://www.support.microsoft.com/?id=246133, however this process
> seems to be manual, and I would like this to be automated, so that
> should there be any changes to logins within the master db, they are
> replicated to the standby server the next time the task is run.
> Similarly I would like to sync any orphaned users at the standy server.
> If anyone could please provide further information I would be grateful.
> Many Thanks
> Andrew
>
|||Mike,
Thanks for your help,
It is a novel approach, and ensures up to date login information.
As I understand whenever a user is created via either an application or
Enterprise manager, the sp_addlogin sp is called.
However what if the remote server is unavailable at the time that a new
user is being created on the primary db?
I am not sure if I have the skills to amend these scripts, particularly
given that they are system objects.
Thanks once again,
Andrew
|||I have continued to search the web for a solution and have found some
scripts provided by Umachandar Jayachandran at
http://www.sqlmag.com/Article/Articl...er_25710.html,
which seem to work, in that the logins ids are located within
sysxlogins table within the master db on the standby server, however
these logins are not visible under users within Enterprise Manager,
does this matter?
CREATE PROCEDURE sp_Syncronize_Logins_from_prod AS
set ANSI_NULLS OFF
set ANSI_WARNINGS OFF
DECLARE @.logins cursor
DECLARE @.name sysname, @.password sysname,
@.dbname sysname, @.language sysname,
@.sid binary(16), @.isntuser bit
SET @.logins = cursor fast_forward FOR
SELECT l.loginname, l.password, l.dbname, l.language, l.sid,
l.isntuser
FROM [server\instance].master.dbo.syslogins AS l
WHERE l.loginname IS NOT NULL
OPEN @.logins
WHILE(1=1)
BEGIN
FETCH @.logins INTO @.name, @.password, @.dbname,
@.language, @.sid, @.isntuser
IF @.@.fetch_status < 0 break
IF is_srvrolemember( 'sysadmin', @.name ) IS NOT NULL
CONTINUE
IF @.isntuser = 0
EXEC sp_addlogin @.name, @.password, @.dbname,
@.language, @.sid, 'skip_encryption'
ELSE
BEGIN
EXEC sp_grantlogin @.name
EXEC sp_defaultdb @.name, @.dbname
EXEC sp_defaultlanguage @.name, @.language
END
END
DEALLOCATE @.logins
|||They should be visible. Enterprise Manager is looking at the sysxlogins
table.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"astrally2005" <andrewdritchie@.yahoo.com.au> wrote in message
news:1142602814.147621.269160@.e56g2000cwe.googlegr oups.com...
>I have continued to search the web for a solution and have found some
> scripts provided by Umachandar Jayachandran at
> http://www.sqlmag.com/Article/Articl...er_25710.html,
> which seem to work, in that the logins ids are located within
> sysxlogins table within the master db on the standby server, however
> these logins are not visible under users within Enterprise Manager,
> does this matter?
> CREATE PROCEDURE sp_Syncronize_Logins_from_prod AS
> set ANSI_NULLS OFF
> set ANSI_WARNINGS OFF
> DECLARE @.logins cursor
> DECLARE @.name sysname, @.password sysname,
> @.dbname sysname, @.language sysname,
> @.sid binary(16), @.isntuser bit
> SET @.logins = cursor fast_forward FOR
> SELECT l.loginname, l.password, l.dbname, l.language, l.sid,
> l.isntuser
> FROM [server\instance].master.dbo.syslogins AS l
> WHERE l.loginname IS NOT NULL
> OPEN @.logins
> WHILE(1=1)
> BEGIN
> FETCH @.logins INTO @.name, @.password, @.dbname,
> @.language, @.sid, @.isntuser
> IF @.@.fetch_status < 0 break
> IF is_srvrolemember( 'sysadmin', @.name ) IS NOT NULL
> CONTINUE
> IF @.isntuser = 0
> EXEC sp_addlogin @.name, @.password, @.dbname,
> @.language, @.sid, 'skip_encryption'
> ELSE
> BEGIN
> EXEC sp_grantlogin @.name
> EXEC sp_defaultdb @.name, @.dbname
> EXEC sp_defaultlanguage @.name, @.language
> END
> END
> DEALLOCATE @.logins
>
Subscribe to:
Posts (Atom)