I have just setup my first Transactional replication between twoservers. Next I needed to test what would happen if the live serverwent down.
I disconnected the live server and connected it to the backup server(doing all the ip setup bits as well). Then I tried running my web app.
The web app ran great. I can look at the data that was replicated. Myproblem is that I cannot seem to add any data. Ever time I try to run astored procedure, the web app crashes with 'sa' account login failed. Itried running the stored procedures through SQL Query Analyzer, and itsays the same thing.
I know there is no problem with the sa account, as I can view the datausing this. There are no permissions set on any of the objects in thedatabase. The only thing I can think of is that the replication hassomehow disabled the ability to add data to the replicated database.
Can anyone point out what is happening?
Foundan MSDN article which explains why this is happening. Solve the problem.
Showing posts with label transactional. Show all posts
Showing posts with label transactional. Show all posts
Tuesday, February 21, 2012
Replication: Transactional: Multi Publications, One Subscriber.
Greetings All, I was hoping that a replication sage might be able to
answer a question for me.
answer a question for me.
I want to have one subscriber subscribing to N publishers. I
essentially have a company that has a main headquarters and three
satellite offices. I want each of the satellite offices to push their
data up to the master database. From what I have read it seems like
this should not be a problem. Some questions that come to mind are:
1.)Does the master need to be read only or can it be configured to be
updateable as well?
2.)Can the distribution agent on all the publishers be set to
continuously distribute or should it be staggered so as not to cause a
problem when another distribution agent is running?
3.)If the distribution is set to "delay distribution" will this cause
changes on the subscriber to be pushed out to the publishers?
In this database guid's are used as pk's so the issue of pk collisions
is not a problem.
I hope that this question is not too vague. My experience thus far
with replication has been simple one way transactional and simple
merge replication.
Regards, Louis Frolio"Louis Frolio" <froliol@.yahoo.com> wrote in message
news:94c28610.0409221536.67fda321@.posting.google.c om...
> Greetings All, I was hoping that a replication sage might be able to
> answer a question for me.
> I want to have one subscriber subscribing to N publishers. I
> essentially have a company that has a main headquarters and three
> satellite offices. I want each of the satellite offices to push their
> data up to the master database. From what I have read it seems like
> this should not be a problem. Some questions that come to mind are:
> 1.) Does the master need to be read only or can it be configured to be
> updateable as well?
> 2.) Can the distribution agent on all the publishers be set to
> continuously distribute or should it be staggered so as not to cause a
> problem when another distribution agent is running?
> 3.) If the distribution is set to "delay distribution" will this cause
> changes on the subscriber to be pushed out to the publishers?
>
> In this database guid's are used as pk's so the issue of pk collisions
> is not a problem.
> I hope that this question is not too vague. My experience thus far
> with replication has been simple one way transactional and simple
> merge replication.
> Regards, Louis Frolio
You might want to post this in microsoft.public.sqlserver.replication, where
there are probably more replication sages than in this newsgroup.
Simon|||froliol@.yahoo.com (Louis Frolio) wrote in message news:<94c28610.0409221536.67fda321@.posting.google.com>...
> Greetings All, I was hoping that a replication sage might be able to
> answer a question for me.
> I want to have one subscriber subscribing to N publishers. I
> essentially have a company that has a main headquarters and three
> satellite offices. I want each of the satellite offices to push their
> data up to the master database. From what I have read it seems like
> this should not be a problem. Some questions that come to mind are:
> 1.)Does the master need to be read only or can it be configured to be
> updateable as well?
> 2.)Can the distribution agent on all the publishers be set to
> continuously distribute or should it be staggered so as not to cause a
> problem when another distribution agent is running?
> 3.)If the distribution is set to "delay distribution" will this cause
> changes on the subscriber to be pushed out to the publishers?
>
> In this database guid's are used as pk's so the issue of pk collisions
> is not a problem.
> I hope that this question is not too vague. My experience thus far
> with replication has been simple one way transactional and simple
> merge replication.
> Regards, Louis Frolio
Q #1
> 1.)Does the master need to be read only or can it be configured to be
> updateable as well?
A. The subscriber database can be updated by users on that end, it
does not need to be read only.
Q #2
> continuously distribute or should it be staggered so as not to cause a
> problem when another distribution agent is running?
A. It can be done either way without issue.
Q #3
> 3.)If the distribution is set to "delay distribution" will this cause
> changes on the subscriber to be pushed out to the publishers?
A. No.
All, I have answered my own questions since I posted this missive.|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<4152b7ee_1@.news.bluewin.ch>...
> "Louis Frolio" <froliol@.yahoo.com> wrote in message
> news:94c28610.0409221536.67fda321@.posting.google.c om...
> > Greetings All, I was hoping that a replication sage might be able to
> > answer a question for me.
> > I want to have one subscriber subscribing to N publishers. I
> > essentially have a company that has a main headquarters and three
> > satellite offices. I want each of the satellite offices to push their
> > data up to the master database. From what I have read it seems like
> > this should not be a problem. Some questions that come to mind are:
> > 1.) Does the master need to be read only or can it be configured to be
> > updateable as well?
> > 2.) Can the distribution agent on all the publishers be set to
> > continuously distribute or should it be staggered so as not to cause a
> > problem when another distribution agent is running?
> > 3.) If the distribution is set to "delay distribution" will this cause
> > changes on the subscriber to be pushed out to the publishers?
> > In this database guid's are used as pk's so the issue of pk collisions
> > is not a problem.
> > I hope that this question is not too vague. My experience thus far
> > with replication has been simple one way transactional and simple
> > merge replication.
> > Regards, Louis Frolio
> You might want to post this in microsoft.public.sqlserver.replication, where
> there are probably more replication sages than in this newsgroup.
> Simon
Simon, thanks I did not realize that that group existed.
L
Replication: Change Distributor
Hello,
I need to change the distributor role from one production server to another
new server. I have 15 servers replicating (transactional) to one central
server, which is the distributor too. Of course, I want to change this role
to another new server.
I have been tested some processes in order to do that, many of them were
certains but I want to know the correct process which is supported by MS.
Thank you a lot.
Maria Pia Fernandez
Maria,
you can deduce the supported path from BOL - the section "Strategies for
Backing Up and Restoring Snapshot and Transactional Replication". The whole
process can be made simpler by preventing any access to the system, then
syncing all subscribers, then backing up the databases (master, msdb,
distribution) on the distributor. You can then restore the databases.
Remember that the server name must be identical for this to work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||To change the distributor you must drop all subscriptions and publications
and then configure the new distributor and all publishers to use this
distributor. Then recreate you publications and subscriptions. Ensure that
the distributor is clustered.
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
"mpia" <repettos@.telviso.com.ar> wrote in message
news:O7ldpJjVHHA.1120@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I need to change the distributor role from one production server to
> another new server. I have 15 servers replicating (transactional) to one
> central server, which is the distributor too. Of course, I want to change
> this role to another new server.
> I have been tested some processes in order to do that, many of them were
> certains but I want to know the correct process which is supported by MS.
> Thank you a lot.
> Maria Pia Fernandez
>
|||Hilary/Paul, thanks for your answers,
I need to maintain the old distributor!!!! I mean I need to change some
production publications in order to use another new distributor. So, this
new distributor will not have the same name.
Besides, I cant stop replicating, I can stop one of them and change its
distributor and so on.
Maria Pia Fernandez
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u3GTp0mVHHA.2212@.TK2MSFTNGP02.phx.gbl...
> To change the distributor you must drop all subscriptions and publications
> and then configure the new distributor and all publishers to use this
> distributor. Then recreate you publications and subscriptions. Ensure that
> the distributor is clustered.
> --
> 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
>
> "mpia" <repettos@.telviso.com.ar> wrote in message
> news:O7ldpJjVHHA.1120@.TK2MSFTNGP02.phx.gbl...
>
|||A publisher can only use a single distributor. Multiple publishers can use
the same distributor on a remote distributor, or they can each have their
own distribution database on the same distributor.
But your configuration is not supported.
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
"mpia" <repettos@.telviso.com.ar> wrote in message
news:epy7SOpVHHA.1200@.TK2MSFTNGP04.phx.gbl...
> Hilary/Paul, thanks for your answers,
> I need to maintain the old distributor!!!! I mean I need to change some
> production publications in order to use another new distributor. So, this
> new distributor will not have the same name.
> Besides, I cant stop replicating, I can stop one of them and change its
> distributor and so on.
> Maria Pia Fernandez
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u3GTp0mVHHA.2212@.TK2MSFTNGP02.phx.gbl...
>
|||I know that. I will try to explain better (sorry for my English!!)
I have 15 publishers using one distributor. I need to change the distributor
for some publications, so I will have two distributors.How can I change el
distributor for ONE publication?
Thanks.
Maria Pia Fernandez
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%237fq$upVHHA.4872@.TK2MSFTNGP03.phx.gbl...
>A publisher can only use a single distributor. Multiple publishers can use
>the same distributor on a remote distributor, or they can each have their
>own distribution database on the same distributor.
> But your configuration is not supported.
> --
> 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
>
> "mpia" <repettos@.telviso.com.ar> wrote in message
> news:epy7SOpVHHA.1200@.TK2MSFTNGP04.phx.gbl...
>
|||You will have to drop these publications and disable replication on those
machines whose distributor you wish to change. When recreate replication
configure them to use the different distributor/distributiondb.
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
"mpia" <repettos@.telviso.com.ar> wrote in message
news:OUx%23DFuVHHA.1432@.TK2MSFTNGP02.phx.gbl...
>I know that. I will try to explain better (sorry for my English!!)
> I have 15 publishers using one distributor. I need to change the
> distributor for some publications, so I will have two distributors.How can
> I change el distributor for ONE publication?
> Thanks.
> Maria Pia Fernandez
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%237fq$upVHHA.4872@.TK2MSFTNGP03.phx.gbl...
>
|||Yes, I know that method but I want to do that no need to drop publications.
Is this possible? I think it is possible without using enterprise manager.
Thank for your time!!!!
Maria Pia Fernandez
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eNT6lkuVHHA.4720@.TK2MSFTNGP04.phx.gbl...
> You will have to drop these publications and disable replication on those
> machines whose distributor you wish to change. When recreate replication
> configure them to use the different distributor/distributiondb.
> --
> 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
>
> "mpia" <repettos@.telviso.com.ar> wrote in message
> news:OUx%23DFuVHHA.1432@.TK2MSFTNGP02.phx.gbl...
>
|||There is no supported way to do this.
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
"mpia" <repettos@.telviso.com.ar> wrote in message
news:%23bwacHvVHHA.4028@.TK2MSFTNGP04.phx.gbl...
> Yes, I know that method but I want to do that no need to drop
> publications.
> Is this possible? I think it is possible without using enterprise manager.
> Thank for your time!!!!
> Maria Pia Fernandez
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eNT6lkuVHHA.4720@.TK2MSFTNGP04.phx.gbl...
>
I need to change the distributor role from one production server to another
new server. I have 15 servers replicating (transactional) to one central
server, which is the distributor too. Of course, I want to change this role
to another new server.
I have been tested some processes in order to do that, many of them were
certains but I want to know the correct process which is supported by MS.
Thank you a lot.
Maria Pia Fernandez
Maria,
you can deduce the supported path from BOL - the section "Strategies for
Backing Up and Restoring Snapshot and Transactional Replication". The whole
process can be made simpler by preventing any access to the system, then
syncing all subscribers, then backing up the databases (master, msdb,
distribution) on the distributor. You can then restore the databases.
Remember that the server name must be identical for this to work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||To change the distributor you must drop all subscriptions and publications
and then configure the new distributor and all publishers to use this
distributor. Then recreate you publications and subscriptions. Ensure that
the distributor is clustered.
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
"mpia" <repettos@.telviso.com.ar> wrote in message
news:O7ldpJjVHHA.1120@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I need to change the distributor role from one production server to
> another new server. I have 15 servers replicating (transactional) to one
> central server, which is the distributor too. Of course, I want to change
> this role to another new server.
> I have been tested some processes in order to do that, many of them were
> certains but I want to know the correct process which is supported by MS.
> Thank you a lot.
> Maria Pia Fernandez
>
|||Hilary/Paul, thanks for your answers,
I need to maintain the old distributor!!!! I mean I need to change some
production publications in order to use another new distributor. So, this
new distributor will not have the same name.
Besides, I cant stop replicating, I can stop one of them and change its
distributor and so on.
Maria Pia Fernandez
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u3GTp0mVHHA.2212@.TK2MSFTNGP02.phx.gbl...
> To change the distributor you must drop all subscriptions and publications
> and then configure the new distributor and all publishers to use this
> distributor. Then recreate you publications and subscriptions. Ensure that
> the distributor is clustered.
> --
> 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
>
> "mpia" <repettos@.telviso.com.ar> wrote in message
> news:O7ldpJjVHHA.1120@.TK2MSFTNGP02.phx.gbl...
>
|||A publisher can only use a single distributor. Multiple publishers can use
the same distributor on a remote distributor, or they can each have their
own distribution database on the same distributor.
But your configuration is not supported.
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
"mpia" <repettos@.telviso.com.ar> wrote in message
news:epy7SOpVHHA.1200@.TK2MSFTNGP04.phx.gbl...
> Hilary/Paul, thanks for your answers,
> I need to maintain the old distributor!!!! I mean I need to change some
> production publications in order to use another new distributor. So, this
> new distributor will not have the same name.
> Besides, I cant stop replicating, I can stop one of them and change its
> distributor and so on.
> Maria Pia Fernandez
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u3GTp0mVHHA.2212@.TK2MSFTNGP02.phx.gbl...
>
|||I know that. I will try to explain better (sorry for my English!!)
I have 15 publishers using one distributor. I need to change the distributor
for some publications, so I will have two distributors.How can I change el
distributor for ONE publication?
Thanks.
Maria Pia Fernandez
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%237fq$upVHHA.4872@.TK2MSFTNGP03.phx.gbl...
>A publisher can only use a single distributor. Multiple publishers can use
>the same distributor on a remote distributor, or they can each have their
>own distribution database on the same distributor.
> But your configuration is not supported.
> --
> 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
>
> "mpia" <repettos@.telviso.com.ar> wrote in message
> news:epy7SOpVHHA.1200@.TK2MSFTNGP04.phx.gbl...
>
|||You will have to drop these publications and disable replication on those
machines whose distributor you wish to change. When recreate replication
configure them to use the different distributor/distributiondb.
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
"mpia" <repettos@.telviso.com.ar> wrote in message
news:OUx%23DFuVHHA.1432@.TK2MSFTNGP02.phx.gbl...
>I know that. I will try to explain better (sorry for my English!!)
> I have 15 publishers using one distributor. I need to change the
> distributor for some publications, so I will have two distributors.How can
> I change el distributor for ONE publication?
> Thanks.
> Maria Pia Fernandez
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%237fq$upVHHA.4872@.TK2MSFTNGP03.phx.gbl...
>
|||Yes, I know that method but I want to do that no need to drop publications.
Is this possible? I think it is possible without using enterprise manager.
Thank for your time!!!!
Maria Pia Fernandez
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eNT6lkuVHHA.4720@.TK2MSFTNGP04.phx.gbl...
> You will have to drop these publications and disable replication on those
> machines whose distributor you wish to change. When recreate replication
> configure them to use the different distributor/distributiondb.
> --
> 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
>
> "mpia" <repettos@.telviso.com.ar> wrote in message
> news:OUx%23DFuVHHA.1432@.TK2MSFTNGP02.phx.gbl...
>
|||There is no supported way to do this.
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
"mpia" <repettos@.telviso.com.ar> wrote in message
news:%23bwacHvVHHA.4028@.TK2MSFTNGP04.phx.gbl...
> Yes, I know that method but I want to do that no need to drop
> publications.
> Is this possible? I think it is possible without using enterprise manager.
> Thank for your time!!!!
> Maria Pia Fernandez
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eNT6lkuVHHA.4720@.TK2MSFTNGP04.phx.gbl...
>
Replication/mirroring on tables without a primary key
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?
>
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?
>
replication/distributor problem
Hello,
I'm trying to set up transactional replication on two SQL2k Servers.
Unfortunately I stuck on the following problem:
I use 'Configure Publishing and Distribution Wizard' , I make one of the
servers (publisher) its own distributor, then I set all the paths .After
pressing 'Finish' button, the wizard tries to perform next step i.e.
'Configuring Distributor' and I get the error:
"Error 14114: '(null)' is not configured as a Distributor". Does anyone have
idea what I should do in order to solve the problem?
I'm trying to set up transactional replication on two SQL2k Servers.
Unfortunately I stuck on the following problem:
I use 'Configure Publishing and Distribution Wizard' , I make one of the
servers (publisher) its own distributor, then I set all the paths .After
pressing 'Finish' button, the wizard tries to perform next step i.e.
'Configuring Distributor' and I get the error:
"Error 14114: '(null)' is not configured as a Distributor". Does anyone have
idea what I should do in order to solve the problem?
I tried to run procedures manually, and I get exactly the same error when I
try to execute sp_adddistributondb
Thanks,
Piotrektry this in QA
SELECT @.@.ServerName
My guess is that this returns NULL.
Now do
Exec sp_AddServer '<Name>', 'local'
Restart the SQL Server and try again
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
"Piotrek Stachowicz" <dynamite@.poczta.onet.pl> wrote in message
news:cdllkd$5ja$1@.news.onet.pl...
> Hello,
> I'm trying to set up transactional replication on two SQL2k Servers.
> Unfortunately I stuck on the following problem:
> I use 'Configure Publishing and Distribution Wizard' , I make one of the
> servers (publisher) its own distributor, then I set all the paths .After
> pressing 'Finish' button, the wizard tries to perform next step i.e.
> 'Configuring Distributor' and I get the error:
> "Error 14114: '(null)' is not configured as a Distributor". Does anyone
have
> idea what I should do in order to solve the problem?
> I tried to run procedures manually, and I get exactly the same error when
I
> try to execute sp_adddistributondb
> Thanks,
> Piotrek
Replication, Log Shipping and Backup
Hi there all.
In my installation there is a main online DB which replicates
some of its data with transactional replication to another DB.
I also use log shipping so that there is always a stand by server
for my main DB.
1. With this installation, can I still keep backing up my log every
half an hour as I used to before log shipping? I would feel much more
safer if I could. Is there a way to do it?
2. Log shipping uses log backups, so it truncate the log every time
it posts changes to the standby server, is that correct?
Thank you all in advance.
George
1) yes, but you might want to back it up more frequently as replication
writes to the log too - not much, but some.
2) it doesn't truncate, but it does back it up.
"George Nentidis" <gnentid@.msn.com> wrote in message
news:%23rnPmmQ6FHA.3760@.TK2MSFTNGP14.phx.gbl...
> Hi there all.
> In my installation there is a main online DB which replicates
> some of its data with transactional replication to another DB.
> I also use log shipping so that there is always a stand by server
> for my main DB.
> 1. With this installation, can I still keep backing up my log every
> half an hour as I used to before log shipping? I would feel much more
> safer if I could. Is there a way to do it?
> 2. Log shipping uses log backups, so it truncate the log every time
> it posts changes to the standby server, is that correct?
> Thank you all in advance.
> George
In my installation there is a main online DB which replicates
some of its data with transactional replication to another DB.
I also use log shipping so that there is always a stand by server
for my main DB.
1. With this installation, can I still keep backing up my log every
half an hour as I used to before log shipping? I would feel much more
safer if I could. Is there a way to do it?
2. Log shipping uses log backups, so it truncate the log every time
it posts changes to the standby server, is that correct?
Thank you all in advance.
George
1) yes, but you might want to back it up more frequently as replication
writes to the log too - not much, but some.
2) it doesn't truncate, but it does back it up.
"George Nentidis" <gnentid@.msn.com> wrote in message
news:%23rnPmmQ6FHA.3760@.TK2MSFTNGP14.phx.gbl...
> Hi there all.
> In my installation there is a main online DB which replicates
> some of its data with transactional replication to another DB.
> I also use log shipping so that there is always a stand by server
> for my main DB.
> 1. With this installation, can I still keep backing up my log every
> half an hour as I used to before log shipping? I would feel much more
> safer if I could. Is there a way to do it?
> 2. Log shipping uses log backups, so it truncate the log every time
> it posts changes to the standby server, is that correct?
> Thank you all in advance.
> George
Subscribe to:
Posts (Atom)