June 1, 2011 at 2:18 pm
Can anyone refer me to general information on publishing and subscriptions? We have two different databases sitting on the same SQLServer instance, one of the DB's needs to send information to the other. Nothing fancy just pull the information, send it and overwrite whatever existing records are already there.
June 1, 2011 at 2:21 pm
What you want is most likely transactional or snapshot replication, depending on if you want it real time or scheduled.
Intro: http://msdn.microsoft.com/en-us/library/ms151198.aspx
Deployment walkthrough (longish, multiple links): http://msdn.microsoft.com/en-us/library/bb677158.aspx
That should get you started.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 1, 2011 at 2:30 pm
So I'm assuming that the records that exist in the parent DB can simply overwrite their counterparts in the child DB leaving any records unique to the child DB alone?
June 1, 2011 at 2:45 pm
david.holley (6/1/2011)
So I'm assuming that the records that exist in the parent DB can simply overwrite their counterparts in the child DB leaving any records unique to the child DB alone?
Ah, didn't realize that was part of your requirements. I should have asked if you intended to have additional columns or rows in the other DB not originating from the primary.
I'd have to research, been a bit. I *think* so, but not sure enough to say go for it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 1, 2011 at 2:58 pm
Ok, not off topic, but addressing the larger issue and the reason for the original post...
The underlying problem is that I initially added a trigger on the table in the parent DB to automatically INSERT/UPDATE records in the child. The trigger works when I execute it under my I-AM-GOD user id. The end users are not able to execute the function and are given the error message 'The server principal is not able to access the database [db] under the current security context.'
The child DB uses and AD group to manage access to it. I have confirmed that her login is a member of the group.
What should I look at to resovle the issue? Right now, I'm just dealing with one person entering the data, however eventually that may be expanded to up to 10 people.
June 1, 2011 at 3:01 pm
The Trigger would need to go through a linked server that way. Let the linked server impersonate a valid user on the foreign DB. That's the quick fix. Still tossing a few ideas around in my head about the best approach.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 1, 2011 at 5:00 pm
My limited knowledge of SQL Server security somewhat grasps that concept given impersonation with ASP.NET. So I believe I understand the concept that doesn't matter who in the parent is executing the INSERT/UPDATE what matters is the child database asking the question 'Are you authorized to dig around in my data?'.
I have thought about just taking the trigger off, putting into a stored procedure and then running it as a job. Even if its just run once a day, the changes aren't that frequent that a 24 hr delay would cause any problems. Plus the data stabilizes as we get closer to the event (the db is related to event management).
June 1, 2011 at 5:25 pm
david.holley (6/1/2011)
My limited knowledge of SQL Server security somewhat grasps that concept given impersonation with ASP.NET. So I believe I understand the concept that doesn't matter who in the parent is executing the INSERT/UPDATE what matters is the child database asking the question 'Are you authorized to dig around in my data?'.
Correct. There's a tab in the linked server dedicated to security and impersonation.
I have thought about just taking the trigger off, putting into a stored procedure and then running it as a job. Even if its just run once a day, the changes aren't that frequent that a 24 hr delay would cause any problems. Plus the data stabilizes as we get closer to the event (the db is related to event management).
Not a bad idea, and that would keep you from failing transactions if the other instance isn't available for some reason. I usually use service broker queues for things like this because of the transaction failure possibilities. However, the proc would require you to keep track of your own deltas.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 3, 2011 at 5:53 am
The one nice thing about this is that all of the records in the child that exist in the parent can be updated en masse regardless if there's an actual change or not.
If I could go back to a year ago, I wouldn't have gone with the design that I did.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply