Tuesday, December 21, 2010

SQL Server - inventories

I've been thinking and thinking to dig out how sql server was built and how the storage engine works in sql server.
I almost became an instant fan of Paul S Randal by reading his blogs on the concepts.
So I started my own blog.

Here are some features in MSSQL on which I have little or no knowledge.

1) Status/Status2 columns in sysdatabases table in Master database, I checked with Paul on this and most of the statuses apart from the usual 1,2,4,8.... are undocumented and hence the results/descriptions vary after every release

2) Implementing Database in RAM, there is ofcourse Ramdisk which is available as free download where you can make some part of your RAM as a hard drive and put your user databases in that.
I am yet to try this



3) DB Cluster in the same instance - kind of wierd thought but y not??
Lets suppose we have two databases in the same instance, one acting as an active DB and the other as a passive DB,  Now I'll create a virtual DB to which users have access.
Users don't know that there are two physical databases.
So at any point of time only one Database is online and the other will be readonly/standby mode same as the secondary database in log shipping.
And the t-log backups should be applied continuously on the secondary.
so its like we have log shipping in place between two different databases with the same name on the same instance.
Now the next question is what happens in case of failover.
when Database A goes down, all the connections will be redirected to Database B with all the incomplete transactions starting from the beginning on B.
This seems a bit of work.

Also we can configure the failover like this:
Do the failover if any of the following conditions are true:
1) If Tlog is full and no further disk space to grow
2) If file group is full and the corresponding disk is full as well
3) DB going to Suspect mode

Is this ideally possible ?? I don't have an answer for this.

No comments:

Post a Comment