Primary Keys with Transactional Replication

I am pretty new to replication and have been setting it up in a test
environment using the test databases delivered during the sql install
(northwind and pubs).
I noticed that when I would setup Transactional Replicational (NON –
updateable subscriber) that the primary keys would NOT come over with tables
to the subscriber. But, if I set up Transactional Replication with
Updateable Subscriber, the primary keys would come over with the tables on
the subscriber. Am I missing something here? Or, is this indeed how it
works?
Hi Janet,
As Paul mentioned, transactional replication typically (or traditionally)
replicates the primary key as just a unique index. Assuming that you are
using a SQL2000 publisher, you can enable the 0x8000 (PKUKAsContraints)
article schema option so primary key will be replicated as primary key. The
behavior that you saw for updateable subscriber was our attempt to
"out-smart" the user as updateable subscriptions requires primary key
constraint (not just the index) at the subscriber to work properly.
-Raymond
"Janet" <Janet@.discussions.microsoft.com> wrote in message
news:A81443F2-9BEF-40B2-9236-3D91DD44D9BC@.microsoft.com...
>I am pretty new to replication and have been setting it up in a test
> environment using the test databases delivered during the sql install
> (northwind and pubs).
> I noticed that when I would setup Transactional Replicational (NON -
> updateable subscriber) that the primary keys would NOT come over with
> tables
> to the subscriber. But, if I set up Transactional Replication with
> Updateable Subscriber, the primary keys would come over with the tables on
> the subscriber. Am I missing something here? Or, is this indeed how it
> works?
>
>

Primary Key defined during Replication

Does Microsoft SQL 2000 Replication require that a primary key be defined on
all tables to be replicated between databases? We are planning on using
Replication to create a near real-time reporting database, based upon our
production database records.
Thanks
Vilma J.
For transactional replication, yes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vilma Johnson" <admin@.myself.org> wrote in message
news:9371ECDD-5A80-41A9-94A4-F4A70B855CB9@.microsoft.com...
> Does Microsoft SQL 2000 Replication require that a primary key be defined
> on
> all tables to be replicated between databases? We are planning on using
> Replication to create a near real-time reporting database, based upon our
> production database records.
> Thanks
> --
> Vilma J.

Primary Database Backup in Log Shipping Scenario

I've setup a Log Shipping scenario for one of our databases. This works fine.
Also, every night a full database backup of the primary database is made to a
disk location. Normally, the transaction log would be truncated after a full
database backup. This behavior is unwanted in a Log Shipping scenario since
the content of the transaction log needs to be applied to the secondary
database. We've seen that since the Log Shipping scenario is in place, the
log is no longer truncated after the full database backup of the primary
database. How does the primary database "know" not to truncate the
transaction log? In other words, I'd like to know how the full database
backup mechanism works in a Log Shipping environment.
Are you saying you with issue a backup log with truncate_only statement
after your backup has finished?
ie
backup log fulltext with truncate_only
If so, this will break your log shipping chain and should not be done.
The way a log shipping works is a backup is done. Embedded in the backup is
the LSN (Log Sequence Number) for the transaction log. The next log backup
you do contains log enteries starting at the lsn for this last backup. So
you can restore the log to the database backup where the lsn matches the lsn
embedded in the backup.
If you then deploy log shipping and do a backup the backup entry is in the
log but it is ignored, so you can continue to apply subsequent log dumps
without breaking the chain.
Does this answer your question?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wilbert" <Wilbert@.discussions.microsoft.com> wrote in message
news:542507D8-50F4-4CEC-BEE5-36DF1EBBA607@.microsoft.com...
> I've setup a Log Shipping scenario for one of our databases. This works
> fine.
> Also, every night a full database backup of the primary database is made
> to a
> disk location. Normally, the transaction log would be truncated after a
> full
> database backup. This behavior is unwanted in a Log Shipping scenario
> since
> the content of the transaction log needs to be applied to the secondary
> database. We've seen that since the Log Shipping scenario is in place, the
> log is no longer truncated after the full database backup of the primary
> database. How does the primary database "know" not to truncate the
> transaction log? In other words, I'd like to know how the full database
> backup mechanism works in a Log Shipping environment.
|||Hello Hilary,
I'll clarify my question: I'd like to know how SQL Server "knows" whether a
Log Shipping scenario is in place, and I'd like an explanation to the
subsequent difference in behavior when performing a Full Database backup
(truncate the log in "normal" operation and not truncating the log in a Log
Shipping scenario).
Best regards, Wilbert
|||SQL Server does not know if a log is being shipped or not. Log shipping fits
into the existing point in time recovery scheme that sql server uses to
recover databases.
If you truncate a log it will remove all committed entries in that log.
Logged open transaction and other non-committed commands will remain in the
log since the last checkpoint. There is no log truncation after a back up in
full recovery model, not in bulk logged. The problem with bulk logged is
that minimally logged operations are minimally logged and will destroy your
log shipping chain.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wilbert" <Wilbert@.discussions.microsoft.com> wrote in message
news:00B5F549-71CE-4089-A81A-A537B6BA212F@.microsoft.com...
> Hello Hilary,
> I'll clarify my question: I'd like to know how SQL Server "knows" whether
> a
> Log Shipping scenario is in place, and I'd like an explanation to the
> subsequent difference in behavior when performing a Full Database backup
> (truncate the log in "normal" operation and not truncating the log in a
> Log
> Shipping scenario).
> Best regards, Wilbert
|||Hello Hilary,
Isn't it so that after a full database backup the committed entries in the
log are removed?
That's the origin of my question: in "normal" operation these entries are
removed from the log, while in a log shipping scenario this doesn't seem to
be the case. This led me to believe that SQL Server somehow "know" that log
shipping is taking place and that the entries in the log should be retained
until the next log backup.
Best regards, Wilbert
|||No, the log is insert and read only. What happens is that it is a record of
all of your database activity. Before anything hits disk it is written to
your log. When you issue a checkpoint the checkpoint is written to the log
and all dirty pages (including uncommitted pages are written to disk).
Should a failure occur the log is consulted to find the last checkpoint and
uncommitted transactions which occurred after the last checkpoint are
removed from disk and committed transactions which committed after the last
checkpoint are written to disk.
When you do a db backup nothing happens to the log other than the backup
happened. When you do a log backup, the internal vlf's in the log buffer I
believe have their status changed to 0 if there are no active transactions
in them. But the log itself does not have anything happen to it.
The above applies to full recovery model.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wilbert" <Wilbert@.discussions.microsoft.com> wrote in message
news:668C79A5-01E2-4336-94D9-EB76B8D23866@.microsoft.com...
> Hello Hilary,
> Isn't it so that after a full database backup the committed entries in the
> log are removed?
> That's the origin of my question: in "normal" operation these entries are
> removed from the log, while in a log shipping scenario this doesn't seem
> to
> be the case. This led me to believe that SQL Server somehow "know" that
> log
> shipping is taking place and that the entries in the log should be
> retained
> until the next log backup.
> Best regards, Wilbert

Primary and linking keys on SQL server tables

I've been designing SQL server databases for a while and have always
assumed that primary keys and linking keys should be numbers rather
than textual fields to improve performance. As an example, if i were
to create a new table i would put in a surrogate primary key that
autoincrements.

I've recently been challenged on this and cannot seem to find any
evidence that would support the view that this is best practice.

Anybody have opinions, or know of articles that provide any evidence
either way?"chris p reynolds" <chrispreynolds@.hotmail.com> wrote in message
news:78ba33c4.0404210124.60a744c9@.posting.google.c om...
> I've been designing SQL server databases for a while and have always
> assumed that primary keys and linking keys should be numbers rather
> than textual fields to improve performance. As an example, if i were
> to create a new table i would put in a surrogate primary key that
> autoincrements.
> I've recently been challenged on this and cannot seem to find any
> evidence that would support the view that this is best practice.
> Anybody have opinions, or know of articles that provide any evidence
> either way?

http://www.winnetmag.com/Articles/P...?ArticleID=5113

Simon

Price of using multiple databases in SELECT statements

Hi,

We are discussing possible implementation for sql2005 database(s). This database will serve one web portal. Part of data will get into it by hand, and part will be replicated from internal system.

Some of us are for creating two separate databases, since there are two separate datasources. One, automatic, will change very little over time and requires almost no maintenance. Other datasource will be manual input. Tables and procedures related to this part will change over time.

Some of us are for creating single database, since it will serve one web site. More important this group is concerned about performance issues since almost every select will require join between tables that would be stored in two separate databases. Do these issues exist?

Can you share some insights, comments, links about this?

Hi,

We are using multiple databases in our many application, and have not found an excuse so far that we should terminate that practice. Most of our modules gather data from different databases and operate on them. I think a little performance overhead is there, which can be ignored.

As every approach has its pros and cons. But we think that this segregation provides us opportunity to

- encapsulate different type of data in separate locations,

- which can be backed up easily,

- can be distributed at different locations,

- due to lesser size data would be fetched rather fast,

- unorganized data could be separaterd.

Whats your take on it?

|||

We too use multiple databases (on the same server, of course) and join across them -- there is no performance penalty that we've ever seen.

In our situation, we have a vendor package around which we've written an extended application. The vendor package uses one database and our extension uses another, but the two are intimately linked and we have views in the extention database pointing back to the vendor packages database (making it very easy to code. I recommend that you too use views to make it appear as though it's one big database.