Friday, April 24, 2009

Planning SQL Upgrade

So you finally decided to upgrade to SQL Server 2008 but it's been awhile since you looked at options for your Raid configurations. Here's a summary of what your options are:

Raid 0 - data is spread in different disk (disk stripping) but lacks redundancy
PROS - performs well on read and write
CONS - Failure of disk means data is lost
Raid 1 - known as disk mirroring no disk stripping. Data is hosted on a single drive then copied to another drive for fault tolerance. Recommended for Transaction Logs.
PROS - Redundant so failure of a drive will not stop database access
CONS - Cost since you'll need to double the size of your disk.

Raid 5 - uses disk stripping with parity. Data is spread but also stores parity information so it can be used in case of disk failure. Best for read-based not many write operations. Also avoid for transaction log because it's primarily sequential write.
PROS - Fault Tolerance at a low price.
CONS - Write Performance. Additional time is required to compute parity info before it's written to disk.

Raid 10 - cross between Raid 0 and 1. Data is striped evenly accross all drives then mirrored.
PROS - Write performance is far superior than Raid 5 and read performance is similar to Raid 5. Offers Fault Tolerance.
CONS - Cost since you'll need twice the number of disk like Raid 1.

Monday, April 20, 2009

SQL Wildcards

We implemented Multicurrency module and part of this implementation is changing the naming convention of our customers in Great Plains. We want to easily identify what kind of customer are they. Currently, our customer id ends in 001 so we decided to change it to whatever the contract currency is. So let's say ABC has a EURO contract with us then their customer id will then be ABCEUR instead of ABC001.

Our database is shared to other systems like our BI team to create relational DBs. A question came to my lap on the easiest way to segregate the customers based on their contract currency. We don't want to give them the Currency ID field as it's not consistent with other system. So I provided them with this Select statement:

Select * from RM00101
where custnmbr like '%[EUR]'