January 1, 2012 at 1:25 am
Hi
i want to create two servers and the information on these two servers is same.
and i want to send my query to each servers one by one,it means i want to spread my request on two servers,so my question is :
should i set a third computer that this computer spread these query?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
January 1, 2012 at 2:07 am
I didn’t get your question exactly. Are you planning to process a query in parallel on two servers? OR you want to setup some sort of load balancing? Please elaborate your business scenario.
January 1, 2012 at 5:32 am
there is a variant of SQL 2008 R2 called "parallel Data Warehouse", you can read a bit more about it here:
http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/pdw.aspx
and it uses Massively Parallel processing, which is in theory superior ot what you are orginially asking, the load balancing of, say, alternate SQL commands to two or more servers.
search for "Load Balancing SQL Server", adn you'll see some third party products, because the balancing effectively has to occur before it gets to SQL server....a web server for exampl,e could have some code built into it to balance teh pages, and there's several hardware pieces that do that as well.
the one way i'd donne that for web pages is when a user connects, a session variable is created for the user which will tell the app which server to get teh data from for as long as the user's session holds true;
the code simply round robins thru connections, soa new connection goes to DMZServer1.mydomain.com, and the next gets assigned DMZServer2.mydomain.com . , and so on.
so all the load balancing is done by the app, and not sql server.
you can make it a little smarter now adays with IP tracing for regions, if you had say east coast and wast cost servers, for example, but ours were all in house, sitting in teh same rackspace.
http://whatis.techtarget.com/definition/0,,sid9_gci214085,00.html
MPP (massively parallel processing)MPP (massively parallel processing) is the coordinated processing of a program by multiple processor s that work on different parts of the program, with each processor using its own operating system and memory . Typically, MPP processors communicate using some messaging interface. In some implementations, up to 200 or more processors can work on the same application. An "interconnect" arrangement of data paths allows messages to be sent between processors. Typically, the setup for MPP is more complicated, requiring thought about how to partition a common database among processors and how to assign work among the processors. An MPP system is also known as a "loosely coupled" or "shared nothing" system.
An MPP system is considered better than a symmetrically parallel system ( SMP ) for applications that allow a number of databases to be searched in parallel. These include decision support system and data warehouse applications.
Lowell
January 2, 2012 at 9:18 am
hi thank you so much for your paying attention.
i have 2 computers and i installed sql server on them.i have a same data in the two sqlservers.
i have a lot of transaction in my network,and i want to make a parallel processing in my servers,it means i want for example 3000 requests or query for a table,i want to divide these 3000 requests on the 2 servers and each of servers process 1500 of those request ,
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
January 2, 2012 at 9:28 am
hi .thank you .
no the data is not static
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
January 2, 2012 at 9:33 am
bijarcity (1/2/2012)
hi thank you so much for your paying attention.i have 2 computers and i installed sql server on them.i have a same data in the two sqlservers.
i have a lot of transaction in my network,and i want to make a parallel processing in my servers,it means i want for example 3000 requests or query for a table,i want to divide these 3000 requests on the 2 servers and each of servers process 1500 of those request ,
What you want to do somehow resembles what Oracle RAC does, two (or more) instances pointing to a single database - nothing similar on the SQL Server environment but you may want to take a look at "Federated Databases"
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 2, 2012 at 9:35 am
thank you.
it's a good idea that your application can connect to the servers by session .
i have a question !is your data static it means how you match your data in both servers?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
January 2, 2012 at 9:58 am
bijarcity (1/2/2012)
i have a question !is your data static it means how you match your data in both servers?
If the first part of the question question is: "What static data means?"... it means that the data do not change over time or it does in such a slow way that allows for a data sync process and can be considered "static" even when it is not.
If the second part of the question is: "How do you sync data in both servers?"... you do either replication or a full refresh, depending on data volatility.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 2, 2012 at 10:06 am
Do you have data mirroring? one sql server mirrors data on primary server.
January 2, 2012 at 10:14 am
you know i didn't exactly understand?
imagine you want to create a search engine like google.
we have a lot of queries and these queries can't execute in one servers ,so we can create many servers and divide our queries between our servers ? so my question is how we can do that ?suppose i donot know any thing about this!!
and what should i search ??
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
January 2, 2012 at 10:28 am
no i don't have data mirroring
you know i didn't exactly understand?
imagine you want to create a search engine like google.
we have a lot of queries and these queries can't execute in one servers ,so we can create many servers and divide our queries between our servers ? so my question is how we can do that ?suppose i donot know any thing about this!!
and what should i search ??
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
January 2, 2012 at 10:41 am
bijarcity (1/2/2012)
you know i didn't exactly understand?imagine you want to create a search engine like google.
we have a lot of queries and these queries can't execute in one servers ,so we can create many servers and divide our queries between our servers ? so my question is how we can do that ?suppose i donot know any thing about this!!
and what should i search ??
you are skipping over so many important issues and not answering all the questions we are putting to you, lets step back.
go back to the absolute basics.
you said you want to load balance, because you perceive that something is overloaded. why do you think load balancing is the solution, over, say, a faster gigabit caard between the application server and the sql servers?
I'm thinking you are already jumping to an exotic solution without even starting with the basics of tuning the queries and improving existing performance.
if you have not looked at an exection plan yet, i'm pretty sure you missed the point where 90% of the workload could be eased.
is it a web page or an application you use?
if this was a search engine, the data is static from the perspective of two or more sql servers retrieving the data.
true, some process will be adding new data to both servers at some point, but it's not the end users. right?
(if it is, i'll stop there...as mentioned before, updates would need to be handled by bigger better hardware, NOT a load balancing application)
Lowell
January 2, 2012 at 11:01 am
you said you can use a higher card between application and server ,but my answer is ,i can't do that because i want to choose a cheap way !
my data every time is going to be changed ,because i have application that every time fetch the websites in the internet and ranks them ,so my data is changed,and my search engine is accessible in my local network in our university .
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
January 2, 2012 at 12:46 pm
You can't scale SQL Server out like this easily. It just isn't done with a RDBMS. Google doesn't use an RDBMS, they use a different type of solution for storing data. They also don't necessarily need the consistency that SQL Server provides.
If there are 1000 servers at Google, and I connect to Server 1 and search "SQL Server", I might get different results than you get if you connect to Server 500 at the same time and run the same search. That's OK. As long as the results are good enough, that works. The results will be close, but they aren't necessarily the same.
If we both do the same at our bank, we can't have different results on our accounts if we have moved money. A higher degree of consistency is needed, and RDBMS systems provide this.
If you need the data to be consistent across all nodes, this is very hard to do. And it can't be done cheap with high volumes of data changes and queries. The data has to be updated somewhere, and then replicated out to other nodes. In SQL Server, replication (merge or peer to peer transactional) is probably the best way to do this, but it will take some coding, and there is overhead on your machines to transfer the data to other nodes and ensure there are no conflicts.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply