How to make an exact database duplicate, including replication

Hi
Yesterday I posted a question regarding our problem with our replication
being corrupted with time:
http://msdn.microsoft.com/newsgroups...8-8be420041185
While waiting for repsonse on this thread I would like to try some rougher
methods to find out what is going on, however I do not want to do this on our
live production server.
As we do not know the cause of the problem except that it manifests itself
and grows worse after long periods of heavy usage, we cannot recreate it
properly on our test servers.
Thus I would like to make a complete backup of a faulty database (including
corrupt replication and all) for us to dissect and analyze. Is this possible,
without halting the production server and ghosting it?
Normal backups of the database will not reproduce the error. Neither will I
get the error if I also use the methods for scripting out the replication and
applying it to the new server.
Does anyone know of a way to make an identical backup of a replicating
database?
Thank you
/Kjell
Besides what you have done, the only other option is to ghost the machine.
The question being can you even reproduce it on a test system? If it is
related to heavy, sustained usage, can you reproduce your poduction load as
well as query patterns on a test server? There seems to be something else
going on in here than just replication.
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.
"Mirtul" <Mirtul@.discussions.microsoft.com> wrote in message
news:F0458481-DF34-4B23-88E1-F44C09731121@.microsoft.com...
> Hi
> Yesterday I posted a question regarding our problem with our replication
> being corrupted with time:
> http://msdn.microsoft.com/newsgroups...8-8be420041185
> While waiting for repsonse on this thread I would like to try some rougher
> methods to find out what is going on, however I do not want to do this on
> our
> live production server.
> As we do not know the cause of the problem except that it manifests itself
> and grows worse after long periods of heavy usage, we cannot recreate it
> properly on our test servers.
> Thus I would like to make a complete backup of a faulty database
> (including
> corrupt replication and all) for us to dissect and analyze. Is this
> possible,
> without halting the production server and ghosting it?
> Normal backups of the database will not reproduce the error. Neither will
> I
> get the error if I also use the methods for scripting out the replication
> and
> applying it to the new server.
> Does anyone know of a way to make an identical backup of a replicating
> database?
> Thank you
> /Kjell
|||Hi Michael
Thank you for the answer.
We do have some automatic testing of updating client databases and
synchronizing afterwards. Though it seems that those tests are not enough, or
they are missing some important aspect, because they cannot replicate the
problem for us. The only databases that are afflicted, are those that have
been running for an extended time with many clients.
As the problem disappears when the replication is recreated we suspect that
there is something going on in the MSMerge tables that causes every client to
receive multiple updates but we cannot put our fingers on it. Just recreating
the snapshot won't solve the problem, the whole replication must be replaced.
Anyone know if there is a good description for how SQL2005 uses the tables
for its replication? Step by step from matching the client with generation
id, retrieveing snapshot and applying changes. If we find out what rows are
causing the multiple updates it should be easier figuring out what is causing
the corrupted rows.
/Kjell
"Michael Hotek" wrote:

> Besides what you have done, the only other option is to ghost the machine.
> The question being can you even reproduce it on a test system? If it is
> related to heavy, sustained usage, can you reproduce your poduction load as
> well as query patterns on a test server? There seems to be something else
> going on in here than just replication.
> --
> 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.
> "Mirtul" <Mirtul@.discussions.microsoft.com> wrote in message
> news:F0458481-DF34-4B23-88E1-F44C09731121@.microsoft.com...
>
>
|||Actually, it is all very well documented or at least as documented as it
gets. The replication engine does 100% of its work using triggers and
stored procedures. So you can trace the trigger code along with the stored
proc code to trace the full calculation path that a piece of data takes.
There isn't anything else that is published on the subject.
Now to narrow it down, the merge engine transfers batches. It tags these
batches by a generation number. By using MSmerge_genhistory, you can
determine the batches which exist on a particular server that don't exist on
another server. You can then take those values to the MSmerge_contents
table to pick up the rowguid and article ID. Those can then be used to
target specific rows in tables.
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.
"Mirtul" <Mirtul@.discussions.microsoft.com> wrote in message
news:901E0AEE-CDB0-4F95-97DE-5C2AD69C4A2A@.microsoft.com...[vbcol=seagreen]
> Hi Michael
> Thank you for the answer.
> We do have some automatic testing of updating client databases and
> synchronizing afterwards. Though it seems that those tests are not enough,
> or
> they are missing some important aspect, because they cannot replicate the
> problem for us. The only databases that are afflicted, are those that have
> been running for an extended time with many clients.
> As the problem disappears when the replication is recreated we suspect
> that
> there is something going on in the MSMerge tables that causes every client
> to
> receive multiple updates but we cannot put our fingers on it. Just
> recreating
> the snapshot won't solve the problem, the whole replication must be
> replaced.
> Anyone know if there is a good description for how SQL2005 uses the tables
> for its replication? Step by step from matching the client with generation
> id, retrieveing snapshot and applying changes. If we find out what rows
> are
> causing the multiple updates it should be easier figuring out what is
> causing
> the corrupted rows.
> /Kjell
> "Michael Hotek" wrote:

how to locate the exact user name who owns a db object

hi all,
In sql 2000, how can you find out the exact user name who owns/created a db
object? They are generally recorded as 'dbo' in the sysobjects table, how
can we find out the specific user login name behind the 'dbo' entry?
many thanks,
JJThe 'dbo' user in a database is a special user that maps to the login
who owns the database (usually the login who initially created the
database). The system stored proc "exec sp_helpdb '<dbname>'" will tell
you who the owner of the database is and that login will be the one that
maps to the dbo user in the database.
Other, less Microsoft approved, ways of finding this info would be:
* "select * from master.dbo.sysdatabases" (the sid column is the
login that owns a given database, ie. that maps to the dbo user in
that database, and you can join that to master.dbo.syslogins to
get more info about that login)
* "select * from <dbname>.dbo.sysusers" (the dbo user in the
database is always uid 1; the sid column in that table will map
back to the master.dbo.syslogins table to tell you who owns the
database...unless the database user is an orphaned user (the sid
doesn't map back to any row in master.dbo.syslogins) which often
happens when you restore DBs from other servers because the other
server has different data in its master.dbo.syslogins table; this
can be corrected with sp_change_users_login)
* You could use the SUSER_SNAME() function with the sysusers table
like this:
select SUSER_SNAME(sid) from <dbname>.dbo.sysusers where uid = 1
Bear in mind, not every object in a DB has to be owned by the dbo user,
although this is quite normal. To find out which DB user owns a
specific object in the database you can use the OBJECTPROPERTY()
function like this:
select USER_NAME(OBJECTPROPERTY(OBJECT_ID('MyTa
ble'),'OwnerId'))
However, sp_helpdb is probably the easiest. Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
JJ Wang wrote:

>hi all,
>In sql 2000, how can you find out the exact user name who owns/created a db
>object? They are generally recorded as 'dbo' in the sysobjects table, how
>can we find out the specific user login name behind the 'dbo' entry?
>many thanks,
>JJ
>|||JJ,
I think that would need to be accomplished by using a source control system
like Visual SourceSafe as many logins may have the ability to have dbo be
the owner of an object.
HTH
Jerry
"JJ Wang" <JJWang@.discussions.microsoft.com> wrote in message
news:73FC551C-3600-4E8F-8B37-B2BB9E890879@.microsoft.com...
> hi all,
> In sql 2000, how can you find out the exact user name who owns/created a
> db
> object? They are generally recorded as 'dbo' in the sysobjects table, how
> can we find out the specific user login name behind the 'dbo' entry?
> many thanks,
> JJ|||Hi,
the only wat i think for that is too tell your developers/DBAs to use full
name while creating db / objects .
Regards