Solid State Disks

SSDs are in place for quite a while. Still they are struggling a bit in making their way into main stream. We, as human, prefer to believe in what other’s have to say, even if its nothing but blabbering. If you doubt it, go and ask your Admin if they know SSDs perform well then HDD? And if they know performance gains, ask why aren’t they fitting in SSDs in servers if they perform well, surely they might be having Cost over Benefits analysis !?

Well, to be honest, I too was one among those DBAs till the moment I got into a conversation and decided to find it out. Well, the current reason is problem called write amplification. SSDs are good for read based operations, but they fall back when data update comes into picture, not so badly, but they do. Another explanation is that current File systems are frankly written with HDDs in mind, they alleviate the mechanisms SSD uses as its shortcoming. e.g. in NTFS an update only needs to flip a bit, while its really not the case in SSDs.

Here’s the most promising paper on this topic which explains SSDs a lot better than I ever can, and much more than what I just scribed above.

Moving SQL Server to New Array

If you have to move to new array and yet retain the SQL Server as is, then its quite possible. All thats needed to be done is:
- set databases offline,
- copy data/log files to target location, and
- bring databases online from new location

Note that SQL 2000 had some constraints in this procedure if the database is a transaction replication publisher, but it works well 2005 onwards. Only slight alteration is as below:
- Take database offline: ALTER DATABASE <databaseName> SET OFFLINE;
- Copy the data/ log files over to the new location (don’t move right away, just in case you need to revert back to original files/ location)
- Reset file references: ALTER DATABASE <databaseName> MODIFY FILE (Name=N’logicalFileName’, FileName=N’location/filename’);
- Bring database online: ALTER DATABASE <databaseName> SET ONLINE;