.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Imran Ghani
Post New Web Links

SQL Server RAID 1 , 1+0 and TempDB

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
Hi, I am tuning a disk sub system for our OLTP datebase server these days. This is DAS disks. I now came into a question. The server has 8 148GB SAS drives. To have fault tolerance, we want to have RAID mirroring, so RAID 1 or 10. That leaves us half of the whole capacity, which is 4 * 148GB. As this box is only used for database, we want to have partitions for OS, data file, log file and temp db. Now I there is a dilemma: I know for best practise, temp db should on its own physical disk. If I set up four partition(logical drives), one for OS, one for data files, one for logs and one for temp db, then I can only have RAID 1 on each of the disk array. But the advantage of this is temp db is only its own disk. Or the second choice I can have is to partition the disks into three array (logical drives), namely OS, data files and logs. In this case I can have RAID 1+0 (striping and mirroring) on data file partition and RAID 1 on OS  and log. Could you give me some idea, which one is better? Any disscussions are welcome. Thank you very much.


View Complete Post


More Related Resource Links

SQL server storage RAID subsystem

  

I had posted some questions on here about a month ago regarding problems with a clients SQL server.  I have spent the last month doing alot of research on the subject and after isolating the mdf, ldf, and tempdb files the SQL server is still showing IO waits that indicate that the IO system is not capable of maintaining the performance standards and average read write times well above 2ms ... Right now the mdf is on a two disk raid one and the ldf, tempdb are on separate (i know this is not a stable long term configuration for stability)

Original Post

Top waits are ...

SLEEP_BPOOL_FLUSH
WRITELOG
PAGEIOLATCH_SH
PAGEIOLATCH_EX
BACKUPBUFFER
ASYNC_IO_COMPLETION
ASYNC_NETWORK_IO
BACKUPIO
PREEMPTIVE_OS_WAITFORSINGLEOBJECT
IO_COMPLETION

I'm thinking about getting a 12-24 disk SATA or SAS raid array and card, the company is only going to grow and the IO pressure is not going to decrease.  The server is a dell power-edge 710.  I'm very interested if anyone can point me in the right direction with this purchase.  I'm trying to keep it under 7,000 but I would be able to break up the SQL files onto separate raids ... I was thinking about

SQL Server 2008 TempDB overload, simple logging

  

Hello,

I have the following stored proc:

ALTER PROCEDURE [dbo].[spgetspreadstage2all]
	-- Add the parameters for the stored procedure here

	
	
AS
BEGIN
/****** Object: Table [dbo].[tblspgetspreadsectornew2]  Script Date: 08/20/2010 18:35:51 ******/
	IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[tblSpreadsStage2]') AND TYPE IN (N'U'))
	DROP TABLE [dbo].[tblSpreadsStage2]

	CREATE TABLE [dbo].[tblSpreadsStage2](
	ID int identity (1,1)not 

SQL Server 2008 R2 on Windows 2008R2 Ent - Drives+Raid Configuration + W2008 Disk Initialization

  

Hi, I just want to make sure that we setup this server the best way possible as we are moving from a previous box where

performance was affected mainly because of partitioning and SQL memory optimizations.

 

The Hardware:

Dell PowerEdge R710 

  • Intel® Xeon® X5660, 2.8Ghz, 12M Cache,Turbo, HT, 1333MHz
  • 64GB Memory (8x8GB), 1333MHz Dual Ranked RDIMMs
  • PERC H700 Controller
  • 8 x 300GB 10K RPM Serial-Attach SCSI 6Gbps 2.5in Hotplug Hard Drive
  • Virtual Volume 1: 2 Drives = Raid 1 - for the OS
  • Virtual Volume 2: 2 Drives = Raid 1 - for the ldf's
  • Virtual Volume 3: 4 Drives = Raid 10 - for the mdf's

Windows 2008R2 64bits is installed on Volume 1.

- I would like to know what suggestions you might have for the Initialization of the other 2 volumes (MBR or GPT).

- Any other settings like partition align = 64

- Memory Settings at the OS level.

- Memory Settings on SQL.

- Any other recommendations.


call JavaScript - jQuery code from ASP.NET Server-Side

  
jQuery got so close to me lately that I can see myself adding the scripts to my project almost unconsciously. The thing is, jQuery is very useful for me, in almost all situations and it has been a do or die enhancement for all my project since I first put my hands on it.

Of course while using it, you encounter few situations which need a bit of research to solve, mostly when you are trying to combine it with some other technologies like: UpdatePanels and ASP.NET Ajax. For instance there are many situations when I would like to run some jQuery magic based on some decision that I make on the server side.

Making Ajax Callbacks to the Server Using jQuery with ASP.NET

  
In the first part of this article series I introduced jQuery's functionality and how it provides a rich client side programming model. This time around I'll expand on these concepts and show you how you can use jQuery in combination with ASP.NET using it as an AJAX backend to retrieve data. I'll also discuss how you can create ASP.NET controls and otherwise interact with jQuery content from ASP.NET pages in WebForms.


jQuery is just a JavaScript library so it will work seamlessly with ASP.NET both from page code as well as through backend driven code using the Page.ClientScript object or ScriptManager. You can use jQuery on its own as a client side and Ajax library that communicates with ASP.NET or you can use jQuery in combination with ASP.NET AJAX. The two actually complement each other quite well as jQuery provides functionality that the ASP.NET AJAX library does not and vice versa. For the most part the interaction between the two libraries is trouble free except for a few very rare edge cases.

MS SQL Server: Disconnect Users From Database - Kill User Session

  
If you ever wanted to restore your database from a SQL backup file (.bak), but there are still users connected to your database, the backup operation will fail causing the error: Exclusive access could not be obtained because the database is in use.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

  
If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

call JavaScript - jQuery code from ASP.NET Server-Side

  
jQuery got so close to me lately that I can see myself adding the scripts to my project almost unconsciously. The thing is, jQuery is very useful for me, in almost all situations and it has been a do or die enhancement for all my project since I first put my hands on it.

Of course while using it, you encounter few situations which need a bit of research to solve, mostly when you are trying to combine it with some other technologies like: UpdatePanels and ASP.NET Ajax. For instance there are many situations when I would like to run some jQuery magic based on some decision that I make on the server side

Publishing SQL Server Database using Publishing Wizard : Tips & Tricks

  
We can use SQL Server Publishing Wizard to deploy our local Database to remote hosting server/production server.This feature is available in SQL Server 2008/VWD 2008.

Below is the complete step by step guide of this process.

CLR Integration With Microsoft SQL Server

  
Transact-SQL provides as much flexibility as possible but, because it is not a real programming language, it has some shortcomings about many issues (memory management, exception handling, debugging, file processing (or streaming), object-orientation (classes), or thread management). Transact-SQL also lacks features of normal libraries (arrays, collections, serialization, expressions, or string manipulations, etc).

IP Address Blocking or Restriction in SQL Server

  
"is it possible to block connections to SQL Server based on IP address". As far as now there is no official way in SQL Server to block the connections in SQL Server based on IP address. However this can be done from the OS end, we have the following three options available, refer HERE for more.


.Firewall
.IPSec
.RRAS IP Filter

Determine whether Forms Server is installed on a server

  
Check whether the Microsoft.Office.InfoPath.Server.dll and FormServer.aspx files are installed on the server.

Connestion string create any database server

  
This links important to how to create connection string to any data base sever.

SQL Server Database Transaction

  
A transaction is a group of SQL commands executed together as a single component of work to be accomplished. If all of these commands succeed, then a transaction is committed - the changes to the data are made final. If any of the commands within a transaction fail, then the entire transaction is cancelled, or in SQL Server terms, the transaction will be rolled back.

Transactions are one of the things that keep your data safe, but they're not necessarily easy to understand. Here's some help:

SQL Server Backup and Restore (video)

  
Restore your full backup, then run a few queries before you keep restoring transaction logs. Brent shows how to use the Standby option for restores.

In this five minute video, Brent demonstrates how to do it by creating a database, populating data, and then showing what disaster recovery is like by doing RESTORE WITH STANDBY.

SQL Server 2005 Try and Catch Exception Handling

  
I'm pretty excited to see that there is some real error handling for T-SQL code in SQL Server 2005. It's pretty painful to have your wonderfully architected .NET solution tainted by less-than-VBScript error handling for stored procedures in the database. The big difference being the addition of TRY..CATCH blocks. Let's take a look:

HttpContext.Items and Server.Transfer/Execute

  
The problem was basically that when a Server.Transfer call was applied the script container (and also various ClientScriptProxy script embedding routines) would potentially fail to load up the specified scripts.
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend