July 7, 2008 at 12:47 pm
I have a production and a standby SQL server. I cannot use Replication, Log Shipping or Mirroring because we do not meet the criteria for these technologies.
is there a script or tool I could use that compares the syslogins table from production to standby and if a user ID is deleted in production it can be deleted off of standby.
Kinda of a manual patch work of how replication works...
Thanks
July 7, 2008 at 1:25 pm
I am not aware of one, but you could certainly write one.
i would suggest creating DDL Login Events triggers/event notifications on the production box. when one of these fires, use a linked server connection to execute the same change on the standby box.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 7, 2008 at 3:01 pm
Thanks that is an idea but I don't want to add triggers to a 3rd party software due to unforeseen problems adding to their proprietary code...
July 7, 2008 at 3:19 pm
These are server level triggers.
Does this 3rd party software provide/control a database or the whole server? While possible, that would be unusual. 3rd party software usually only provides/controls a database. You are usually free to add other databases and to manage the server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 8, 2008 at 2:03 pm
I wouldn't use a linked server connection with a DDL trigger. If there is an issue with the connection, the whole transaction will roll back. Probably better to do this through an asynchronous process. Have a DDL trigger, but have it drop the login name and SID into a holding table. Have a second process periodically check that table and then delete the login, if necessary, on the second server. Once it verifies the login has been deleted, remove it from the holding table. This could be scheduled using SQL Server Agent or the like.
K. Brian Kelley
@kbriankelley
July 8, 2008 at 3:45 pm
Brian:
True, but you could buffer it with a service broker queue. Which sort of is an asynchronous process, I guess. Of course your method of polling from the target works too and is probably simpler, assuming that the polling period latency is not a concern to them.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply