I need to load balance / mirror 3 things.
1. sql server. Can i do this using sql technology ?
i.e i get my app to connect to 99.99.99.99:1344 and the server connect me
to one but then mirrors data on next sql server too ?
2. IIS6. I can use windows load balancing for this but one IIS6 machine need
to be the front end i believe. Better to use hardware infront of server i
guess ?
3. specific traffic on single IP ... i guess i need hardware to do this.
Any help much appreicated.
ScottAnswers inline.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Scott" <s@.yahoo.co.uk> wrote in message
news:eLJdGQytHHA.3400@.TK2MSFTNGP03.phx.gbl...
>I need to load balance / mirror 3 things.
> 1. sql server. Can i do this using sql technology ?
> i.e i get my app to connect to 99.99.99.99:1344 and the server connect me
> to one but then mirrors data on next sql server too ?
>
Yes, with peer to peer replication or database mirroring DML occuring on one
node could be replicated/mirrored to all nodes in the replication/mirroring
topology. However with mirroring all nodes except the principal will be in a
state of recovering (in other words inaccessible), and with both replication
or mirroring, there is no client redirect. So if the app is connecting to
the SQL Server on 99.99.99.99:1344, you will have to do something
programmatically to have it connect to the other servers, and bring them
online (if you are using mirroring). So mirroring is not the best way to do
this and not the appropritate technology. Peer to peer replication can work,
but the latencies and the possibility or conflicts also do not make it an
appropriate technology.
>
> 2. IIS6. I can use windows load balancing for this but one IIS6 machine
> need to be the front end i believe. Better to use hardware infront of
> server i guess ?
With NLB one IIS server will redirect requests to other IIS servers which
form your NLB topology. Each of these IIS servers could be directed to a
different SQL Server, however this moves the problem back to how you are
going to consolidate DML to be consistent across all SQL Servers. If a
client connetct to IIS Server1, and does some work on SQL Server 1, what
happens when it reconnects to IIS 2? Is it ok that its data is not on SQL
Server 2 which hangs off IIS2? You need to work these issues out before
deploying such a topology. Of course all IIS Servers in your NLB topology
could connect to a single SQL Server which might lead to concurrency issues.
>
> 3. specific traffic on single IP ... i guess i need hardware to do this.
You need to look at hardware like Cisco's Local Director for things like
this.
>
> Any help much appreicated.
> Scott
>|||great help, thank you very much.
scott
Showing posts with label app. Show all posts
Showing posts with label app. Show all posts
Wednesday, March 28, 2012
load balancing question
I need to load balance / mirror 3 things.
1. sql server. Can i do this using sql technology ?
i.e i get my app to connect to 99.99.99.99:1344 and the server connect me
to one but then mirrors data on next sql server too ?
2. IIS6. I can use windows load balancing for this but one IIS6 machine need
to be the front end i believe. Better to use hardware infront of server i
guess ?
3. specific traffic on single IP ... i guess i need hardware to do this.
Any help much appreicated.
ScottAnswers inline.
--
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Scott" <s@.yahoo.co.uk> wrote in message
news:eLJdGQytHHA.3400@.TK2MSFTNGP03.phx.gbl...
>I need to load balance / mirror 3 things.
> 1. sql server. Can i do this using sql technology ?
> i.e i get my app to connect to 99.99.99.99:1344 and the server connect me
> to one but then mirrors data on next sql server too ?
>
Yes, with peer to peer replication or database mirroring DML occuring on one
node could be replicated/mirrored to all nodes in the replication/mirroring
topology. However with mirroring all nodes except the principal will be in a
state of recovering (in other words inaccessible), and with both replication
or mirroring, there is no client redirect. So if the app is connecting to
the SQL Server on 99.99.99.99:1344, you will have to do something
programmatically to have it connect to the other servers, and bring them
online (if you are using mirroring). So mirroring is not the best way to do
this and not the appropritate technology. Peer to peer replication can work,
but the latencies and the possibility or conflicts also do not make it an
appropriate technology.
>
> 2. IIS6. I can use windows load balancing for this but one IIS6 machine
> need to be the front end i believe. Better to use hardware infront of
> server i guess ?
With NLB one IIS server will redirect requests to other IIS servers which
form your NLB topology. Each of these IIS servers could be directed to a
different SQL Server, however this moves the problem back to how you are
going to consolidate DML to be consistent across all SQL Servers. If a
client connetct to IIS Server1, and does some work on SQL Server 1, what
happens when it reconnects to IIS 2? Is it ok that its data is not on SQL
Server 2 which hangs off IIS2? You need to work these issues out before
deploying such a topology. Of course all IIS Servers in your NLB topology
could connect to a single SQL Server which might lead to concurrency issues.
>
> 3. specific traffic on single IP ... i guess i need hardware to do this.
You need to look at hardware like Cisco's Local Director for things like
this.
>
> Any help much appreicated.
> Scott
>|||great help, thank you very much.
scottsql
1. sql server. Can i do this using sql technology ?
i.e i get my app to connect to 99.99.99.99:1344 and the server connect me
to one but then mirrors data on next sql server too ?
2. IIS6. I can use windows load balancing for this but one IIS6 machine need
to be the front end i believe. Better to use hardware infront of server i
guess ?
3. specific traffic on single IP ... i guess i need hardware to do this.
Any help much appreicated.
ScottAnswers inline.
--
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Scott" <s@.yahoo.co.uk> wrote in message
news:eLJdGQytHHA.3400@.TK2MSFTNGP03.phx.gbl...
>I need to load balance / mirror 3 things.
> 1. sql server. Can i do this using sql technology ?
> i.e i get my app to connect to 99.99.99.99:1344 and the server connect me
> to one but then mirrors data on next sql server too ?
>
Yes, with peer to peer replication or database mirroring DML occuring on one
node could be replicated/mirrored to all nodes in the replication/mirroring
topology. However with mirroring all nodes except the principal will be in a
state of recovering (in other words inaccessible), and with both replication
or mirroring, there is no client redirect. So if the app is connecting to
the SQL Server on 99.99.99.99:1344, you will have to do something
programmatically to have it connect to the other servers, and bring them
online (if you are using mirroring). So mirroring is not the best way to do
this and not the appropritate technology. Peer to peer replication can work,
but the latencies and the possibility or conflicts also do not make it an
appropriate technology.
>
> 2. IIS6. I can use windows load balancing for this but one IIS6 machine
> need to be the front end i believe. Better to use hardware infront of
> server i guess ?
With NLB one IIS server will redirect requests to other IIS servers which
form your NLB topology. Each of these IIS servers could be directed to a
different SQL Server, however this moves the problem back to how you are
going to consolidate DML to be consistent across all SQL Servers. If a
client connetct to IIS Server1, and does some work on SQL Server 1, what
happens when it reconnects to IIS 2? Is it ok that its data is not on SQL
Server 2 which hangs off IIS2? You need to work these issues out before
deploying such a topology. Of course all IIS Servers in your NLB topology
could connect to a single SQL Server which might lead to concurrency issues.
>
> 3. specific traffic on single IP ... i guess i need hardware to do this.
You need to look at hardware like Cisco's Local Director for things like
this.
>
> Any help much appreicated.
> Scott
>|||great help, thank you very much.
scottsql
Load balancing
I'm wondering if its reasonable to use clustering combined with table
partitiions to distribute a heavy load? (OLTP app)
My basic thought is that I want to distribute product data across many hard
drives (and I/O busses) such that the database server knows which drives
contain the data it needs (and therefore doesn't acces the drives it doesn't)
as well as using the cluster to distribute load to multiple servers.
I've seen a reference to a "federated" database, but am not sure what that
means.
absolutely. Clustering won't do much for you performance wise though.
I think you probably want to look at Distributed Partitioned Views for what
you want to do though. Clustering is used for high availability. IE Should
one node fail, your clients will be disconnected and when they reconnect
they will be accessing the same SQL Server but running on a different node.
With Distributed Partitioned Views their query will be directed at a view on
one server, but the data will be accessed on a different server.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:282512C2-0242-40A6-93BD-010E155398EA@.microsoft.com...
> I'm wondering if its reasonable to use clustering combined with table
> partitiions to distribute a heavy load? (OLTP app)
> My basic thought is that I want to distribute product data across many
> hard
> drives (and I/O busses) such that the database server knows which drives
> contain the data it needs (and therefore doesn't acces the drives it
> doesn't)
> as well as using the cluster to distribute load to multiple servers.
> I've seen a reference to a "federated" database, but am not sure what that
> means.
>
|||> With Distributed Partitioned Views their query will be directed at a view on
> one server, but the data will be accessed on a different server.
Thank you.
There is a rather criptic statement in the 2000 BOL that concerns me though.
"The most important goal is to minimize distributed processing in such a
system. You must be able to collocate related data on the same member server,
and then route each SQL statement to a member server that contains most, if
not all, of the data required to process the statement. "
Does this mean that I should put all (or as much) of the total data to be
retervied on one server?
For example, in a title/item lookup all item records related to a title
should be on the same server/partition? All other things being equal (which
they are probably not), I would prefer to put them on seperate servers and
have seperate NIC's for the DB's to talk to each other. Still, either would
work.
|||I believe it means that if your distributed partition view has three member
servers and you issue a query on servera which has a where clause pointing
to the data stored on serverb, and a join on another table, that second
table should be on serverb as well.
So if you partitioned on date. Servera might contain last years data,
serverb, the prior year, and serverc this years data. A search on this years
data might originate on servera, and will be directed to serverc by the
distributed partition view.
In your case perhaps titles beginning with a-h might be on servera, i-q
might be on serverb, r to z on serverc. Items related to titles in servera
should be on servera, and so on for serverb and c. There could be some
duplication of item data across all three servers.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:E798A0E7-6392-4DD2-9D53-18C43E4C1DE0@.microsoft.com...
> Thank you.
> There is a rather criptic statement in the 2000 BOL that concerns me
> though.
> "The most important goal is to minimize distributed processing in such a
> system. You must be able to collocate related data on the same member
> server,
> and then route each SQL statement to a member server that contains most,
> if
> not all, of the data required to process the statement. "
> Does this mean that I should put all (or as much) of the total data to be
> retervied on one server?
> For example, in a title/item lookup all item records related to a title
> should be on the same server/partition? All other things being equal
> (which
> they are probably not), I would prefer to put them on seperate servers and
> have seperate NIC's for the DB's to talk to each other. Still, either
> would
> work.
|||My understanding is that this is related to the selectivity of your partition
function. In other words, if your queries always have a where clause that
states "WHERE [YEAR] = 2007" and each partition has data for a separate year,
then things will go well. However if peaple can do "WHERE [YEAR] IN (1999,
2000, 2007) and Forename = 'Harry'" then there are problems. The data has to
be unioned from the 3 partitions and the processing won't be on a separate
server. Performance in this type of scenario can become worse than not having
the partition at all...
HTH,
Paul Ibison
|||No, my understanding is the this query will be sent to all partitions and
then unioned. This will be efficient.
However a query on a DPV partitioned on data which is not aligned with any
of these partitions won't be as efficient. For example this query
select * from MyDPV where forname='Harry'
will result in index scans of all partitions and then bookmark lookups to
return all the matching rows. It is important to note that processing is
distributed amoung the member servers. If the DPV was all on one server (ie
each partition in a different table or database) all the processing will be
on the same server, and the cpu hit of this server doing that bookmark
lookup will be intense.
If it was distributed over different servers each server will crunch their
part of the query and the cpu hit will be there. The cpu hit on the server
where you issue the DPV query will be minimal - its only aggregating the
results.
DPV are great if the majority of the queries align themselves with your
partitions. If they don't they aren't so great as it involves table/index
scans.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:EA4B50B2-A9C4-45EE-AE79-9B76088027A2@.microsoft.com...
> My understanding is that this is related to the selectivity of your
> partition
> function. In other words, if your queries always have a where clause that
> states "WHERE [YEAR] = 2007" and each partition has data for a separate
> year,
> then things will go well. However if peaple can do "WHERE [YEAR] IN (1999,
> 2000, 2007) and Forename = 'Harry'" then there are problems. The data has
> to
> be unioned from the 3 partitions and the processing won't be on a separate
> server. Performance in this type of scenario can become worse than not
> having
> the partition at all...
> HTH,
> Paul Ibison
|||What my example was trying to (incorrectly!) show was that if the query
doesn't relate to the partition function (startup filter), the query will get
sent to each server and processed there, then the results unioned. There will
be cases where this is worse than processing on one box, which avoids the
network round trips and the final unioning of datasets.
Cheers,
Paul Ibison
sql
partitiions to distribute a heavy load? (OLTP app)
My basic thought is that I want to distribute product data across many hard
drives (and I/O busses) such that the database server knows which drives
contain the data it needs (and therefore doesn't acces the drives it doesn't)
as well as using the cluster to distribute load to multiple servers.
I've seen a reference to a "federated" database, but am not sure what that
means.
absolutely. Clustering won't do much for you performance wise though.
I think you probably want to look at Distributed Partitioned Views for what
you want to do though. Clustering is used for high availability. IE Should
one node fail, your clients will be disconnected and when they reconnect
they will be accessing the same SQL Server but running on a different node.
With Distributed Partitioned Views their query will be directed at a view on
one server, but the data will be accessed on a different server.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:282512C2-0242-40A6-93BD-010E155398EA@.microsoft.com...
> I'm wondering if its reasonable to use clustering combined with table
> partitiions to distribute a heavy load? (OLTP app)
> My basic thought is that I want to distribute product data across many
> hard
> drives (and I/O busses) such that the database server knows which drives
> contain the data it needs (and therefore doesn't acces the drives it
> doesn't)
> as well as using the cluster to distribute load to multiple servers.
> I've seen a reference to a "federated" database, but am not sure what that
> means.
>
|||> With Distributed Partitioned Views their query will be directed at a view on
> one server, but the data will be accessed on a different server.
Thank you.
There is a rather criptic statement in the 2000 BOL that concerns me though.
"The most important goal is to minimize distributed processing in such a
system. You must be able to collocate related data on the same member server,
and then route each SQL statement to a member server that contains most, if
not all, of the data required to process the statement. "
Does this mean that I should put all (or as much) of the total data to be
retervied on one server?
For example, in a title/item lookup all item records related to a title
should be on the same server/partition? All other things being equal (which
they are probably not), I would prefer to put them on seperate servers and
have seperate NIC's for the DB's to talk to each other. Still, either would
work.
|||I believe it means that if your distributed partition view has three member
servers and you issue a query on servera which has a where clause pointing
to the data stored on serverb, and a join on another table, that second
table should be on serverb as well.
So if you partitioned on date. Servera might contain last years data,
serverb, the prior year, and serverc this years data. A search on this years
data might originate on servera, and will be directed to serverc by the
distributed partition view.
In your case perhaps titles beginning with a-h might be on servera, i-q
might be on serverb, r to z on serverc. Items related to titles in servera
should be on servera, and so on for serverb and c. There could be some
duplication of item data across all three servers.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:E798A0E7-6392-4DD2-9D53-18C43E4C1DE0@.microsoft.com...
> Thank you.
> There is a rather criptic statement in the 2000 BOL that concerns me
> though.
> "The most important goal is to minimize distributed processing in such a
> system. You must be able to collocate related data on the same member
> server,
> and then route each SQL statement to a member server that contains most,
> if
> not all, of the data required to process the statement. "
> Does this mean that I should put all (or as much) of the total data to be
> retervied on one server?
> For example, in a title/item lookup all item records related to a title
> should be on the same server/partition? All other things being equal
> (which
> they are probably not), I would prefer to put them on seperate servers and
> have seperate NIC's for the DB's to talk to each other. Still, either
> would
> work.
|||My understanding is that this is related to the selectivity of your partition
function. In other words, if your queries always have a where clause that
states "WHERE [YEAR] = 2007" and each partition has data for a separate year,
then things will go well. However if peaple can do "WHERE [YEAR] IN (1999,
2000, 2007) and Forename = 'Harry'" then there are problems. The data has to
be unioned from the 3 partitions and the processing won't be on a separate
server. Performance in this type of scenario can become worse than not having
the partition at all...
HTH,
Paul Ibison
|||No, my understanding is the this query will be sent to all partitions and
then unioned. This will be efficient.
However a query on a DPV partitioned on data which is not aligned with any
of these partitions won't be as efficient. For example this query
select * from MyDPV where forname='Harry'
will result in index scans of all partitions and then bookmark lookups to
return all the matching rows. It is important to note that processing is
distributed amoung the member servers. If the DPV was all on one server (ie
each partition in a different table or database) all the processing will be
on the same server, and the cpu hit of this server doing that bookmark
lookup will be intense.
If it was distributed over different servers each server will crunch their
part of the query and the cpu hit will be there. The cpu hit on the server
where you issue the DPV query will be minimal - its only aggregating the
results.
DPV are great if the majority of the queries align themselves with your
partitions. If they don't they aren't so great as it involves table/index
scans.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:EA4B50B2-A9C4-45EE-AE79-9B76088027A2@.microsoft.com...
> My understanding is that this is related to the selectivity of your
> partition
> function. In other words, if your queries always have a where clause that
> states "WHERE [YEAR] = 2007" and each partition has data for a separate
> year,
> then things will go well. However if peaple can do "WHERE [YEAR] IN (1999,
> 2000, 2007) and Forename = 'Harry'" then there are problems. The data has
> to
> be unioned from the 3 partitions and the processing won't be on a separate
> server. Performance in this type of scenario can become worse than not
> having
> the partition at all...
> HTH,
> Paul Ibison
|||What my example was trying to (incorrectly!) show was that if the query
doesn't relate to the partition function (startup filter), the query will get
sent to each server and processed there, then the results unioned. There will
be cases where this is worse than processing on one box, which avoids the
network round trips and the final unioning of datasets.
Cheers,
Paul Ibison
sql
Monday, March 19, 2012
ListAvailableSQLServers
I have a VB6 app that uses ListAvailableSQLServers to collect the server names on a computer. On Windows XP, however, the default instance shows up as (LOCAL) while @.@.servername is the computername. When my app tries to select from (LOCAL).db.dbo.table, it gets "SQL Server does not exist or access is denied." If it selects from "computer".db.dbo.table all is well.
Any thoughts how to get around this? Aliasing does not work...
Thanks in advance.Plz first make sure the SQL service is running by checking it under the Services group in the Management console and what startup service account it's using; can u connect to that instance thru Query Analyzer? If yes then type in
select serverproperty('servername').
Check the mode of Authentication u r tryin to connect to SQL thru. Post some details about the startup-service account etc.
Howdy.|||Since the (local) server is nearly "hardwired" into the list, there isn't a good way to avoid it. The machine name should still appear in the list anyway. I usually just put in code to ignore both the IP address and the constant (local) as I iterate through the list of servers.
-PatP|||Sorry, SPE (Stupid Programmer Error); one of my ADO connection strings had the data source hardcoded (leftover from testing). Thanks both...|||Hmmm... We know those as ID10T errors. Same difference!
Glad you are working now!
-PatP
Any thoughts how to get around this? Aliasing does not work...
Thanks in advance.Plz first make sure the SQL service is running by checking it under the Services group in the Management console and what startup service account it's using; can u connect to that instance thru Query Analyzer? If yes then type in
select serverproperty('servername').
Check the mode of Authentication u r tryin to connect to SQL thru. Post some details about the startup-service account etc.
Howdy.|||Since the (local) server is nearly "hardwired" into the list, there isn't a good way to avoid it. The machine name should still appear in the list anyway. I usually just put in code to ignore both the IP address and the constant (local) as I iterate through the list of servers.
-PatP|||Sorry, SPE (Stupid Programmer Error); one of my ADO connection strings had the data source hardcoded (leftover from testing). Thanks both...|||Hmmm... We know those as ID10T errors. Same difference!
Glad you are working now!
-PatP
Friday, February 24, 2012
List of accessible databases on a server
Hi All
I once saw this app that allowed you to pick a SQL server and provided your
signon details; it then displayeda list of DBs on that server which you had
access to with the provided signon.
Does anybody know how they did this? How they got the list of databases off
the server?
ThanksHi
EXEC sp_MSForEachDB "use ? select '?' as dbname, name as username from
sysusers where islogin = 1 and hasdbaccess = 1"
"Chan" <Chan@.discussions.microsoft.com> wrote in message
news:B87B3DDC-4F51-4810-959A-96CFB27E93BD@.microsoft.com...
> Hi All
> I once saw this app that allowed you to pick a SQL server and provided
> your
> signon details; it then displayeda list of DBs on that server which you
> had
> access to with the provided signon.
> Does anybody know how they did this? How they got the list of databases
> off
> the server?
> Thanks|||You could start with:
SELECT name FROM master..sysdatabases
"Chan" <Chan@.discussions.microsoft.com> wrote in message
news:B87B3DDC-4F51-4810-959A-96CFB27E93BD@.microsoft.com...
> Hi All
> I once saw this app that allowed you to pick a SQL server and provided
> your
> signon details; it then displayeda list of DBs on that server which you
> had
> access to with the provided signon.
> Does anybody know how they did this? How they got the list of databases
> off
> the server?
> Thanks
I once saw this app that allowed you to pick a SQL server and provided your
signon details; it then displayeda list of DBs on that server which you had
access to with the provided signon.
Does anybody know how they did this? How they got the list of databases off
the server?
ThanksHi
EXEC sp_MSForEachDB "use ? select '?' as dbname, name as username from
sysusers where islogin = 1 and hasdbaccess = 1"
"Chan" <Chan@.discussions.microsoft.com> wrote in message
news:B87B3DDC-4F51-4810-959A-96CFB27E93BD@.microsoft.com...
> Hi All
> I once saw this app that allowed you to pick a SQL server and provided
> your
> signon details; it then displayeda list of DBs on that server which you
> had
> access to with the provided signon.
> Does anybody know how they did this? How they got the list of databases
> off
> the server?
> Thanks|||You could start with:
SELECT name FROM master..sysdatabases
"Chan" <Chan@.discussions.microsoft.com> wrote in message
news:B87B3DDC-4F51-4810-959A-96CFB27E93BD@.microsoft.com...
> Hi All
> I once saw this app that allowed you to pick a SQL server and provided
> your
> signon details; it then displayeda list of DBs on that server which you
> had
> access to with the provided signon.
> Does anybody know how they did this? How they got the list of databases
> off
> the server?
> Thanks
List both SQL2000&2005 instance using SQLDMO
I have C++ application which can list all registered SQL Server in your local machine. The problem is this app can't list SQL server 2005 instance.
How can I list both SQL2000 and 2005 server instance using SQLDMO?
Thanks
Have you installed the SQL 2005 workstation components? There is an updated version of SQLDMO which supports SQL Server 2005. The DMO dll's that came with SQL Server 2000 won't be able to read SQL 2005 server information, but the 2005 DMO dll's should solve your problem.
Subscribe to:
Posts (Atom)