Does SQL server support failover without sahred disk or external storage

Hi fellow,
I'd like to know if is it possible to configure SQL Server in a cluster enviroment to provide failover, without using shared disks or external storages.
What I wish is setup and cluster to provide failover in case of a hardware malfuction only between servers A and B. The data "integrity" however will be guarantee by my application. I only wish to provide my clients an transparent and automatic way to c
onnect to server B in case of failure of server A, behaving just like the virtual server in a cluster enviroment.
Anyone knows if this is possible?
Cheers,
Eric.
SQL does not have such a feature. The SQL failover cluster model requires a
common dagta store that can be arbitrated between nodes so that a single
node failure is recoverable.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Eric" <anonymous@.discussions.microsoft.com> wrote in message
news:8F02A739-E588-4C27-9CF2-63999EFBE802@.microsoft.com...
> Hi fellow,
> I'd like to know if is it possible to configure SQL Server in a cluster
enviroment to provide failover, without using shared disks or external
storages.
> What I wish is setup and cluster to provide failover in case of a
hardware malfuction only between servers A and B. The data "integrity"
however will be guarantee by my application. I only wish to provide my
clients an transparent and automatic way to connect to server B in case of
failure of server A, behaving just like the virtual server in a cluster
enviroment.
> Anyone knows if this is possible?
> Cheers,
> Eric.
|||Yes, SQL Server does support failover in that situation. It will not
operate exactly like a virtual server in a cluster environment.
You can use either replication or log shipping to create the second copy of
the database on Server B. Each will have latency as far as what data
exists. Each one has different processes for failing over as well as
failing back. If you search back across this newsgroups and the replication
newsgroup for the last 2 months, there will be more than enough information
posted about that subject.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Hi Michel,
The replication and log shipping solves the problem of keeping data between servers ok, but my point is related to the client.
Is there a way to make the client service start using the second server automatically in case of server A failure? For example, my apllication use ODBC to connect to DB_SERVER, wich will be directed to Server A only if it is availble. If not, then DB_SE
RVER will point automatically to Server B. The Client is unware of any change, just like a failover cluster.
Cheers,
Eric
|||Not in the current version of SQL Server. However, database mirroring in
Yukon may be of interest to you. Have a look here:
http://www.microsoft.com/technet/pro...n123121120120.
Of course, the downside is that Yukon won't be available until next year...
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric" <anonymous@.discussions.microsoft.com> wrote in message
news:32D7E78D-55B4-4B49-A1FE-2385D41E9BA0@.microsoft.com...
> Hi Michel,
> The replication and log shipping solves the problem of keeping data
between servers ok, but my point is related to the client.
> Is there a way to make the client service start using the second server
automatically in case of server A failure? For example, my apllication use
ODBC to connect to DB_SERVER, wich will be directed to Server A only if it
is availble. If not, then DB_SERVER will point automatically to Server B.
The Client is unware of any change, just like a failover cluster.
> Cheers,
> Eric
|||Nope to the original question and no even to database mirroring.
If using replication or log shipping, you will have to write code into you
application to detect it, redirect, and reconnect.
When Yukon ships and if database mirroring is in the product, when the
primary fails, you will be disconnected. You will then have to reinitiate
the connection. You just won't have to redirect to a different server name.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||From Lubor's presentation at the PASS Summit (public info here), there is
supposed to be client library awareness so that an automatic, transparent
shift happens with database mirroring.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:%23ukY8TiLEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Nope to the original question and no even to database mirroring.
> If using replication or log shipping, you will have to write code into you
> application to detect it, redirect, and reconnect.
> When Yukon ships and if database mirroring is in the product, when the
> primary fails, you will be disconnected. You will then have to reinitiate
> the connection. You just won't have to redirect to a different server
name.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
|||Yes, there is supposed to be client library awareness. The feature is
currently called "transparent client redirect". The transparent redirect
part is that an application doesn't need to change names. It is called
"redirect". It is not called "reconnect". The information is in the PASS
session you refer to. The same basic information with a few extensions is
also contained in a database mirroring session I did in London at DevWeek as
well as in Vienna at XtremeSQL.
There is also a very big word of caution here since this has the potential
to head the wrong direction. Yukon is still in Beta 1. Beta 1 is still
NDA. The only thing allowed to be discussed is what is publicly released.
Outside of that, there are a small group of people authorized to talk about
it. At this point, it is best to not even discuss Yukon features or
functionality. If there are specific questions on Yukon, they can be asked
in "other venues".
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I am very aware of my NDA obligations on Yukon. I only discuss items where
I can refer to a public statement by an authorized Microsoft representative,
such as the Beta 1 presentations at PASS.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:%23O$GGamLEHA.3324@.TK2MSFTNGP10.phx.gbl...
> Yes, there is supposed to be client library awareness. The feature is
> currently called "transparent client redirect". The transparent redirect
> part is that an application doesn't need to change names. It is called
> "redirect". It is not called "reconnect". The information is in the PASS
> session you refer to. The same basic information with a few extensions is
> also contained in a database mirroring session I did in London at DevWeek
as
> well as in Vienna at XtremeSQL.
> There is also a very big word of caution here since this has the potential
> to head the wrong direction. Yukon is still in Beta 1. Beta 1 is still
> NDA. The only thing allowed to be discussed is what is publicly released.
> Outside of that, there are a small group of people authorized to talk
about
> it. At this point, it is best to not even discuss Yukon features or
> functionality. If there are specific questions on Yukon, they can be
asked
> in "other venues".
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>

Does placing the transaction log on dedicated RAID volume make sense with Simple Recovery

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
DavidLarry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the log after each
transaction. It truncates after each time it performs a checkpoint (read about checkpoint in Books
Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com...
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would NOT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the next
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>sql

Does placing the transaction log on dedicated RAID volume make sense with Simple Recovery

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
David
Larry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the log after each
transaction. It truncates after each time it performs a checkpoint (read about checkpoint in Books
Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com...
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would NOT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the next
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>

Does placing the transaction log on dedicated RAID volume make sense with Simple Recov

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
DavidLarry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the l
og after each
transaction. It truncates after each time it performs a checkpoint (read abo
ut checkpoint in Books
Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com.
.
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would N
OT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the ne
xt
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>

Does format file for bulk insert allow mix of native and character format?

I tried to place this question to the .Net framework Data Access and Storage forum and got no answer, so I am trying to move it on this forum.

So I have a module which require me to import big amount of data. I believe that the native format data files with format files will be the most efficient way of implementation. I am trying to programmatically produce a BCP like exported files of native format(it means without type conversion) from tables with nullable and nonnullable values.I prefere to be able to not produce computed or identity or rowguid fields,so I need format files.

I don't have problems producing different kinds of int or float( which are the majority of fields) or char or nchar fields.

Problems are emerging with the datetime or smalldatetime or decimal fields because I don't know how to convert to them from the strings or from the CLR types.

So I trying to find a way to find a native format of those fields or to find if a bulk insert will accept mixed format files with some of the fields in the native format without field terminators and some with field terminators or to use char format with field terminators only plus maybe format files.

So if the answer to above question is positive I can partially resolve the problem, if negative I will have to use the character format.

Unless you can educate me on the convertion to the SQL server internal formats of datetimes and decimals from the CLR types.

See SQL Server 2005 Books Online topics:

SQL Server Data Types and Their .NET Framework Equivalents
http://msdn2.microsoft.com/en-us/library/ms131092.aspx
Specifying File Storage Type by Using bcp
http://msdn2.microsoft.com/en-US/library/ms189110.aspx

Data Type Conversion (Database Engine)
http://msdn2.microsoft.com/en-us/library/ms191530.aspx

Document Storage inside DB?

Has anyone got any experience or opinions about the
pros/cons of storing Office Type Documents inside an
image field in the database as opposed to storing a URL
pointer to the file in the filesystem?
Assuming there will be several GB of documents Im
thinking it would be better to keep these out of the
database.
AdamA common approach is to :-
Store the documents on a file server
Store a pointer to the Documents in the DB (to aid searching)
HTH
Ryan Waight, MCDBA, MCSE
"Adam" <atoth@.msdnspam.com> wrote in message
news:07ab01c3b4d3$2e07d430$a501280a@.phx.gbl...
> Has anyone got any experience or opinions about the
> pros/cons of storing Office Type Documents inside an
> image field in the database as opposed to storing a URL
> pointer to the file in the filesystem?
> Assuming there will be several GB of documents Im
> thinking it would be better to keep these out of the
> database.
> Adam
>|||Hi,
We are providing content management software that's using such an approach -
but we are using both DB and FileSystem as a storage for the documents. For
storing the documents inside the database, we use a "document-breaker" to
split the documents in meaningfull parts (e.g. the html documents are split
in tags and text).
The advantages we have with the documents inside the database:
- one single resource that contains the whole system
- easy backup
- depending on the document splitting, we can track and validate cross
document references
The advantages we have by also storing the documents in the file system:
- fast document access (knowing the ID, we don't need a query to the DB)
- fast search capability (using Windows Index Services)
By the way: we have productive systems using this approach with 2000+
documents.
Sven
"Adam" <atoth@.msdnspam.com> wrote in message
news:07ab01c3b4d3$2e07d430$a501280a@.phx.gbl...
> Has anyone got any experience or opinions about the
> pros/cons of storing Office Type Documents inside an
> image field in the database as opposed to storing a URL
> pointer to the file in the filesystem?
> Assuming there will be several GB of documents Im
> thinking it would be better to keep these out of the
> database.
> Adam
>|||I see two approaches that use a "file system" is this the
Windows "file system" that you are referring to and if
so... how do you insure the documents are not deleted?
>--Original Message--
>Has anyone got any experience or opinions about the
>pros/cons of storing Office Type Documents inside an
>image field in the database as opposed to storing a URL
>pointer to the file in the filesystem?
>Assuming there will be several GB of documents Im
>thinking it would be better to keep these out of the
>database.
>Adam
>.
>|||>--Original Message--
>I see two approaches that use a "file system" is this
the
>Windows "file system" that you are referring to and if
>so... how do you insure the documents are not deleted?
>
Yes - I do mean the Windows File System; in answer to
your question you could disallow Delete rights (assuming
NTFS) - Ive never tried it but the option is there.
Adam|||Thanks for the reply - do you think it would still work
well with say 100,000 documents?
Adam

Document Storage

Hello Everyone and thanks for your help in advance. I am developing a document storage application for an intranet that will store various Word, Excel, and PDF documents. Most of the examples I see utilize SQL Server and an image field rather than the FileSystem Object to store documents. My concern with this method is that some of the documents may be several hundred pages (not exactly sure of the actual file size yet, but they must be fairly large). My question is, where does the use of SQL Server become impractical for this type of application? Any insight would be greatly appreciated. Thanks.I'm no expert on this but the guy quoted below is :) Image is the right data type for your purpose. You should do some reading on this because there are some considerations about transferring the large amount of data along with storing it.

"In short, should you consider storing images in a database? If you need to frequently edit the images, I suggest storing the images as separate files. If the size of the images are very large (for example, hundreds of megabytes), I suggest storing the images as separate files. If your images are essentially read-only and relatively static, and if you measure the size in kilobytes or a few megabytes, you can consider storing your images in the database."

Programming Microsoft ASP.NET
Chapter 22 - Working with Images in ASP.NET
by Dino Esposito

Joe