Basically, I need to have real active/active cluster
solution on SQL server(any MS platform).
The windows failover clustering tech is share nothing
clustering , so can not do the load balancing stuff(
distribute transaction traffic between servers).
Only product I know on the market is PolyServ's Matrix
server, any suggestions from microsoft?
Thanks
David Liu
MCSE/MCDBA
DBA
www.moneris.com
The purpose of MS Cluster Servers is to provide high availability not load balancing for SQL Server. If you require both you need multiple clusters as a federation of SQL Servers. Another option is a single 4-node cluster with 4 SQL Instances in a feder
ation configuration. As your 4-nodes reach peak utilitisation, scale up the four servers. To explore how federated solutions work start by searching BOL for "federated database servers".
If you only need read-only access to SQL Server data you could build a solution using replication. As your load increases add more subscribers to handle the load.
|||As I know, The federated database is only support
partition data, do not support share database( exactly the
same database, as oracle's real application cluster).
In a word, failover and load balance at the same time;
and can quickly scale out by simply adding a new server.
From BOL, microsoft say do not support this type of
clustering.
Thanks for your reply
David Liu
>--Original Message--
>The purpose of MS Cluster Servers is to provide high
availability not load balancing for SQL Server. If you
require both you need multiple clusters as a federation of
SQL Servers. Another option is a single 4-node cluster
with 4 SQL Instances in a federation configuration. As
your 4-nodes reach peak utilitisation, scale up the four
servers. To explore how federated solutions work start by
searching BOL for "federated database servers".
>If you only need read-only access to SQL Server data you
could build a solution using replication. As your load
increases add more subscribers to handle the load.
>.
>
|||Thanks David for your interest in Polyserve !
Unfortunately, SQL server itself is a shared nothing application,
which cannot benefit from a clustered file system.
A clustered file system allows all the nodes in the cluster to see
(share) the same file with full read/write access,
but unlike IIS / CIFS applications which can use a clustered file
system to load balanced access (scale out) across all the nodes in the
cluster,
a SQL server instance data and log files is not shared with other SQL
servers instances on other nodes.
But, there are some benefits to gain from the use of a shared file
system for SQL HA
1) a clustered file system is mounted on all the nodes, this
guarantee a seamless transition of the instance in case of disruption,
rather than a typical "failover" scenario with a forced unmount, etc.
2) Large, heterogeneous, node count (16 node SQL Server clusters that
can mix 2p, 4p, and 8p servers)
3) No drive letter limitations
4) Easy storage management
5) Easy management of raw data file for ETL . I.e. you can store all
your
exports in one folder which is seen by all the nodes.
6) Off host backup. All the instances can backup into one location,
and one node can copy the backup to tape.
For more information : http://www.polyserve.com/sql
Tomer Meshorer
Database Engineer
Polyserve
"David Liu" <david.z.liu@.moneris.com> wrote in message news:<197f901c41cb5$ff95c440$a501280a@.phx.gbl>...
> Basically, I need to have real active/active cluster
> solution on SQL server(any MS platform).
> The windows failover clustering tech is share nothing
> clustering , so can not do the load balancing stuff(
> distribute transaction traffic between servers).
> Only product I know on the market is PolyServ's Matrix
> server, any suggestions from microsoft?
> Thanks
> David Liu
> MCSE/MCDBA
> DBA
> www.moneris.com
|||SQL Server does not support multiple machine access to SQL Server database
files. This is a protection mechanism which ensures that processes from
different machines can not step on each other and introduce corruption into
the data files.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
Showing posts with label basically. Show all posts
Showing posts with label basically. Show all posts
Thursday, March 22, 2012
Monday, February 13, 2012
Accessing SQL Server 2000 in .NET
There are basically two ways of doing this offered by .Net
environment: using SqlDataClient and OdbcClient. My question is:
what are the advantages of using Sql client over Odbc client?
I understand that specialized classes are supposed to be
optimized for transfer protocol and that Odbc is an additional
layer which I supposedly pass when using Sql client. But are
the differences significant? I performed simple tests and found
out that Sql approach is really faster (about twice) when you
need to return large amount of short rows, like e.g. you do
SELECT * FROM from a single column table of integers, which
situation is not going to happen often. The same query against
big table with long string data, or query returning reasonable
number of rows do not reveal superiocity of the Sql client.
Are there other situations you can think of, when Sql client
beats Odbc badly? I mean at least 20% performance gain.
Thank you.http://whidbey.msdn.microsoft.com/l...6bdb6dcb131.asp
http://www.sql-server-performance.c...performance.asp
AMB
"Sericinus hunter" wrote:
> There are basically two ways of doing this offered by .Net
> environment: using SqlDataClient and OdbcClient. My question is:
> what are the advantages of using Sql client over Odbc client?
> I understand that specialized classes are supposed to be
> optimized for transfer protocol and that Odbc is an additional
> layer which I supposedly pass when using Sql client. But are
> the differences significant? I performed simple tests and found
> out that Sql approach is really faster (about twice) when you
> need to return large amount of short rows, like e.g. you do
> SELECT * FROM from a single column table of integers, which
> situation is not going to happen often. The same query against
> big table with long string data, or query returning reasonable
> number of rows do not reveal superiocity of the Sql client.
> Are there other situations you can think of, when Sql client
> beats Odbc badly? I mean at least 20% performance gain.
> Thank you.
>|||"Sericinus hunter" <serhunt@.flash.net> wrote in message
news:cvd0v8$917$1@.news.monmouth.com...
> There are basically two ways of doing this offered by .Net
> environment: using SqlDataClient and OdbcClient.
Three, actually - you're forgetting OleDb...|||If you are using SQL Server from .NET, use SqlClient. OleDb and Odbc
are there to support other databases, such as Access or non-msft
platforms. SqlClient was designed specifically to take advantage of
SQL Server and can't be used with any other RDBMS. SqlClient allows
you to take advantage of all of SQL Server's functionality and is the
most performant.
Mary
On Mon, 21 Feb 2005 11:07:03 -0500, Sericinus hunter
<serhunt@.flash.net> wrote:
> There are basically two ways of doing this offered by .Net
>environment: using SqlDataClient and OdbcClient. My question is:
>what are the advantages of using Sql client over Odbc client?
> I understand that specialized classes are supposed to be
>optimized for transfer protocol and that Odbc is an additional
>layer which I supposedly pass when using Sql client. But are
>the differences significant? I performed simple tests and found
>out that Sql approach is really faster (about twice) when you
>need to return large amount of short rows, like e.g. you do
>SELECT * FROM from a single column table of integers, which
>situation is not going to happen often. The same query against
>big table with long string data, or query returning reasonable
>number of rows do not reveal superiocity of the Sql client.
> Are there other situations you can think of, when Sql client
>beats Odbc badly? I mean at least 20% performance gain.
> Thank you.|||Alejandro Mesa wrote:
> http://whidbey.msdn.microsoft.com/l...6bdb6dcb131.asp
> http://www.sql-server-performance.c...performance.asp
Thank you, Alejandro (and others).
Indeed, I read about the recommendation to use Sql data
client in many places, but could not find anything more specific
than general words.
Here is the result of the test I performed.
In the test I tried to cover two distinct variables: table size
(in terms of number of rows) and data size (in terms of the size of
a row). I measured the time needed for DataAdapter.Fill call to return,
where the select command was a simple SELECT * statement canned in a
stored procedure.
Each of six tables in the test is a single column table. Each table
is either "small" (100 rows) or "big" (10000 rows). Two tables have
a column of int type, two -- varchar(4000) ("long string") and the
remaining two -- varchar(200) ("short string").
As you can see Sql client is a clear winner only when you need
to return a huge number of rows which is probably not going to
happen very often. In addition, even in this case, there is no
advantage for reasonable row size ("short string").
Could it be that after .NET 1.1 has been released, different
data providers are not that different performance wise as it
was in 1.0 times, when probably all those recommendations were made?
---
| Time, ms
Table Type +--
| Sql Odbc OleDb
========================================
=
Int SmallTb | 1.2 1.6 2.0
---
Int BigTb | 22 48 46
---
LongStr SmallTb | 50 50 51
---
LongStr BigTb | 5200 5400 5400
---
ShortStr SmallTb | 3.8 3.8 4.2
---
ShortStr BigTb | 270 270 280
========================================
=
environment: using SqlDataClient and OdbcClient. My question is:
what are the advantages of using Sql client over Odbc client?
I understand that specialized classes are supposed to be
optimized for transfer protocol and that Odbc is an additional
layer which I supposedly pass when using Sql client. But are
the differences significant? I performed simple tests and found
out that Sql approach is really faster (about twice) when you
need to return large amount of short rows, like e.g. you do
SELECT * FROM from a single column table of integers, which
situation is not going to happen often. The same query against
big table with long string data, or query returning reasonable
number of rows do not reveal superiocity of the Sql client.
Are there other situations you can think of, when Sql client
beats Odbc badly? I mean at least 20% performance gain.
Thank you.http://whidbey.msdn.microsoft.com/l...6bdb6dcb131.asp
http://www.sql-server-performance.c...performance.asp
AMB
"Sericinus hunter" wrote:
> There are basically two ways of doing this offered by .Net
> environment: using SqlDataClient and OdbcClient. My question is:
> what are the advantages of using Sql client over Odbc client?
> I understand that specialized classes are supposed to be
> optimized for transfer protocol and that Odbc is an additional
> layer which I supposedly pass when using Sql client. But are
> the differences significant? I performed simple tests and found
> out that Sql approach is really faster (about twice) when you
> need to return large amount of short rows, like e.g. you do
> SELECT * FROM from a single column table of integers, which
> situation is not going to happen often. The same query against
> big table with long string data, or query returning reasonable
> number of rows do not reveal superiocity of the Sql client.
> Are there other situations you can think of, when Sql client
> beats Odbc badly? I mean at least 20% performance gain.
> Thank you.
>|||"Sericinus hunter" <serhunt@.flash.net> wrote in message
news:cvd0v8$917$1@.news.monmouth.com...
> There are basically two ways of doing this offered by .Net
> environment: using SqlDataClient and OdbcClient.
Three, actually - you're forgetting OleDb...|||If you are using SQL Server from .NET, use SqlClient. OleDb and Odbc
are there to support other databases, such as Access or non-msft
platforms. SqlClient was designed specifically to take advantage of
SQL Server and can't be used with any other RDBMS. SqlClient allows
you to take advantage of all of SQL Server's functionality and is the
most performant.
Mary
On Mon, 21 Feb 2005 11:07:03 -0500, Sericinus hunter
<serhunt@.flash.net> wrote:
> There are basically two ways of doing this offered by .Net
>environment: using SqlDataClient and OdbcClient. My question is:
>what are the advantages of using Sql client over Odbc client?
> I understand that specialized classes are supposed to be
>optimized for transfer protocol and that Odbc is an additional
>layer which I supposedly pass when using Sql client. But are
>the differences significant? I performed simple tests and found
>out that Sql approach is really faster (about twice) when you
>need to return large amount of short rows, like e.g. you do
>SELECT * FROM from a single column table of integers, which
>situation is not going to happen often. The same query against
>big table with long string data, or query returning reasonable
>number of rows do not reveal superiocity of the Sql client.
> Are there other situations you can think of, when Sql client
>beats Odbc badly? I mean at least 20% performance gain.
> Thank you.|||Alejandro Mesa wrote:
> http://whidbey.msdn.microsoft.com/l...6bdb6dcb131.asp
> http://www.sql-server-performance.c...performance.asp
Thank you, Alejandro (and others).
Indeed, I read about the recommendation to use Sql data
client in many places, but could not find anything more specific
than general words.
Here is the result of the test I performed.
In the test I tried to cover two distinct variables: table size
(in terms of number of rows) and data size (in terms of the size of
a row). I measured the time needed for DataAdapter.Fill call to return,
where the select command was a simple SELECT * statement canned in a
stored procedure.
Each of six tables in the test is a single column table. Each table
is either "small" (100 rows) or "big" (10000 rows). Two tables have
a column of int type, two -- varchar(4000) ("long string") and the
remaining two -- varchar(200) ("short string").
As you can see Sql client is a clear winner only when you need
to return a huge number of rows which is probably not going to
happen very often. In addition, even in this case, there is no
advantage for reasonable row size ("short string").
Could it be that after .NET 1.1 has been released, different
data providers are not that different performance wise as it
was in 1.0 times, when probably all those recommendations were made?
---
| Time, ms
Table Type +--
| Sql Odbc OleDb
========================================
=
Int SmallTb | 1.2 1.6 2.0
---
Int BigTb | 22 48 46
---
LongStr SmallTb | 50 50 51
---
LongStr BigTb | 5200 5400 5400
---
ShortStr SmallTb | 3.8 3.8 4.2
---
ShortStr BigTb | 270 270 280
========================================
=
Thursday, February 9, 2012
accessing mapped drive via xp_cmdshell
Hello,
I'm having problems accessing a mapped network drive. Basically, I
need to be able to access the g:\ drive from SQL Server (i.e.
master..xp_cmdshell 'dir g:\')
I created a share on my server (\\MyServer\ShareName) and mapped the
network drive G to this. So, from my machine I can do a "start, run",
and type in g:\, and get what I want to see.
However, when I run:
master..xp_cmdshell 'dir g:\
I get a message "The system cannot find the path specified."
I have the MSSQLSERVER account on this server setup to login as me.
Shouldn't this take care of any issues?
I would like to use a UNC share, but unfortunately I can't do that at
this time.
Much appreciate any ideas!
Sylvia
Try specifying the UNC path instead of a mapped drive:
EXEC master..xp_cmdshell 'DIR \\MyServer\ShareName'
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133830852.921653.231970@.g49g2000cwa.googlegr oups.com...
> Hello,
> I'm having problems accessing a mapped network drive. Basically, I
> need to be able to access the g:\ drive from SQL Server (i.e.
> master..xp_cmdshell 'dir g:\')
> I created a share on my server (\\MyServer\ShareName) and mapped the
> network drive G to this. So, from my machine I can do a "start, run",
> and type in g:\, and get what I want to see.
> However, when I run:
> master..xp_cmdshell 'dir g:\
> I get a message "The system cannot find the path specified."
> I have the MSSQLSERVER account on this server setup to login as me.
> Shouldn't this take care of any issues?
> I would like to use a UNC share, but unfortunately I can't do that at
> this time.
> Much appreciate any ideas!
> Sylvia
>
|||Thanks for the reply. Unfortunately using a UNC path is not an option
at this point, as I mentioned above (some hard-coded stuff already
written, can't change right now).
Any other options? Does a network share mapped to a drive just not
work?
Thanks,
Sylvia
|||> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
Sorry, I didn't understand that from your original post.
> Any other options? Does a network share mapped to a drive just not
> work?
A mapped drive can work but it is a kludge. One thing you might try is
establishing a persistent mapped drive. I expect you'll only need to do
this once, unless you change the SQL Server service account or unmap the
drive for that user.
EXEC master..xp_cmdshell 'NET USE L: \\MyServer\ShareName /PERSISTENT:YES'
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133845592.917117.66320@.g43g2000cwa.googlegro ups.com...
> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
> Any other options? Does a network share mapped to a drive just not
> work?
> Thanks,
> Sylvia
>
|||That did it! Thanks much - it's just the kludge I needed to get past
this.
I'm having problems accessing a mapped network drive. Basically, I
need to be able to access the g:\ drive from SQL Server (i.e.
master..xp_cmdshell 'dir g:\')
I created a share on my server (\\MyServer\ShareName) and mapped the
network drive G to this. So, from my machine I can do a "start, run",
and type in g:\, and get what I want to see.
However, when I run:
master..xp_cmdshell 'dir g:\
I get a message "The system cannot find the path specified."
I have the MSSQLSERVER account on this server setup to login as me.
Shouldn't this take care of any issues?
I would like to use a UNC share, but unfortunately I can't do that at
this time.
Much appreciate any ideas!
Sylvia
Try specifying the UNC path instead of a mapped drive:
EXEC master..xp_cmdshell 'DIR \\MyServer\ShareName'
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133830852.921653.231970@.g49g2000cwa.googlegr oups.com...
> Hello,
> I'm having problems accessing a mapped network drive. Basically, I
> need to be able to access the g:\ drive from SQL Server (i.e.
> master..xp_cmdshell 'dir g:\')
> I created a share on my server (\\MyServer\ShareName) and mapped the
> network drive G to this. So, from my machine I can do a "start, run",
> and type in g:\, and get what I want to see.
> However, when I run:
> master..xp_cmdshell 'dir g:\
> I get a message "The system cannot find the path specified."
> I have the MSSQLSERVER account on this server setup to login as me.
> Shouldn't this take care of any issues?
> I would like to use a UNC share, but unfortunately I can't do that at
> this time.
> Much appreciate any ideas!
> Sylvia
>
|||Thanks for the reply. Unfortunately using a UNC path is not an option
at this point, as I mentioned above (some hard-coded stuff already
written, can't change right now).
Any other options? Does a network share mapped to a drive just not
work?
Thanks,
Sylvia
|||> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
Sorry, I didn't understand that from your original post.
> Any other options? Does a network share mapped to a drive just not
> work?
A mapped drive can work but it is a kludge. One thing you might try is
establishing a persistent mapped drive. I expect you'll only need to do
this once, unless you change the SQL Server service account or unmap the
drive for that user.
EXEC master..xp_cmdshell 'NET USE L: \\MyServer\ShareName /PERSISTENT:YES'
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133845592.917117.66320@.g43g2000cwa.googlegro ups.com...
> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
> Any other options? Does a network share mapped to a drive just not
> work?
> Thanks,
> Sylvia
>
|||That did it! Thanks much - it's just the kludge I needed to get past
this.
accessing mapped drive via xp_cmdshell
Hello,
I'm having problems accessing a mapped network drive. Basically, I
need to be able to access the g:\ drive from SQL Server (i.e.
master..xp_cmdshell 'dir g:\')
I created a share on my server (\\MyServer\ShareName) and mapped the
network drive G to this. So, from my machine I can do a "start, run",
and type in g:\, and get what I want to see.
However, when I run:
master..xp_cmdshell 'dir g:\
I get a message "The system cannot find the path specified."
I have the MSSQLSERVER account on this server setup to login as me.
Shouldn't this take care of any issues?
I would like to use a UNC share, but unfortunately I can't do that at
this time.
Much appreciate any ideas!
SylviaTry specifying the UNC path instead of a mapped drive:
EXEC master..xp_cmdshell 'DIR \\MyServer\ShareName'
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133830852.921653.231970@.g49g2000cwa.googlegroups.com...
> Hello,
> I'm having problems accessing a mapped network drive. Basically, I
> need to be able to access the g:\ drive from SQL Server (i.e.
> master..xp_cmdshell 'dir g:')
> I created a share on my server (\\MyServer\ShareName) and mapped the
> network drive G to this. So, from my machine I can do a "start, run",
> and type in g:\, and get what I want to see.
> However, when I run:
> master..xp_cmdshell 'dir g:\
> I get a message "The system cannot find the path specified."
> I have the MSSQLSERVER account on this server setup to login as me.
> Shouldn't this take care of any issues?
> I would like to use a UNC share, but unfortunately I can't do that at
> this time.
> Much appreciate any ideas!
> Sylvia
>|||Thanks for the reply. Unfortunately using a UNC path is not an option
at this point, as I mentioned above (some hard-coded stuff already
written, can't change right now).
Any other options? Does a network share mapped to a drive just not
work?
Thanks,
Sylvia|||> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
Sorry, I didn't understand that from your original post.
> Any other options? Does a network share mapped to a drive just not
> work?
A mapped drive can work but it is a kludge. One thing you might try is
establishing a persistent mapped drive. I expect you'll only need to do
this once, unless you change the SQL Server service account or unmap the
drive for that user.
EXEC master..xp_cmdshell 'NET USE L: \\MyServer\ShareName /PERSISTENT:YES'
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133845592.917117.66320@.g43g2000cwa.googlegroups.com...
> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
> Any other options? Does a network share mapped to a drive just not
> work?
> Thanks,
> Sylvia
>|||That did it! Thanks much - it's just the kludge I needed to get past
this.
I'm having problems accessing a mapped network drive. Basically, I
need to be able to access the g:\ drive from SQL Server (i.e.
master..xp_cmdshell 'dir g:\')
I created a share on my server (\\MyServer\ShareName) and mapped the
network drive G to this. So, from my machine I can do a "start, run",
and type in g:\, and get what I want to see.
However, when I run:
master..xp_cmdshell 'dir g:\
I get a message "The system cannot find the path specified."
I have the MSSQLSERVER account on this server setup to login as me.
Shouldn't this take care of any issues?
I would like to use a UNC share, but unfortunately I can't do that at
this time.
Much appreciate any ideas!
SylviaTry specifying the UNC path instead of a mapped drive:
EXEC master..xp_cmdshell 'DIR \\MyServer\ShareName'
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133830852.921653.231970@.g49g2000cwa.googlegroups.com...
> Hello,
> I'm having problems accessing a mapped network drive. Basically, I
> need to be able to access the g:\ drive from SQL Server (i.e.
> master..xp_cmdshell 'dir g:')
> I created a share on my server (\\MyServer\ShareName) and mapped the
> network drive G to this. So, from my machine I can do a "start, run",
> and type in g:\, and get what I want to see.
> However, when I run:
> master..xp_cmdshell 'dir g:\
> I get a message "The system cannot find the path specified."
> I have the MSSQLSERVER account on this server setup to login as me.
> Shouldn't this take care of any issues?
> I would like to use a UNC share, but unfortunately I can't do that at
> this time.
> Much appreciate any ideas!
> Sylvia
>|||Thanks for the reply. Unfortunately using a UNC path is not an option
at this point, as I mentioned above (some hard-coded stuff already
written, can't change right now).
Any other options? Does a network share mapped to a drive just not
work?
Thanks,
Sylvia|||> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
Sorry, I didn't understand that from your original post.
> Any other options? Does a network share mapped to a drive just not
> work?
A mapped drive can work but it is a kludge. One thing you might try is
establishing a persistent mapped drive. I expect you'll only need to do
this once, unless you change the SQL Server service account or unmap the
drive for that user.
EXEC master..xp_cmdshell 'NET USE L: \\MyServer\ShareName /PERSISTENT:YES'
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133845592.917117.66320@.g43g2000cwa.googlegroups.com...
> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
> Any other options? Does a network share mapped to a drive just not
> work?
> Thanks,
> Sylvia
>|||That did it! Thanks much - it's just the kludge I needed to get past
this.
Subscribe to:
Posts (Atom)