SQL Failover - Mirroring, Log Shipping, Clustering, Load Balancing

Jun 14, 2007



Hello.



I am confuse and cant decide on how to setup high availability on our SQL 2005. Here's what on my mind and on resources list:



I plan to have mirroring on my SQL1 to SQL2 with the help of SQL3 as witness. So this would be automatic failover. My idea on mirroring is when SQL1 goes down, SQL3 would tell SQL2 to run and be the primary. It will automatically failover to SQL2. Right? My questions are:

1) How can I revert back to SQL1 once it is ready?

2) I read in one of the post that it is impossible to write in a mirrored DB, is this true? I mean, what's the use of failing over to the next node when it's not possible to write and update data/records?

3) If number 2 is false (i hope so), how would the data be synchronize from SQL2 back to SQL1. Those transaction that were made while SQL1 is down.

4) How about the connection string from the web applications? Would it be automatically point to SQL2? We have load balancing setup in place, would this help web application connection to automatically point to SQL2?



Another setup:



We have SAN in place (not yet used, but is planning to use for this SQL thing), EMC to be specific. My question would be:

1) For SAN setup, the data storage would be centralize. So would that mean that SQL1 and SQL2 services will use the same data and log file from the SAN storage?

2) How would you call this setup then? Can this be clustering type of high availability? Will clustering work under load balancing setup? I believe mirroring is not possible here? Right?

3) How can I setup my 3 SQL servers with the same theory in mind: when SQL1 goes down, SQL2 will take over. Data will be synchronize when SQL1 is up and running again. With automatic failover and reverting back to primary.



I read so much topics about this, but the more I research, the more I get confuse.



Any suggestions, comments, advice is greatly appreciated!

View 6 Replies


ADVERTISEMENT

Load Balancing And Clustering

Jul 20, 2005

When a web application becomes overloaded with traffic, one can offload itby load balancing and clustering the front end web servers. What happenswhen the back-end MSSQL database becomes overloaded? Does MYSQL offer loadbalancing and clustering?

View 3 Replies View Related

DB Load Balancing With Mirroring ?

Apr 27, 2007

Hi guys, can I know whether 2 servers (load balancing) able to set up the mirroring on another server? From what I know, mirroring only can set 1 IP address for principal. I just want to double confirm on it. Thanks for any assistance here.



Best Regards,

Hans

View 1 Replies View Related

DB Mirroring + Load Balancing

Nov 3, 2006

Is it compatible to set up SQL DB mirroring on 2
different physical servers (High availbility + FULL transaction safety +
Automatic failover with a third server as witness) and simultaneously set up
network load balancing between these 2 servers (option with Windows Server 2003
Enterprise) ?

If yes, which type of licenses do I need and how
many (for SQL server 2005 and Windows Server 2003) with such a configuration
?
- 4 physical servers in total : A, B, C and
D
- load balancing between A and B (same application)

- load balancing between C and D (same application)

- A hosts principal DB X, B hosts mirror of DB
X
- C hosts principal DB Y, D hosts mirror of DB
Y
- D is witness for A and B
- B is witness for C and D
- A and B : only internal clients <
25
- C and D : both internal and external clients
(internal clients <25, number of external clients is unknown (>25)
)

Hoping this description will be helpful enough for
you...
(I am looking for a very high availability
system)

Thank you in advance for your support.
This licensing question is pretty much urgent (for
a bid) : a quick answer would be very appreciated...

View 7 Replies View Related

Mirroring, Snapshot Replication, Load Balancing And Other BIG Questions

Mar 22, 2006

You will all have to excuse my ignorance. I'm a developer who also doubles up as a development DBA. I am however not particularly knowedgeable about all the really important DBA stuff.

We've built a small BI solution using SQL Server 2000. Our problem is that our server is getting on in years (5) and doesn't really have enough disk space or grunt. We havce a number of summary cubes that we've optimised quite successfully but our billing line level cubes run to 60 million rows and, well, they're about as quick as a dead ferret. Especially given the stupid queries our data analysts keep running.

We have however proved our point. That this can be done and indeed SQL Server can do it. So we're now looking at some infrastructure spend and some new copies of SQL2005.

But i need some advice. Our user base is climbing through the roof, we originally had 10, now we have closer to 50 and at this rate it'll be a couple of hundred by the end of the year. We're using a plugin called XLCubed to deliver that data into Excel from the Analysis Server.

The OLTP database that sits behind it is fairly robust but we have a number of web based apps (mostly lookup systems) that want to use the nice shiny new accurate tables of data we have created.

So I'm looking at a fairly big server to hold the OLTP DB, this will also serve up live data to our web apps. Its worth pointing out that the source data system is a batch system that processes overnight so we load data from yesterday at 6pm each evening and process our cubes and stuff overnight. Thus the data is a couple of days out of date. Don't laugh they used to use MS Access and got one mangy data set a month so this is a massive leap forward.

I wanted to mirror the DB to another machine but I also want to have a separate Cube Server. I wondered if the cube server could use the mirror to read its data from as opposed to loading the Main Server (the mirror would be an identical box) we would also have a separate box running some of our other systems acting as the witness.

I also wonderd about exporting the Cubes onto file shares for use locally as opposed to via the server which is how they connect now.

We have been using Reporting Services and some of the queries the devs write are not exactly efficient. So I was also planning on clustering a pair of smaller servers into a reporting farm. Could I use another SQL Server to serve data up to them? Could I use a DB snapshot to copy the data required to this server? What are the time / size implications of using a snapshot and replicating it over each night?

Any suggestions for places to read up on this? I've looked at the MS marketing blurb and while its big on buzzwords its light on specifics. Like how it actually works and how you would actually configure it to do some of this and what the implications would be.

Any advice?



many thanks


Steve



View 4 Replies View Related

DR: Replication Vs. Log Shipping Vs. Clustering Vs. Database Mirroring........

Aug 8, 2006

Up to now we have gotten by without having any local DR copies of servers (if a sql server goes down we are usually able to get it back in less than 3 hours). But I want more now. I want to trim the "down" window to no more than 5 or 10 minutes. (Immedate failover would be nice but is not an essential requirement. The essential requirement is to loose no data!)

I have a spec of knowledge in these areas:

SQL 2005 Clustering (requires approved hardware, quorum disk, etc. involved)

SQL 2005 Replicaiton

SQL 2005 Log Shipping.


SQL 2005 Database mirroring. ( needs three servers)



Which approach do you think is the most straightforward, sparing of hardware, yet reliable way to get us back up and running after a sql server failure.



TIA,



barkingdog

View 8 Replies View Related

Load Balancing

Feb 27, 2006

Hello,
 
How is load balancing working in SQL Server 2000. We want to have two different servers as a back end for our web based eCommerce application. Is this possible? While there be any modification requirement in the application?

View 4 Replies View Related

Load Balancing

Nov 6, 2000

What is the meaning of Loadbalancing and how can I use this term to my advantage.
Q2. How would I know that I need to do load balancing and how can I set it up.

I have wrote load balancing in BOL and did not get any thing on it. can anyone refer me to where I can read more about it.

thanks

Ahmed

View 1 Replies View Related

Load Balancing In SQL 7.0

Dec 14, 1999

Hi All,

We have a Intranet Application which uses IIS / SQL Server 7.0, we are trying to support the application for 30,000 + users.

Since SQL Server can only support upto 32676 user connections (Theoretically), is it possible achieve this with clustering / dynamic load balancing. Does MSCS and SQL 7.0 support this. Any other ideas, 3rd party software etc?????

Any input will be of great help.

Thanks

NK

View 1 Replies View Related

Load Balancing

Jul 20, 2005

How much traffic/load can a database server running MS SQL server takebefore it can't handle it anymore? And when that time comes, what are therecourses? Am I able to load balance it between separate servers?

View 3 Replies View Related

Load Balancing

Sep 9, 2007

need advice for below scenario

currently am having a active / passive cluster sql 2000 server, due to the amount of transactions am moving to new high end servers with sql 2005 cluster.

incase the new cluster also doesnt stands the load what approach i should use similar to load balancing.

with regards
alby peter

View 4 Replies View Related

About Load Balancing

Apr 18, 2008

I want to know about Data Base Load balancing work. How to do that? I want to know that.

Please help me........

Reagrds
Binod Kumar Tiwari
+91 9958680044

View 1 Replies View Related

Load Balancing

Aug 14, 2007

Why SQL Server 2005 Dosn't Supoort Load Balancing?
I think Load Balancing is a feature of great necessity.
Would it be probably added to other features in Next versions?
thank every one

View 5 Replies View Related

Configuration/load Balancing

Jan 25, 2006

HiOn our SQl Server (2000) we can have queries that take at least 10-20mins(require full table scan fo billion row table). When one of these queriesis running it substantailly slows down very quick queries (sub secondqueries take several seconds).I believe there is no way to set priorities but was wondering if thereare other configuration settings that could help. The server is dualprocessor so maybe setting maxdop to 1 will help. Memory size isdynmaically assigned up to 4Gb but as the DB size is > 1Tb I'm not sureif allowing this much memory is actually decreasing performance when thequick query trys to get through.Any suggestions.ThanksMike

View 8 Replies View Related

Load Balancing Cluster

Nov 30, 2007

need the concpet of load balancing cluster ?can any one help me out with any link....?

View 1 Replies View Related

Query Load Balancing

Apr 10, 2006

Hi,

We have a massive database that users run complex queries on. The queries are never the same so caching cannot be used too much effect. When many queries are run at one time by the various users the system slows down.

What we are trying to achieve is maybe have a primary SQL Server 2005 machine and multiple (4-5) SQL Server 2005 secondary machines. The primary machine would get the query and would decide based on loads as to which secondary machine would fulfill the query.

We are not worried about failover and clustering and all that fun stuff. Our primary concern is to achieve the quickest execution of the query. We were wondering if SQL Server 2005 has any feature that enables query load balancing of some kind.

Thanks

View 1 Replies View Related

Load Balancing And Fail Over For SQL Server.

Jun 7, 2005

Hi!Currently we only have one SQL Server database in our production system. I would like to add one more SQL server 2000 database. I would like to configure them so that both server share the load and Failover. I did some research and I found that I can do this by installing the OS (Windows 2000 Server Enterprise Edition) and SQL 2000 Enterprise Edition on both machines using the windows clustering. I want both servers to be active. They both have a copy of the production data. What I don't know is that, if it is possible to synchronize the data on both databases using SQL Server Replication utitlites. From what I know one SQL Server must be Publisher and one Subscriber. Can one sql server be both? Because I want both sql server to be identical. Can I set up replication between more than 2 servers? We just need to add one server for now but I would like it to be expandable. In the future we may need more.So Please provide me some ideas and answers about the following.1- Can two SQL Server cross replicate (both update each other in order to be identical)?2 -Does replication work beyond 2 servers.?3- If you were to set up a production database; what do you recommend considering the load balancing and Failover using Windows 2000 Server Enterprise Edition and SQL Sever 20000 Enterprise Edition?Thanks,Michael

View 3 Replies View Related

Load Balancing Or Seperate Databases

Oct 15, 2007

We are looking at expanding our web application (C# and Sql Server). The module we are going to be adding will be far more processor intense (plotting and modeling) than any of the current code.

I need to know what the best design for this add on would be.

Would it be better to
a) Keep the new module as part of the core db (entire application on 1 database) and use loading balncing between 2 (or more) servers to handle the huge increase in demand?
b) Create the new module as a seperate database that runs on a seperate server?

If B is the best option (I am thinking it is not) is there a way to write easy and efficient queries and views of the 2 databases as 1.

Thanks

Justin

Justin Bezanson
www.justinbezanson.com
www.geekdaily.net - web development and technology blog and news
www.offsidegames.com - free online flash games
www.thrufare.com - free proxy website

View 4 Replies View Related

How Can I Cluster Sql 2005 For Load Balancing?

Nov 6, 2006

i have a table with 10,000,000,000 records and i need Select and Insert many
records from or into this table in less than one second.
i can't buy a very expensive hardware(Server) for this SQL Server 2005
but i can buy many medium price hardwares(Servers) for this SQL Server
2005.
how can i distribute or cluster this table between many hardwares(Servers)?
note: i have few users (maximum 5 users) for my database but i have a
very large table and Sql server 2005 server need to respond to this
users in less than 1 second.
i want to distribute this huge table in seperated hardwares. becuase i
can't buy a very expensive hardware from my server but i can buy many
medium price hardware for my server.
note: i need this: when a user run a select query on this huge table
his/her request distribute between many hardwares not one hardware.

View 3 Replies View Related

Load Balancing Using Service Broker

Jun 29, 2006

I am trying to achieve load balancing through service broker.

I have a 3 server setup:

server1:

Source Database where the requests are sent. This has two routes created on this to server 2 and 3 with the service broker instance id specified. The service broker matches both these routes to the destination databases on Server 2 & 3 and send the requests in a round robin fashion by itself.

server2 & 3: These servers have the destination databases. The databases are exactly same with same messages, contracts, queues, services, and servicing programs. These don't send the responce back and the source is also not expecting for any response from the target databases.

When I tested this scenario it was behaving in a different manner. If there are 20 requests, in a normal case 10 requests should go to either of the destiantion servers, but I found that sometimes it was 11-9 or 12-8 only once did it do a 10-10.

Also I want to know is it best practice to rely on the inbuilt feature of the service broker in matching the routes and sending the messages rather than doing it ourselves. I which case one has to write the logic for sending the message to appropriate target server based on the status of the destination queue.

I also want to know which would be the best technique to use to bring the two destination databases in a concurrent state.

thanks

ashok

View 1 Replies View Related

Log Shipping: How To Failback After A Failover Log Shipping?

Jun 8, 2006

Hi,

I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?

I did the failover as follow:

I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following

1) Stop the primary database by using sp_change_primary_role (Sv1)

2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)

3) Change the monitor role by running sp-change_monitor_role (Sv2)

4) Resolve the log ins - (Sv2)

5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.

I appreciate any suggestion.

Thanks in advance

View 5 Replies View Related

[Q]SQL Server 2000 Cluster Load Balancing

Nov 8, 2001

Hey guys,

Lately we have come across a problem where our application is undergoing some extreme load against the SQL 2000 server database we have setup, where the server is hitting 100% CPU utilization each time. Currently the box is a 2 processor box.

Here is the question I have. I have seen under most SQL Server clusters that an active/passive setup is implemented. Where the passive server just exists as a failover mechanism. What I am looking for is some information on how to setup active/active setup where each server receives processes to handle.

Has anyone created a setup like this? Are there any standard benchmarking tools that can be used to see how this configuration increases performance? Is this setup more favorable than going to a 4 processor server as oppossed to our current 2 processor server?

BTW: We have noticed that after a web application where the user sits idle for a while the SQL Server application loses the connection with the application user...Is this the SQL Timeout causing the connection to disconnect?

Thanx for any response...

Bri

View 1 Replies View Related

SQL 2012 :: Load Balancing And Fail Over Cluster

Jun 13, 2014

Is there such a thing called 'Load balancing' on fail over cluster?

View 2 Replies View Related

SQL Server Admin 2014 :: Load Balancing With Availability Groups

Aug 28, 2014

We are having a conversation at work and the subject of load balancing with SQL came up. Right now we are running SQL Server 2014 on four (4) machines. I am using a AlwaysOn with Availability Groups (AG). Now I know that we can scale out the reading in AG by allowing the secondary serves to receive reads.

Is there a way to be able to do this with writes? Can I have in essences 2 masters that some how reconcile with each other? We are expecting a huge amount of writes in the near future and we need a way for SQL to handle the amount of traffic we are expecting with out any issues.

I explored the possibility of Peer - to - Peer replication; however, it seems that it would be more work if we are constantly making updates to the database scheme.

View 2 Replies View Related

Microsoft OLE DB Provider For SQL Server Error '80004005' On Load Balancing

Jul 7, 2007



I have posted this also in one forum. Somebody might have idea here.

We have four Windows 2003 advance servers with SP 3 configured on NLB.
Each of them has one NIC.
Recently we started to get frequent time-out error messages from web applications on those servers when they try to establish the connection with the SQL 2005. If you run web pages application to connect to sql server 10 times, 9 times you will get good connection results and 1 time you will get time-out.

Microsoft OLE DB Provider for SQL Server error '80004005'
Timeout expired
/common/mypage.asp, line 20

Connection to sql server is established with the connection string. Any idea how to solve this problem? Is there a connectionproblem? from NLB to Web to SQL?


We have tried doing some suggestions (e.g. LMHOST, IP, domain account for WEb to SQL connection, the Query Wait in SQL advance property.), but seems this timeout expired still exist



Thanks in advance...

View 3 Replies View Related

MS SQL Clustering And Failover

Jul 20, 2005

I have a 2 node Microsoft 2000 cluster with a shared storage device. Iwant to create automatic failover for MS SQL 2000 server. I can dothat wit the following options:1. Active/Pasive (one instance installed)2. Active/Active (More then one instance/virtual sql serversinstalled)I have a question about option 2.Is it possible to have more then one instance/virtual SQL server,service only one database for example loadbalancing. ?, or when youcreate multiple instances/virtual SQL servers, does that mean you havemultiple databases ? If not why does a Virtual SQL server has to betaken over by a cluster node that didnt fail. Then you can actuallyuse option 1. Please advise!The documentation is not very clear on this issues.Regards,Nico de Groot

View 1 Replies View Related

SQL - Clustering For Failover SQL - Server

Sep 24, 2002

Hi all,

We are the studying the possibilty of implementing SQL - Clustering for
Failover server.

Are there any third party tools readily available.
Any suggestion or checklist ??

Any suggestions or comments will be appreciated.

Thanks and have a great day.

View 1 Replies View Related

Failover Clustering Without SAN && SCSI ?

Jul 20, 2005

Dear Friends,I wanted to configure Failover cluster for SQL Server 2000 on Windows2000 advanced servers.I have only 2 no.s of windows 2000 advanced server m/cs. I dont haveany shared disks and SCSI drives. Can I still configure the Failoverclustering?I would like to know more about the need for the shared disks in theFailover clustering.Please shed some light.RegardsKumar

View 1 Replies View Related

Failover Clustering Metadata

May 24, 2007

Does anyone know how to obtain the physical server name that a SQL failover cluster instance is running on through the system tables or other database commands? Thanks in advance.

View 3 Replies View Related

Clustering - Effor With Restore After Failover

Feb 20, 2000

I have two SQL 7.0 SP1 (WinNT 4.0 SP 6.2) Servers using Clustering.

Problem: When SQLBox1 goes down, the Failover sends it to SQLBox2. When
SQLBox1 comes back up, the node stays on SQLBox2, instead of going back to
SQLBox1. This happens over 50% of the time, but not every time. Sometimes
it works fine.

I have done 3 hours of research, and can not find anything on this problem,
can any one a tleast point me in the right direction. I can hardly find any
troubleshooting on this issue. I do have January 2000 of the MSDN Library
installed on my machine if that helps, but there was not much on it in there
either (at least not that I found).

View 3 Replies View Related

MS Clustering Vs Hardware Failover Blades

Sep 29, 2004

:confused:

In evaluating/comparing MS Clustering vs Hardware Failover Blades, has anyone come across any disadvantages/concerns for a Hardware Failover Blades.

Why would you install MS SQL 2000 Clustering when you can use a Hardware Failover Blade and use a SQL 2000 standard edition. e.g In an event a hardware failure occurs the admin manager brings over a standby hardware blade and reassigns it to the SAN (on which OS and SQL 2000) is installed and you are up and running.

Would appreciate any comments and suggestions to compare these solutions.

Thanks

View 1 Replies View Related

How To Configure Email In Failover Clustering

Jun 10, 2015

how to configure email in SQL server failover clustering. I need to configure email to send when node switches from one node to another. best way to configure email in fail over clustering?

View 1 Replies View Related

Recovery :: Failover Clustering 2008R2

Jul 22, 2015

I tested the failover clustering for SQL 2008R2.When I stop the SQL server services manully, the failover did not fail to another resource. At the Faiolover cluster manager, SQl server(MSSQLSERVER)  only show the status for offline. I think it should be move to another owner intead of just show as offline.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved