One thing I deal with all too frequently these days, is a rather unique and overly testy MSSQL db server. I have the distinct displeasure of being thrown to the lions as the resident pseudo-DBA, since there is a lack of a real DBA. Unfortunately, my years of MySQL experience didn't prepare me for the quirks of MSSQL, it may actually be putting me at a disadvantage because I get into situations expecting things to work and it seems that more often than not, things do not work as expected. Take replication for example, it takes me about 30 minutes, tops, to get MySQL replication going - I can pretty much say I've worked on this particular MSSQL instance for almost a year and it's still really not functioning as I would expect.
How Replication Should Work
Logic would dictate that once you are replicating a database, queries executed on the Master (MySQL terminology) get logged to the binary log, those transactions are transferred to the Slave and executed there. As such, all queries, including things like DROP TABLE and DROP DATABASE take effect on the SLAVE shortly after being executed on the MASTER. On the MySQL databases I admin, that is exactly what happens. Not so on MSSQL - in fact, the replication is forever crapping out for one reason or other. A recent attempt to update a particular application that included some SQL db restructuring failed with an oddball error that it could not execute a DROP TABLE because that table was being replicated. Well, duh... then drop the table already and lets move on.
I also find any minor glitch or hiccup puts MSSQL replication into a tizzy where out of spite it will choke and stop replicating. Apparently replication can also go stale... like a bun left on the counter. Where a MySQL replicated database can suffer through all sorts of interruptions between the MASTER and SLAVES, and just catch-up once the connection is restored - on numerous occasions I have had to redo MSSQL from scratch because it has broke.
What is Missing in MSSQL
Those who favour a GUI tend to poke fun at those who use the more archaic command line - but within seconds I can exec SHOW MASTER STATUS; and SHOW SLAVE STATUS; and instantly I can tell you if MySQL replication is not only working, but if the DBs are sync'd. MSSQL and it's Studio Management seems to have all sort of bells and whistles, wizards and monitors - but it's never very clear what state the replication is in. In fact, I have had incidences where the monitors have said replication is stopped when in fact it was still running. I think a nice Redmondian touch would be a traffic light for replication status - red, yellow, green, so at a glance you can see if more attention is required. If MSSQL has an equivalent to the MySQL "Log Position" records - that would also be handy to have.
In short, I think Microsoft can do better. I've read numerous blogs and articles and reviews telling me how wonderful MSSQL is and in how many ways it is superior to MySQL, and possibly in some areas it may be superior - but it is also sorely lacking in some basics it seems.
Oh, and if anyone is an MSSQL DBA and does work on the command line (isql?) - please give me a shout if you know of any useful commands for getting replication status!
Replicate Me!
One thing I deal with all too frequently these days, is a rather unique and overly testy MSSQL db server. I have the distinct displeasure of being thrown to the lions as the resident pseudo-DBA, since there is a lack of a real DBA. Unfortunately, my years of MySQL experience didn't prepare me for the quirks of MSSQL, it may actually be putting me at a disadvantage because I get into situations expecting things to work and it seems that more often than not, things do not work as expected. Take replication for example, it takes me about 30 minutes, tops, to get MySQL replication going - I can pretty much say I've worked on this particular MSSQL instance for almost a year and it's still really not functioning as I would expect.
How Replication Should Work
Logic would dictate that once you are replicating a database, queries executed on the Master (MySQL terminology) get logged to the binary log, those transactions are transferred to the Slave and executed there. As such, all queries, including things like DROP TABLE and DROP DATABASE take effect on the SLAVE shortly after being executed on the MASTER. On the MySQL databases I admin, that is exactly what happens. Not so on MSSQL - in fact, the replication is forever crapping out for one reason or other. A recent attempt to update a particular application that included some SQL db restructuring failed with an oddball error that it could not execute a DROP TABLE because that table was being replicated. Well, duh... then drop the table already and lets move on.
I also find any minor glitch or hiccup puts MSSQL replication into a tizzy where out of spite it will choke and stop replicating. Apparently replication can also go stale... like a bun left on the counter. Where a MySQL replicated database can suffer through all sorts of interruptions between the MASTER and SLAVES, and just catch-up once the connection is restored - on numerous occasions I have had to redo MSSQL from scratch because it has broke.
What is Missing in MSSQL
Those who favour a GUI tend to poke fun at those who use the more archaic command line - but within seconds I can exec SHOW MASTER STATUS; and SHOW SLAVE STATUS; and instantly I can tell you if MySQL replication is not only working, but if the DBs are sync'd. MSSQL and it's Studio Management seems to have all sort of bells and whistles, wizards and monitors - but it's never very clear what state the replication is in. In fact, I have had incidences where the monitors have said replication is stopped when in fact it was still running. I think a nice Redmondian touch would be a traffic light for replication status - red, yellow, green, so at a glance you can see if more attention is required. If MSSQL has an equivalent to the MySQL "Log Position" records - that would also be handy to have.
In short, I think Microsoft can do better. I've read numerous blogs and articles and reviews telling me how wonderful MSSQL is and in how many ways it is superior to MySQL, and possibly in some areas it may be superior - but it is also sorely lacking in some basics it seems.
Oh, and if anyone is an MSSQL DBA and does work on the command line (isql?) - please give me a shout if you know of any useful commands for getting replication status!