One of my clients is using an SQL Server 2000 database provided by a
third party. I attempted to setup transactional replication, but this
failed as a number of their tables do not have primary keys.
What they are really after is simply a mirrored database for reporting
purposes. So far, my position is that this cannot be done unless:-
(1) The vendor adds primary keys to the database
(2) The vendor upgrades to 2005 (and then I can use mirroring)
Notes:
A. I have discounted using snapshot replication due to the dynamic
nature of some of the tables that don't have a primary key
B. Although I can change the database schema myself to set the primary
keys, I do not want to do this as the vendor wont support the app, it
is risky and would involve a lot of work.
Are there any other solutions to this? eg third party products?
You could use log shipping in standby mode for this if you can control when
the reports are accessed. The main problem is that the restore of the logs
will drop all user connections, but in some cases this can be manageable.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul
I discounted log shipping as the interface is via the web and we
cant/dont want to drop user connections.
On Jan 24, 11:14 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> You could use log shipping in standby mode for this if you can control when
> the reports are accessed. The main problem is that the restore of the logs
> will drop all user connections, but in some cases this can be manageable.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.
|||In that case I'd upgrade to SQL Server 2005 and use mirroring/database
snapshots. Can't see another option as the requirements are incompatible
with current options
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||You mention manageable regarding the user connections, I see a couple
of options here:
(1) More frequent logs (eg 15 mins). This would minimise the risk of
getting disconnected as applying the transactions would be very quick
AND/OR (2) Build a level of fault tolerance into the s/w so that if
they get the message regarding disconnected because of the transaction
log being applied, the s/w retries.
Is this the sort of thing you were meaning?
On Jan 25, 5:22 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> In that case I'd upgrade to SQL Server 2005 and use mirroring/database
> snapshots. Can't see another option as the requirements are incompatible
> with current options
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.
|||I was really thinking of latency. If you can allow say a day's latency, you
could ship the logs each evening during the out of hours time and this way
keep things going.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks so much for your feedback Paul, I really appreciate it.
Unfortunately, the day's latency is unacceptable by the client. I did
try that initially but with no luck. They need data to be no more than
30mins late.
So given that log-shipping is the only possible alternative for SQL
Server 2000, I need to give the developers/query writers some guidance
on how they need to develop in this regime. So far, what I have been
thinking was:
(1) When developing SQL queries don't develop using the server that
uses log shipping as you will frequently lose your connection.
(2) In the application, do not keep keep connections open
unnecessarily. Open the connection,. perform the query and close the
connection as quick as possible. I am assuming 'pooled connections'
are still the best way to go.
(3) Try and build in fault tolerance into the application, ie if you
cannot open a connection, then retry within a set period. Also, if you
get an error when executing your query, close the connection (if you
can) and retry. Finally, if you cant close the connection, then just
ignore.
(4) Keep your queries short and snappy and avoid cursors.
Is there any white paper or guidance from Microsoft on this issue or
anyone with practical experience in running an app against a database
that uses 'log shipping'?
On Jan 25, 7:11 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I was really thinking of latency. If you can allow say a day's latency, you
> could ship the logs each evening during the out of hours time and this way
> keep things going.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.
|||You could set the rowguid column that is used by replication as the
primary key - just don't make it clustered.
PromisedOyster wrote:
> One of my clients is using an SQL Server 2000 database provided by a
> third party. I attempted to setup transactional replication, but this
> failed as a number of their tables do not have primary keys.
> What they are really after is simply a mirrored database for reporting
> purposes. So far, my position is that this cannot be done unless:-
> (1) The vendor adds primary keys to the database
> (2) The vendor upgrades to 2005 (and then I can use mirroring)
> Notes:
> A. I have discounted using snapshot replication due to the dynamic
> nature of some of the tables that don't have a primary key
> B. Although I can change the database schema myself to set the primary
> keys, I do not want to do this as the vendor wont support the app, it
> is risky and would involve a lot of work.
> Are there any other solutions to this? eg third party products?
>
|||You could set the rowguid column that is used by replication as the
primary key - just don't make it clustered.
PromisedOyster wrote:
> One of my clients is using an SQL Server 2000 database provided by a
> third party. I attempted to setup transactional replication, but this
> failed as a number of their tables do not have primary keys.
> What they are really after is simply a mirrored database for reporting
> purposes. So far, my position is that this cannot be done unless:-
> (1) The vendor adds primary keys to the database
> (2) The vendor upgrades to 2005 (and then I can use mirroring)
> Notes:
> A. I have discounted using snapshot replication due to the dynamic
> nature of some of the tables that don't have a primary key
> B. Although I can change the database schema myself to set the primary
> keys, I do not want to do this as the vendor wont support the app, it
> is risky and would involve a lot of work.
> Are there any other solutions to this? eg third party products?
>
|||You could set the rowguid column that is used by replication as the
primary key - just don't make it clustered.
PromisedOyster wrote:
> One of my clients is using an SQL Server 2000 database provided by a
> third party. I attempted to setup transactional replication, but this
> failed as a number of their tables do not have primary keys.
> What they are really after is simply a mirrored database for reporting
> purposes. So far, my position is that this cannot be done unless:-
> (1) The vendor adds primary keys to the database
> (2) The vendor upgrades to 2005 (and then I can use mirroring)
> Notes:
> A. I have discounted using snapshot replication due to the dynamic
> nature of some of the tables that don't have a primary key
> B. Although I can change the database schema myself to set the primary
> keys, I do not want to do this as the vendor wont support the app, it
> is risky and would involve a lot of work.
> Are there any other solutions to this? eg third party products?
>
