Re: High availability

I agree with Andrew. Having someone who can help you down the clustering and high availabiltiy path is a small price compared to getting it wrong. The best consultants teach and guide while designing and building.

And yes, I am also a consultant, but I spent over fifteen years on the full-time side of the fence.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP




"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message news:eTrBJC%23wHHA.2432@xxxxxxxxxxxxxxxxxxxxxxx
I would start here: http://www.microsoft.com/sql/technologies/highavailability/default.mspx
But if you are serious about a system of this size and want to do it right I would definitely recommend brining in a good consultant to get you started on the right path. And this isn't just because I am a consultant:).

--
Andrew J. Kelly SQL MVP

"Suri Nagarajan" <SuriNagarajan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:79FA8260-CDB5-4E99-AB1E-58DFF7008D6F@xxxxxxxxxxxxxxxx
Thanks Geoff and Andrew for the good info, I guess my best bet is to scale up
rather than scale out and makesure the database is tuned right.

Is there any good book you guys might suggest which could walk me thru on
setting up sql server failover clustering, replication, High availability,
etc., ?

Suri.

"Andrew J. Kelly" wrote:

Just to clarify that some. Geoff is 100% correct in that SQL Server does not
have the implementation of multiple dbs such as Oracle Rac. But as I eluded
to earlier there are two types of replication that you may have heard of
that some people relate to this. This is Merge and bi-directional
replication. Each allows multiple SQL Servers to have a full or partial copy
of the db and has the ability to update the others with changes. But this is
not typically a scale out solution for performance so I am not advocating it
just making you aware of what SQL Server has to offer and hopefully avoid
some confusion.

--
Andrew J. Kelly SQL MVP

"Geoff N. Hiten" <SQLCraftsman@xxxxxxxxx> wrote in message
news:%23q0Z6N8wHHA.3756@xxxxxxxxxxxxxxxxxxxxxxx
> There are no native multiple-master database implementation > technologies
> for SQL Server.
>
> -- > Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
>
>
>
> "Suri Nagarajan" <SuriNagarajan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> message news:91A0A7A4-536D-45AF-8C7E-CCFAE8C43CB9@xxxxxxxxxxxxxxxx
>>I think you are right regarding scaling up, now I have to convince my
>>client
>> to invest in bigger and better hardware for hosting the database, not >> one
>> but
>> two or more of similar hardware for fail over server. Thanks for the
>> info,
>> it was really useful.
>>
>> But just for my understanding - in SQL server is there an >> implementation
>> like Oracle to have multiple master database in different servers >> with
>> each
>> database mimicking the same tables,structure and data which gets
>> automatically synchronized among themselves, so that different users >> can
>> connect to different servers and still be looking at the same data?
>>
>> "Andrew J. Kelly" wrote:
>>
>>> I have worked on many systems with thousands of users on a single >>> server
>>> with no problem given the right configuration. Both scaling up and >>> out
>>> have
>>> pros and cons but unless the system is read only scaling out is much
>>> more
>>> difficult than scaling up in most cases with SQL Server. These days >>> you
>>> can
>>> scale up to a 64 processor system with 1TB of memory so hitting the >>> max
>>> performance limits are pretty hard to do if done correctly.
>>>
>>> -- >>> Andrew J. Kelly SQL MVP
>>>
>>> "Suri Nagarajan" <SuriNagarajan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>>> message
>>> news:5AA822BD-FD7A-47A5-8D01-9455D22C2149@xxxxxxxxxxxxxxxx
>>> > Yes, I did think of scaling up as an option. I have been dealing >>> > with
>>> > DB2
>>> > and Oracle based systems in the past , I recently took over this >>> > SQL
>>> > server
>>> > based system, I am trying to understand/explore all options >>> > available
>>> > (since
>>> > I am not sure about the options available in SQL server >>> > environment).
>>> >
>>> > Currently we are dealing with user environment with couple of >>> > hundred
>>> > users
>>> > which is not a problem, the application is running smooth. But in >>> > the
>>> > near
>>> > future this system has to be deployed in an environment where >>> > couple
>>> > of
>>> > thousand users will be accessing the system (huge jump in volume >>> > of
>>> > data
>>> > and
>>> > number of transactions).
>>> >
>>> > I feel if I scale up, we will be hitting the max performance >>> > limits
>>> > soon
>>> > due
>>> > to sudden increase in number of users. I think scaling out will >>> > be
>>> > more
>>> > flexible to add more power in the future if needed. Both High
>>> > availability
>>> > and performance are critical requirements since its an on-line
>>> > transactional
>>> > system.
>>> >
>>> >
>>> >
>>> > "Andrew J. Kelly" wrote:
>>> >
>>> >> FYI, Replication can be used for non-read only servers but is >>> >> usually
>>> >> not
>>> >> a
>>> >> good choice for scaling out in order to get performance gains. >>> >> You
>>> >> need
>>> >> to
>>> >> separate the two requirements (Performance & High availability)
>>> >> somewhat
>>> >> as
>>> >> they are typically two completely different solutions or >>> >> approaches.
>>> >> What
>>> >> is wrong with scaling up? How large is the server now and what >>> >> do
>>> >> you
>>> >> need
>>> >> to support? And are you sure it has been tuned properly? You >>> >> might
>>> >> not
>>> >> even
>>> >> have the need to scale up or out.
>>> >>
>>> >> -- >>> >> Andrew J. Kelly SQL MVP
>>> >>
>>> >> "Suri Nagarajan" <Suri Nagarajan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote >>> >> in
>>> >> message
>>> >> news:BEFF51E6-28E0-4784-A24C-0DBB2C4B497F@xxxxxxxxxxxxxxxx
>>> >> >I am new to SQL server, I would appreciate any help on following
>>> >> >question.
>>> >> >I
>>> >> > tried researching and found few options (detailed below after >>> >> > the
>>> >> > question)
>>> >> > which didn't really help what I am trying to do. Any suggestion >>> >> > for
>>> >> > the
>>> >> > Gurus
>>> >> > will be helpful.
>>> >> >
>>> >> > I am trying to implement a high availability system which will >>> >> > have
>>> >> > very
>>> >> > high database access traffic - potentially couple of thousand >>> >> > users
>>> >> > trying
>>> >> > to
>>> >> > access a transaction system (read and/or update by most users).
>>> >> > Unfortunately this is not a new system, its an existing system >>> >> > with
>>> >> > around
>>> >> > 200 tables implemented in a single server (Using SQL server as
>>> >> > database),
>>> >> > due to increase in number of users and traffic I need to find >>> >> > ways
>>> >> > to
>>> >> > increase availability and performance.
>>> >> >
>>> >> > My final system should be implemented on multiple servers and >>> >> > each
>>> >> > server
>>> >> > hosting SQL server databases (same data, tables and structures) >>> >> > but
>>> >> > they
>>> >> > should be automatically synchronized to have same data (just >>> >> > like
>>> >> > multiple
>>> >> > masters in Oracle) so that different users can be connected to
>>> >> > different
>>> >> > server (for load balancing and performance improvement . Is >>> >> > this
>>> >> > possible
>>> >> > in
>>> >> > SQL server?
>>> >> >
>>> >> > I tried following options
>>> >> >
>>> >> > 1. SQL server clustering - this can only be used for fail over >>> >> > not
>>> >> > as
>>> >> > multiple synchronized master databases.
>>> >> >
>>> >> > 2. Replicated server - replicated server can be used only for >>> >> > read
>>> >> > not
>>> >> > for
>>> >> > updates
>>> >> >
>>> >> > 3. Federated database design with distributed partitioned >>> >> > views -
>>> >> > but
>>> >> > its
>>> >> > difficult to split certain databases across servers due to
>>> >> > complexity
>>> >> > and
>>> >> > number of Foreign key constraints defined in the system.
>>> >> >
>>> >> >
>>> >> > Thanks in advance for suggestions/help.
>>> >> >
>>> >> > Regards,
>>> >> > Suri.
>>> >> >
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>






.

Relevant Pages

  • Re: High availability
    ... Thanks Geoff and Andrew for the good info, I guess my best bet is to scale up ... setting up sql server failover clustering, replication, High availability, ... Senior Database Administrator ...
    (microsoft.public.sqlserver.clustering)
  • Re: How to convert the SQL Developer Edition to SQL Standard Editi
    ... drivers point since the server will be the same as long as you upgrade the ... Andrew J. Kelly SQL MVP ... >>> under Database properties->Data Files, ... >>>> you can not avoid rerunning setup for SQL Server to get to Std ...
    (microsoft.public.sqlserver.setup)
  • Re: High availability
    ... I think you are right regarding scaling up, now I have to convince my client ... But just for my understanding - in SQL server is there an implementation ... like Oracle to have multiple master database in different servers with each ...
    (microsoft.public.sqlserver.clustering)
  • Re: Locking
    ... Andrew, Pessimistic is the default. ... > transaction isolation mode and set as with IMPLICIT TRANSACTIONS. ... > SQL Server when you connect. ... >> We are using Borland delphi and bde to communicate with the database. ...
    (microsoft.public.sqlserver.server)
  • Migration Question Again...
    ... Sorry to ask a stupid question again since this is the first time that i do migration. ... I've got response from Andrew. ... for SQL Server ... I am not sure if I should use Copy Database Method to move all the objects or use sp_attached/sp_detached. ...
    (microsoft.public.sqlserver.setup)