Regarding Collation for mirroring & Error 1418

Can I know is it possible that if the database servers' collation in both principal and mirror server might unable to get the mirroring? Because one of database collation is Latin1_General_CI_AS while another server (mirror) is SQL_Latin1_General_CP1_CI_AS. I did several type of troubleshooting but still unable to solve the Error 1418. All the ports are stated as started, able to use telnet to connect to the server's port but still unable to get a connection for the database mirroring. I do really need help on it. Thx for the assistance and kindness.

Best Regards,

Hans

Hello Hans,

Database mirroring is the copy of your data syncronous or assyncronous, when you do the copy of data between database with collation different occurs the error: Cannot resolve the collation conflict.

I believe your error is the same.

Try build the database mirroring with the same Collation on Source and destination.

For start the database mirroring you need restore the database in destination with the NORECOVERY option.

Good Luck!

Regards,

|||

Hello Fernandes,

Thanx for the reply and your information. I guess I forgot to give an useful information regarding this issue. Actually the database collation that I mention is wrong , suppose to be the server's collation (inside server properties). Both servers' collation are different which are Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS. However, the database (AdventureWorks) that used for mirroring is the same collation for principal and mirror. Is there any conflict as well ? I did try on using NORECOVERY when I restored it into the mirror server. All the steps I just follow which stated in BOL. Can we discuss through msn messenger anyway? Hope to hear from you soon again. Thx again and have a nice day.

From,

Hans

|||

Hello Hans,

Server's collation different not problem, because database mirroring work inside the database.

Both SQL Server running with same domain account?

Do you restore your database in mirror with the NORECOVERY option?

Can we discuss through msn messenger anyway?

Ok, send invite for me: lobo-fernandes@.hotmail.com

But I live in Rio de Janeiro - Brazil.

Where do you live? What your timezone?

Regards,

|||

Oic, means Collation will not causing the problem then. Domain account? Is it 'Log on as ' Local Systemn, Local Service and Network Service?

Yup , the database that i restored in mirror database is usinfg the NORECOVERY option. Ok , let's discuss through msn then. I already added u into my msn list. I am from Malaysia (Asia) , GMT+8. Hope able to meet up with u thru msn then. See ya and have a nice day. :)

From,

Hans

reg linked server

We are using linked servers to access another server, we are using windows authentication. but when we use

EXEC master.dbo.sp_addlinkedserver @.server = 'SERVER', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.locallogin = NULL, @.useself = N'False', @.rmtuser = N'sa', @.rmtpassword = N'sa'

When we connect using a SQL login as specified in the above line we are able to connect to the linked server.

But when we want to connect using a Windows user it is connecting.(we want to connect using a specific windows user i.e only one windows user will be there in the server and we shall access the server using that windows user)

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.locallogin = NULL, @.useself = N'False', @.rmtuser = N'Domain\userid', @.rmtpassword = 'password'

Is there any other way to connect to the linked serrver.. we dont want to use the SQL server login to connect to the linked server.. how to use windows authentication to connect to the linked server. Anyone have tried it out.. Thanks in advance

It should work try the following example...

EXEC sp_addlinkedserver @.server = 'SERVER', @.provider = 'SQLOLEDB.1', @.srvproduct = '', @.provstr = 'Privider=SQLOLEDB.1;Data Source=TargetServer;Initial Catalog=Database'

go

Exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.useself = true, @.locallogin = null, @.rmtuser = 'Domain\Userid', @.rmtpassword = 'Password'

go

Select * From OpenQuery(MyRemoteServer, 'Select * From Sysobjects')

--OR

Select * From MyRemoteServer.DatabaseName.dbo.Sysobjects

go

Exec sp_droplinkedsrvlogin 'SERVER', null

Exec sp_dropserver 'SERVER'

Referential constraint between two tables in two databases

Is it possible to define a referential constraint between two tables in two different databases (on two servers)? Or are there beter best practices methods/products to achieve this result.

i think not possible

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

You should be able to set up the reference as a linked server (http://www.databasejournal.com/features/mssql/article.php/3085211,http://msdn2.microsoft.com/en-us/library/ms188279.aspx -- this is a sql server 2005 article, but earlier versions support linked servers as well)

I doubt that you can enforce the constraint declaritively, but I am quite sure you can do it using a trigger. Let us know if you need help with the triggers.

|||

And what can be used when databases are sitted on the same server?

|||

Then it's even easier because you don't need the linked server, you can simply reference the table on the other database by indluding the DB name, eg,

database2.dbo.sometable

Or (and this is the way I do it), create a view in database1 pointing to the table in database2

use database1
go
create view TableXyz as
select * from databse2.dbo.TableXyz
go
grant insert, update, select, delete on TableXyz to ...
go

Then, from within database1, all references to TableXyz really point to database2.dbo.TableXyz

I think the declaritive RI may work in this scenario but I'm not positive, but in any case a trigger will work

sql

Re-establish replication

I had Merge replication working fine between two SQL 2000 servers for months... then the subscriber server had a hard drive crash and we rebuilt it... I am now having trouble re-establishing the replication with the subscriber. On the publisher I have de
leted the subscription and reestablished it... but I never see the database being pushed to the subscriber, and the Merge agent always fails because it says it cannot find the database on the subscriber.
What am I missing here? Any advice would be greatly appreciated.
Thanks,
Jeff
Jeff,
you say "I never see the database being pushed to the subscriber" but the
database isn't created by the snapshot synchronization. Can you check to see
if it exists on the subscriber? If it does, then on the publisher
properties, subscriptions tab does the subscription database name appear?
Cheers,
Paul Ibison
|||what is the error message you are getting?
"dev@.mycompany.com" <anonymous@.discussions.microsoft.com> wrote in message
news:DEFD3DDB-3465-44E2-B1EB-1050147A2BC7@.microsoft.com...
> I had Merge replication working fine between two SQL 2000 servers for
months... then the subscriber server had a hard drive crash and we rebuilt
it... I am now having trouble re-establishing the replication with the
subscriber. On the publisher I have deleted the subscription and
reestablished it... but I never see the database being pushed to the
subscriber, and the Merge agent always fails because it says it cannot find
the database on the subscriber.
> What am I missing here? Any advice would be greatly appreciated.
> Thanks,
> Jeff
|||Paul,
Thanks for the response... you reminded me that I had to manually create the database on the subscriber... then replciation agenst would populate it for me... which they did... and I thought all was fine (I tested on table and changes on both sides propog
ated properly to the other side)... but after a few days, we noticed that new records in another table on the publisher would not only NOT get replicated to the subscriber, but would be deleted from the publisher. The Merge logs showed conflicts... how c
ould there be conflicts with a new record being added on the publisher?
Also, when I try to manually add a record to the same tabel on the subscriber, I get a stored proc error... I am guessing related to replication.
What did I forget here?
1) Existing replication working fine
2) Subscriber machine failed
3) Rebuilt subscriber from scratch, reloaded SQL
4) Set-up SQL User list, and created databse that si used for replication
5) Deleted old subsciption on publisher, and created a new one
6) Then the story above took place...
Jeff
|||What triggers do you have on the tables?
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I have no triggers on the tables at all. The only triggers that fire should be those that SQL Replication uses.
|||Jeff,
there is definitely something extra/abnormal/odd going on here. I think your best bet would be to use profiler and try to trace the event sequence that leads to this behaviour. Alternatively, if you don't want to do this detective work, then you could set
up the whole thing from scratch (drop publisher, remove replication on publishing db and subscribing db, add publisher and subscriber, initialize).
HTH,
Paul Ibison
|||Paul,
Thanks for your help... I deleted the subscription and the publication and started from scratch... set up the publication... ok... set up the subscription... then I get an error saying the publisher could not log-in to the subscriber.
I have the SQL server and SQL Agent set to use a Windows account on both machines... same account ID/PW. But the Merge agent error says "Error logging in to subscriber. sa login failed".
What am I missing here? I thought the Merge agent used the security settings set for the SQL Agent (set to a Windows account)... so why is the merge agent trying to use the sa login at the subscriber?
I know I am just forgetting something very simple here. Any advice would be greatly appreciated.
Thanks,
Jeff
|||Jeff,
right-click the replication monitor and go to properties. Using the elipsis,
make sure that your agents are using trusted security and not sql server
security.
HTH,
Paul
|||Paul,
Under Distributor properties, Subscribers tab, ellipsis next to subscriber, it shows Agent connection to subscriber as "Impersonate the SQL Server Agent account on 'PublisherName' (trusted connection)"
Why else would the Merge agent be trying to connect with sa instead of the Windows account that both SQL Agents are running under on both publisher and subscriber?
Jeff

Redundant SQL servers - Environment

Here is what we are trying to accomplish, and please let me know if we are
going down the right path.
We have 2 Windows 2000 Servers running SQL 2000 for a website. The SQL
database is on one of the two servers and we want to create a redundant
environment so when one of the SQL servers fails, the other will take over,
or continue to provide the functions for the website.
We are thinking of putting the SQL database on a NAS device and then point
each server to it and if one goes down the other can perform the operations
as needed.
Can this be done by clustering the two SQL servers and then pointing them to
the SQL database on the NAS device?
Do you know of any other way to create a redundant SQL environment?
Hopefully I've provided you with enough information, if not please let me
know if you need more details.
Thanks,
CC
SQL Server does not support NAS. If you can use a SAN, then you have two
choices. First, you can go with log shipping. Second, you can go with a
cluster solution, as long as the hardware is supported for clustering.
However, you can't just cluster an existing single-server instance. What
you could do is cluster the server that is not being used right now, migrate
the old instance to it and then remove the old instance. At this point, you
have a single-node cluster. Take the old server and add it to the cluster
and you're there.
For a bit more on clustering, check out:
http://www.microsoft.com/technet/technetmag/issues/2007/03/SQLClusters/default.aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"VIT" <VIT@.discussions.microsoft.com> wrote in message
news:D7E35BA4-EDE6-4CEB-B474-652CBD9DD64F@.microsoft.com...
Here is what we are trying to accomplish, and please let me know if we are
going down the right path.
We have 2 Windows 2000 Servers running SQL 2000 for a website. The SQL
database is on one of the two servers and we want to create a redundant
environment so when one of the SQL servers fails, the other will take over,
or continue to provide the functions for the website.
We are thinking of putting the SQL database on a NAS device and then point
each server to it and if one goes down the other can perform the operations
as needed.
Can this be done by clustering the two SQL servers and then pointing them to
the SQL database on the NAS device?
Do you know of any other way to create a redundant SQL environment?
Hopefully I've provided you with enough information, if not please let me
know if you need more details.
Thanks,
CC
|||There are several techniques for creating a redundnt SQL environment.
Clustering using a NAS box is not one of them. Clustering using a supported
storage unit is one possible technique. Only a very few NAS systems are
supported for SQL and none are supported for clustering.
Another possibility is database mirroring. This technique does not require
a shared storage system, but is more resource intensive and has some other
unique requirements.
Here is the Microsoft SQL 2005 High Availability home page:
http://www.microsoft.com/sql/technologies/highavailability/default.mspx
It contains links to FAQs, white papers, etc. on SQL High Availability.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VIT" <VIT@.discussions.microsoft.com> wrote in message
news:D7E35BA4-EDE6-4CEB-B474-652CBD9DD64F@.microsoft.com...
> Here is what we are trying to accomplish, and please let me know if we are
> going down the right path.
> We have 2 Windows 2000 Servers running SQL 2000 for a website. The SQL
> database is on one of the two servers and we want to create a redundant
> environment so when one of the SQL servers fails, the other will take
> over,
> or continue to provide the functions for the website.
> We are thinking of putting the SQL database on a NAS device and then point
> each server to it and if one goes down the other can perform the
> operations
> as needed.
> Can this be done by clustering the two SQL servers and then pointing them
> to
> the SQL database on the NAS device?
> Do you know of any other way to create a redundant SQL environment?
> Hopefully I've provided you with enough information, if not please let me
> know if you need more details.
> Thanks,
> CC
|||In addition to what the others have said, I just want to add that NAS is
built on top of CIFS, and CIFS is a horrible protocol for block I/Os. You'd
be better with considering iSCSI.
Linchi
"VIT" wrote:

> Here is what we are trying to accomplish, and please let me know if we are
> going down the right path.
> We have 2 Windows 2000 Servers running SQL 2000 for a website. The SQL
> database is on one of the two servers and we want to create a redundant
> environment so when one of the SQL servers fails, the other will take over,
> or continue to provide the functions for the website.
> We are thinking of putting the SQL database on a NAS device and then point
> each server to it and if one goes down the other can perform the operations
> as needed.
> Can this be done by clustering the two SQL servers and then pointing them to
> the SQL database on the NAS device?
> Do you know of any other way to create a redundant SQL environment?
> Hopefully I've provided you with enough information, if not please let me
> know if you need more details.
> Thanks,
> CC
|||So based on what we have what is the best, most cost effective way to achieve
a redundant SQL environment?
Thanks,
CC
"Linchi Shea" wrote:
[vbcol=seagreen]
> In addition to what the others have said, I just want to add that NAS is
> built on top of CIFS, and CIFS is a horrible protocol for block I/Os. You'd
> be better with considering iSCSI.
> Linchi
> "VIT" wrote:
|||What are the requirements of the applications?
Log Shipping is cost-effective, but in the event of an outage, manual
intervention would be required to reconfigure the applications to point to
the failsafe server.
SQL Server 2005 Database Mirroring is also cost-effective but will require
some beefier hardware, an upgraded network to reduce latency, an upgrade to
SQL Server 2005, and a reconfiguration of the applications to the later MDAC
or SQL Native Client protocol. Failover would be automatic.
Finally, Failover Clustering would provide better availability and
automation and is supported under both SQL Server 2000 and 2005; however,
this is not as cost-effective and requires a shared storage array
environment. SQL Server 2005, however, has introduced support for failover
clusters on the Standard Edition, which helps reduce the expense some.
Sincerely,
Anthony Thomas

"VIT" <VIT@.discussions.microsoft.com> wrote in message
news:353890CD-84AA-4E3B-A6FB-8A6580611216@.microsoft.com...
> So based on what we have what is the best, most cost effective way to
achieve[vbcol=seagreen]
> a redundant SQL environment?
> Thanks,
> CC
> "Linchi Shea" wrote:
You'd[vbcol=seagreen]
are[vbcol=seagreen]
SQL[vbcol=seagreen]
redundant[vbcol=seagreen]
over,[vbcol=seagreen]
point[vbcol=seagreen]
operations[vbcol=seagreen]
them to[vbcol=seagreen]
me[vbcol=seagreen]

Redundant Servers?

I have to setup a redundant server system. I want to
preferably use Windows Server 2003 Standard but I can use
Windows Server 2000 as well. The system will be running
SQL Server 2000. I am writing some custom software to
run on the system and will be storing data to the SQL
database. If the primary server fails then the secondary
server will start handling the functions of the primary
server. What is the best way to accomplish this? I need
the data in each SQL database to be identical, is there a
way to sync this data? I have thought about using Legato
Co-Standby Server AAdvanced, is this a good product?
WIll it do what I need? Any ideas are greatly
appreciated. I am a software engineer not a sys admin so
this is kinda new to me. Thanks!!!Look into Microsoft Cluster Services and Microsoft SQL Clustering.
--
Geoff N. Hiten
Senior Database Administrator
Careerbuilder.com
"Kris" <grindaz@.hotmail.com> wrote in message
news:022f01c34181$83b10930$a101280a@.phx.gbl...
> I have to setup a redundant server system. I want to
> preferably use Windows Server 2003 Standard but I can use
> Windows Server 2000 as well. The system will be running
> SQL Server 2000. I am writing some custom software to
> run on the system and will be storing data to the SQL
> database. If the primary server fails then the secondary
> server will start handling the functions of the primary
> server. What is the best way to accomplish this? I need
> the data in each SQL database to be identical, is there a
> way to sync this data? I have thought about using Legato
> Co-Standby Server AAdvanced, is this a good product?
> WIll it do what I need? Any ideas are greatly
> appreciated. I am a software engineer not a sys admin so
> this is kinda new to me. Thanks!!!
>|||Kris, the answer depends on what you need exactly. When
you ask for the "best way" to approach, it also depends on
how much $$$ you'd like to spend, and how much down time
your users could deal with in the event of a system
crash. Yes, clustering is a nice option, but it's more
complex until you've done it a while, and you need shared
disk storage between the nodes, Advanced Server and
Enterprise Ed of SQL Server...
Then you could also look at something like shipping your
transaction logs over to the offline server, and if a
crash happens, maybe get that last LOG over, then changing
server names and bringing that server online...
It all depends on the budget and the length of down time
that's bearable... Bruce
>--Original Message--
>I have to setup a redundant server system. I want to
>preferably use Windows Server 2003 Standard but I can use
>Windows Server 2000 as well. The system will be running
>SQL Server 2000. I am writing some custom software to
>run on the system and will be storing data to the SQL
>database. If the primary server fails then the secondary
>server will start handling the functions of the primary
>server. What is the best way to accomplish this? I need
>the data in each SQL database to be identical, is there a
>way to sync this data? I have thought about using Legato
>Co-Standby Server AAdvanced, is this a good product?
>WIll it do what I need? Any ideas are greatly
>appreciated. I am a software engineer not a sys admin so
>this is kinda new to me. Thanks!!!
>.
>|||Thanks for the reply Bruce. I cannot spend lots of money
on this like $11,000 for SQL Ent. ED. But I will be
using Windows Server 20003 Enterprise. I have been
reading about clustering and it sounds like the way to go
except for as you metion the shared storage. How exactly
do you do this? What type of hardware? Also, will
clustering keep data in the SQL database on each machine
synced together or do you have to keep the DB on the
shared storage? I look forward to your comments and
thanks again.
Kris
>--Original Message--
>Kris, the answer depends on what you need exactly. When
>you ask for the "best way" to approach, it also depends
on
>how much $$$ you'd like to spend, and how much down time
>your users could deal with in the event of a system
>crash. Yes, clustering is a nice option, but it's more
>complex until you've done it a while, and you need
shared
>disk storage between the nodes, Advanced Server and
>Enterprise Ed of SQL Server...
>Then you could also look at something like shipping your
>transaction logs over to the offline server, and if a
>crash happens, maybe get that last LOG over, then
changing
>server names and bringing that server online...
>It all depends on the budget and the length of down time
>that's bearable... Bruce
>>--Original Message--
>>I have to setup a redundant server system. I want to
>>preferably use Windows Server 2003 Standard but I can
use
>>Windows Server 2000 as well. The system will be
running
>>SQL Server 2000. I am writing some custom software to
>>run on the system and will be storing data to the SQL
>>database. If the primary server fails then the
secondary
>>server will start handling the functions of the primary
>>server. What is the best way to accomplish this? I
need
>>the data in each SQL database to be identical, is there
a
>>way to sync this data? I have thought about using
Legato
>>Co-Standby Server AAdvanced, is this a good product?
>>WIll it do what I need? Any ideas are greatly
>>appreciated. I am a software engineer not a sys admin
so
>>this is kinda new to me. Thanks!!!
>>.
>.
>|||In your situation, you have two servers (nodes), and also
a shared disk array, a separate unit, sitting between and
cbaled to each node. Yes, the database files sit on the
shared drives, there's only one copy of them (well, not
counting whatever you do for disaster planning!)... After
teh 2 node cluster is built, then you install SQL Server
and it's "cluster-aware", so you should generally only
have to install it once, and it actually installs SQL
Server software on BOTH nodes C: drives, but anything
shared goes on the disk array. I'm not a hardware guy, to
talk about disk array brands exactly, and which is
better... But in a cluster you need some type of shared
disk between the nodes. Also, since you're dealing with 2
machines, keep in mind that you have TWO registries, so
anything not cluster-aware, needs to MAYBE be done on each
box... Bruce
>--Original Message--
>Thanks for the reply Bruce. I cannot spend lots of money
>on this like $11,000 for SQL Ent. ED. But I will be
>using Windows Server 20003 Enterprise. I have been
>reading about clustering and it sounds like the way to go
>except for as you metion the shared storage. How exactly
>do you do this? What type of hardware? Also, will
>clustering keep data in the SQL database on each machine
>synced together or do you have to keep the DB on the
>shared storage? I look forward to your comments and
>thanks again.
>Kris
>>--Original Message--
>>Kris, the answer depends on what you need exactly. When
>>you ask for the "best way" to approach, it also depends
>on
>>how much $$$ you'd like to spend, and how much down time
>>your users could deal with in the event of a system
>>crash. Yes, clustering is a nice option, but it's more
>>complex until you've done it a while, and you need
>shared
>>disk storage between the nodes, Advanced Server and
>>Enterprise Ed of SQL Server...
>>Then you could also look at something like shipping your
>>transaction logs over to the offline server, and if a
>>crash happens, maybe get that last LOG over, then
>changing
>>server names and bringing that server online...
>>It all depends on the budget and the length of down time
>>that's bearable... Bruce
>>--Original Message--
>>I have to setup a redundant server system. I want to
>>preferably use Windows Server 2003 Standard but I can
>use
>>Windows Server 2000 as well. The system will be
>running
>>SQL Server 2000. I am writing some custom software to
>>run on the system and will be storing data to the SQL
>>database. If the primary server fails then the
>secondary
>>server will start handling the functions of the primary
>>server. What is the best way to accomplish this? I
>need
>>the data in each SQL database to be identical, is there
>a
>>way to sync this data? I have thought about using
>Legato
>>Co-Standby Server AAdvanced, is this a good product?
>>WIll it do what I need? Any ideas are greatly
>>appreciated. I am a software engineer not a sys admin
>so
>>this is kinda new to me. Thanks!!!
>>.
>>.
>.
>|||probably the cheapest solution is log shipping, which can be home grown or
purchased (enterprise edition). Failover is manual, then.
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:08f801c34193$8b57c950$a001280a@.phx.gbl...
> In your situation, you have two servers (nodes), and also
> a shared disk array, a separate unit, sitting between and
> cbaled to each node. Yes, the database files sit on the
> shared drives, there's only one copy of them (well, not
> counting whatever you do for disaster planning!)... After
> teh 2 node cluster is built, then you install SQL Server
> and it's "cluster-aware", so you should generally only
> have to install it once, and it actually installs SQL
> Server software on BOTH nodes C: drives, but anything
> shared goes on the disk array. I'm not a hardware guy, to
> talk about disk array brands exactly, and which is
> better... But in a cluster you need some type of shared
> disk between the nodes. Also, since you're dealing with 2
> machines, keep in mind that you have TWO registries, so
> anything not cluster-aware, needs to MAYBE be done on each
> box... Bruce
>
> >--Original Message--
> >Thanks for the reply Bruce. I cannot spend lots of money
> >on this like $11,000 for SQL Ent. ED. But I will be
> >using Windows Server 20003 Enterprise. I have been
> >reading about clustering and it sounds like the way to go
> >except for as you metion the shared storage. How exactly
> >do you do this? What type of hardware? Also, will
> >clustering keep data in the SQL database on each machine
> >synced together or do you have to keep the DB on the
> >shared storage? I look forward to your comments and
> >thanks again.
> >
> >Kris
> >
> >>--Original Message--
> >>Kris, the answer depends on what you need exactly. When
> >>you ask for the "best way" to approach, it also depends
> >on
> >>how much $$$ you'd like to spend, and how much down time
> >>your users could deal with in the event of a system
> >>crash. Yes, clustering is a nice option, but it's more
> >>complex until you've done it a while, and you need
> >shared
> >>disk storage between the nodes, Advanced Server and
> >>Enterprise Ed of SQL Server...
> >>
> >>Then you could also look at something like shipping your
> >>transaction logs over to the offline server, and if a
> >>crash happens, maybe get that last LOG over, then
> >changing
> >>server names and bringing that server online...
> >>
> >>It all depends on the budget and the length of down time
> >>that's bearable... Bruce
> >>
> >>--Original Message--
> >>I have to setup a redundant server system. I want to
> >>preferably use Windows Server 2003 Standard but I can
> >use
> >>Windows Server 2000 as well. The system will be
> >running
> >>SQL Server 2000. I am writing some custom software to
> >>run on the system and will be storing data to the SQL
> >>database. If the primary server fails then the
> >secondary
> >>server will start handling the functions of the primary
> >>server. What is the best way to accomplish this? I
> >need
> >>the data in each SQL database to be identical, is there
> >a
> >>way to sync this data? I have thought about using
> >Legato
> >>Co-Standby Server AAdvanced, is this a good product?
> >>WIll it do what I need? Any ideas are greatly
> >>appreciated. I am a software engineer not a sys admin
> >so
> >>this is kinda new to me. Thanks!!!
> >>
> >>.
> >>
> >>.
> >>
> >.
> >

Redundancy

Hi All,

After some feedback from some of you who might have done something similar:

We are going to be having 3 SQL servers (running SQL Std licence).
2 Are live
1 is a hot swap in the event that we have a total loss of either of the SQL boxes.
Basically what I am wanting to do, is have the hot swap being updated periodically so that the databases are being replicated on this box, so that if the live one fell over, we could very quickly get the hot swap into take over.

Can anyone offer any perspectives on the best method of attack for this?

Thanks in advance for your thoughts.

CheersTry to implement your own log shipping by referring to this article http://www.sql-server-performance.com/sql_server_log_shipping.asp and this http://support.microsoft.com/default.aspx?scid=kb;en-us;314515 KBA for FAQs about LS.|||Brilliant - thanks this is exactly what I was after.

Thanks!|||This is only a thought, but you could also do transactional replication to the third box. If one of the two other servers dies, all you need to do is drop all of the replication and rename the third box to take the place of the downed server.

This buys two advantages from my perspective... First, it gives you a "live" set of data that you can query, etc. to take load off of your production database(s). As a nice side benefit, it gives you literally "up to the minute" data, there is no loss due to log shipping delays if you ever need to use it!

-PatP

Reducing failover time

Hi All,

Is it possible to reduce the automatic failover time on MS SQL 2005? It seems to take around a minute on my servers.

Thanks for your help.

Regards,

1 Minute?, is so much time, can you post your configurations pls?...

John Bocachica

|||

Failover time can depend on a lot of things.

Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx for a discussion on what happens during the automatic failover.

What type of failure are you talking about here?

What is load on the database? Take a look at the redo_queue on the mirror at the time of failure.

More details needed to answer your question.