Change SQL Server Collation in a clustered environment

start /wait D:\Servers\setup.exe /qn VS=[VIRTUALSERVER] INSTANCENAME=[MSSQLSERVER] REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=[CURRENT USERPASS] SAPWD=[sapassword] SQLCOLLATION=[NewCollation] SQLACCOUNT=[domain acct] SQLPASSWORD=[domainpass] AGTACCOUNT=[domainacct] AGTPASSWORD=[domainpss]

Make sure that ADMINPASSWORD is the current user's password and this user should have admin rights on all involved nodes.

Disable the loopback check

This is used to fix a logon issue with NTLM and IIS, the error message:

Logon Failure:
Reason: An error occurred during logon
User Name: [User Name]
Domain: [Domain]
Logon Type: 3
Logon Process: Èù®
Authentication Package: NTLM
Workstation Name: [Workstation Name]
Status code: 0xC000006D
Substatus code: 0x0
Caller User Name: -
Caller Domain: -
Caller Logon ID: -
Caller Process ID: -
Transited Services: -
Source Network Address: 127.0.0.1
Source Port: 1298


For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

the following steps will fix the issue:

1. Click Start, click Run, type regedit, and then click OK.
2. In Registry Editor, locate and then click the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
3. Right-click Lsa, point to New, and then click DWORD Value.
4. Type DisableLoopbackCheck, and then press ENTER.
5. Right-click DisableLoopbackCheck, and then click Modify.
6. In the Value data box, type 1, and then click OK.
7. Quit Registry Editor, and then restart your computer.

sourced from http://support.microsoft.com/kb/896861

Foreign Key vs. Trigger Referential Integrity in SQL Server

I personally thought foreign key action is better than triggers, well the following article back up my idea:

http://www.mssqltips.com/tip.asp?tip=1508

Adding cluster disk to sql

went through the practice again and realized that I actually forgot something I did before :-)

Also one mistake is, the LUN was presented to me already partitioned and thus when I tried to do the SAN disk alignment it gave me error message which I happened to find one technet article taking about different issue with same symptom and led to a waste of time.(error msg: DiskPart was unable to create the specified partition)

Anway, I finally figured this out and delete the old partition and then things went smoothly.

Finally, the performance project is finally nearlly there, the newly purchased test server is put online and a significant performance increase was observed. (previoulsy revenue recognition process took 50+ minutes now only 1+ minutes!).

Web Server Farm

Sometimes different iis configuration inside the web server farm can cause serious issue.
the following command can be quiet helpful:
iiscnfg /copy /ts websrv08 /tu domain\george.wen /tp xxxx

Nginx with FastCgi

Haven't been playing with linux web server for a while, I tried Nginx and it seems to be impressively fast:

Red Hat / CentOS Install nginx PHP5 FastCGI Webserver

How do I install and configure a ngnix FastCGI php5 HTTP / web server under Red Hat / RHEL / Fedora / CentOS Linux?

Nginx (engine x) is an HTTP(S) server, reverse proxy and IMAP/POP3 proxy server written by Igor Sysoev. It is known for its high performance, stability, rich feature set, simple configuration, and low resource consumption.
Step # 1: Enable EPEL repo

ngnix is not included in the base system. Turn on EPEL repo to install nginx stable release:
# rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/$(uname -m)/epel-release-5-3.noarch.rpm
Step # 2: Install ngnix

Type the following command at a shell prompt:
# yum install nginx
Sample output:

Loaded plugins: downloadonly, fastestmirror, priorities, protectbase
Loading mirror speeds from cached hostfile
* epel: archive.linux.duke.edu
* base: ftp.linux.ncsu.edu
* updates: centos.mirror.nac.net
* addons: mirror.cs.vt.edu
* extras: centos.mirror.nac.net
0 packages excluded due to repository protections
Setting up Install Process
Parsing package install arguments
Resolving Dependencies
--> Running transaction check
---> Package nginx.x86_64 0:0.6.34-1.el5 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================
Installing:
nginx x86_64 0.6.34-1.el5 epel 319 k

Transaction Summary
==============================================================================================================================================================
Install 1 Package(s)
Update 0 Package(s)
Remove 0 Package(s)

Total size: 319 k
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : nginx [1/1]

Installed: nginx.x86_64 0:0.6.34-1.el5
Complete!

nginx configuration file

* Default config file: /etc/nginx/nginx.conf
* Default SSL config file: /etc/nginx/conf.d/ssl.conf
* Default virtual hosting config file: /etc/nginx/conf.d/virtual.conf
* Default documentroot: /usr/share/nginx/html

Configure PHP As FastCGI

Type the following to install php5 with other modules:
# yum install php-pear-Net-Socket php-pear php-common php-gd php-devel php php-mbstring php-pear-Mail php-cli php-imap php-snmp php-pdo php-xml php-pear-Auth-SASL php-ldap php-pear-Net-SMTP php-mysql
Install spawn-fcgi simple program for spawning FastCGI processes

Type the following command:
# yum install spawn-fcgi
Next, download spawn-fcgi init.d shell script:
# wget http://bash.cyberciti.biz/dl/419.sh.zip
# unzip 419.sh.zip
# mv 419.sh /etc/init.d/php_cgi
# chmod +x /etc/init.d/php_cgi
Start php app server, enter:
# /etc/init.d/php_cgi start
# netstat -tulpn | grep :9000
Sample output:

tcp 0 0 127.0.0.1:9000 0.0.0.0:* LISTEN 14294/php-cgi

By default php server listens on 127.0.0.1:9000 port. Finally, update /etc/nginx/nginx.conf as follows:
# vi /etc/nginx/nginx.conf
Modify / append as follows:


location ~ \.php$ {
root html;
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME /usr/share/nginx/html$fastcgi_script_name;
include fastcgi_params;
}


Save and close the file. Restart nginx:
# service nginx restart
Create /usr/share/nginx/html/test.php as follows:


phpinfo();
?>

Original Post at http://www.cyberciti.biz/faq/rhel-fedora-install-configure-nginx-php5/

Tip: Fighting OS-Level Fragmentation

SQL Server Magazine UPDATE TIP OF THE WEEK
This SQL Server tip is brought to you by SQL Server Magazine, the smart guide to building world-class applications! Click here for two free sample issues:

Tip: Fighting OS-Level Fragmentation
by Brian Moran

Q. I've read about how fragmentation at the SQL Server level can hurt my queries' performance. I understand how to look for fragmentation within my SQL Server tables by using the DBCC SHOWCONTIG command. However, should I also worry about fragmentation at the OS level?

A. Fragmentation exists at both the SQL Server level and the file level within the OS. It sounds like you already use DBCC SHOWCONTIG to combat SQL Server-level fragmentation (for a DBCC SHOWCONTIG primer, see SQL Server Books Online—BOL). So, let's look at how and when OS-level defragmentation can speed up your SQL Server.

Remember that SQL Server can report 0 percent fragmentation even when the on-disk files are horribly fragmented. SQL Server doesn't know or need to know how the OS physically lays out the bits on disk; it's the OS's job to manage physical bits on disk. However, because SQL Server doesn't know how the bits are laid out on disk, SQL Server has no direct way to report about file fragmentation. Imagine you're performing a table scan and SQL Server reports 0 percent fragmentation but the file that contains the table is scattered all over your disk. In this case, performing an OS-level defragmentation could help performance by making the files more contiguous on disk. However, defragmenting at the OS level doesn't always have the effect that you might expect. SQL Server pre-allocates space when you create a file. For example, if you create a 100MB file on a disk that's been defragmented recently, SQL Server creates the file in contiguous space. And SQL Server can read and write to that file forever, and the file won't fragment at an OS level (unless you stop SQL Server and perform a file-copy operation that puts the file into noncontiguous space). But if you create that 100MB file on a disk that's heavily fragmented, the file will also be fragmented.

What happens if SQL Server creates the 100MB file in contiguous space, but auto-grow operations increase the size of the file to 200MB? In this case, the new space added to the SQL Server file might fragment as the file grows. An OS-level defragmentation will improve performance if the files become fragmented and you're performing table-scan operations within SQL Server that look at ranges of data. So, I suggest scheduling a regular OS-level defragmentation. Given that file defragmentation is integrated in the operating system, defragmenting open files is perfectly safe. However, I/O activity generated must be considered if continuous high performance is mandatory. SQL Server slow time might not be a problem for small shops, but larger enterprise shops trying to maintain 4 or 5 nines (99.99% to 99.999%) of availability will be hard-pressed to find time for I/O intensive OS-level defragmentation. In such cases, many third-party defragmenters have advanced features to minimize or eliminate the I/O overhead of disk defragmentation. The best practice for highly available SQL Servers is to create your files on contiguous space that's already been defragmented, planning ahead of time for data growth and building that growth into the size of the initial files.

Send your technical questions to Brian Moran.


Additional Reference:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://technet.microsoft.com/en-us/library/cc966523.aspx