The Ji Village News

季庄新闻–Haidong Ji’s Blog

Here is some of my thoughts on migrating MySQL to Sql Server. It came out of an email discussion. I’d love to hear your thoughts on migrating to a different database platform, not just MySQL to Sql Server.

I actually thought about writing a white paper or even a course on migrating from MySQL to Sql Server, but never got the time to do it. Sometimes a project doing similar things can serve as a launchpad for this endeavour, but that never came along, at least not yet. I am very interested in database interoperability field though. I’ve done MySQL and Oracle admin in the past and have published some MySQL and Oracle stuff in blogs. I have much better technical skills on Sql Server than any other RDBMS platforms, primarily because I’ve worked on it longer.

Here are some of my thoughts. I think most of it applies equally on migration from Oracle, DB2, Sybase, Postgresql, etc., to Sql Server, or the other way around. It might be slightly easier to migrate from Sybase to Sql Server, considering their common root.

1. It is not easy to migrate existing app, unless the app is a simple one. Even for that, there are enough quirks that can throw people off and cause enough frustration to derail the whole projects. I’ve seen that happening twice, having engaged in moving 2 apps from Sql Server to Oracle;

2. Therefore, the best way to migrating to a new database RDBMS, in my opinion, is to start from a new initiative, probably not big initially. When you start things from a clean slate, you don’t have the historical garbage to worry about. Furthermore, you will give the team enough time to learn the new platform, and prepare the team for future migration, if you choose to do so;

3. Having open-minded team members is crucial to a migration project’s success. Too often people have emotional attachments to the platform they are familiar with, possibly out of job security concerns and lack of general curiosity toward new things.

I generally adopt a platform agnostic attitude, and don’t get religious and too carried away on the platform I work on. Having said that, I think these are points that marketing people can spin for persuasion purposes:

1. MySQL has too many storage platforms: MyISAM, InnoDB, MaxDb, and the newly introduced Maria. This can be viewed as a plus, as it provides choice. The downside of it is that it causes confusion for end users;

2. MySQL’s support for relational model is fairly recent. For example, for a long time, MySQL didn’t support Stored Procedures, Views, Triggers, Foreign Keys, etc. One could argue that MySQL is not mature in this area since it is new for them, but I think it is difficult to find evidence to substantiate that claim. Also, running the risk of offending some people, I think the importance of relational model got overblown a bit;

3. Sql Server offers the CLR integration. This can be a great selling point;

4. Sql Server offers tight integration with Visual Studio, Windows network, and all other things Microsoft. This is a huge advantage.

5. Too many people find *nix environment intimidating. Although MySQL works on Windows, but the perception in the marketplace is MySQL works better on *nix.

As far as migrating MySQL to Sql Server in a hosting web environment, my honest opinion is Sql Server will be fighting an uphill battle, because MySQL excels in this arena, especially for small and medium-sized, or departmental organizations, with the proliferation of such LAMP app like blogs, wikies, discussion boards, etc. I believe Microsoft’s weapon of choice in this arena should be SharePoint. Given Microsoft’s clout, it is certainly a battle worth fighting.

It is good to be at PASS Sql Server conference in Seattle. Most people here have this kindred Sql Server spirit. Since we all have this in common, it is easy to start a conversation with pretty much anybody. All you need to do is to smile, greet each other warmly, and share your own stories that a lot of other people can relate to. Most people are very nice, friendly, and helpful.

A couple of things to comment on:

1. Unlike last year’s conference in Dallas, the session rooms and labs are fairly close to each other. That makes it easier for people to find and go to the locations they want to;

2. PASS decided to stop printing PowerPoint slides. Instead, most slides can be downloaded before a session starts. I found this really helpful. I like to skim through files to determine if I am interested in a particular topic. The green backpack is a nice touch, since we are going “green” (paperless) this year;

3. Microsoft interactive lounge is very helpful, both to attendees and Microsoft, I hope. I talked to some senior testers from the full text search team, and got one of my issues resolved. You know, when you talk to an expert, you can get the gist of stuff without the noise in a short time. I will post some of the code snippets I got regarding full text search in a future post;

4. Steve Ballmer’s keynote is ok. He is a good presenter and very energetic. The Vista UI looks nice. At the end, the last question to him was if he would do a dance for us, like he did during a developer conference. He responded well, saying there is no music, and yelled “Go DBAs Baby” or something to that effect before leaving stage;

5. Steve Ballmer also commented on the alliance with Novell, and Microsoft’s legal trouble at the EU. I thought his responses were quite disingenuous. Regarding Novell and Linux, he indicated that Linux has infringed on Microsoft’s intellectual property and the agreement with Novell would cover SUSE Linux in case of lawsuit. The intellectual property infringement argument may or may not be true. But the table can be turned too. Can Microsoft guarantee that it does not infringe other’s, including Linux community’s, intellectual property? Initially I felt pretty positive about the alliance between Microsoft and Novell. After hearing Ballmer’s comments, I am not so sure now. It may not be worth worrying, after all. Just look at the thing Microsoft did with Sun. Now Java and Solaris are all open-source.

Regarding the EU legal issue, he responded that Microsoft is already pretty open. He went on to argue that since so many applications and device drivers are written for Windows platform, Microsoft can not be accused of not being open. That is quite disingenuous. I do agree that Microsoft has changed a lot and is definitely more open now, but Steve’s thinking is not in the right place. I also agree with Joel Spolsky and others. I think Microsoft would benefit without Steve as the CEO.

We went to Plano, Texas, a suburb of Dallas, for my son’s spring break. My brother’s family just moved there recently. With everybody being new to the area, I didn’t know which place to visit for some fun. Naturally, I started looking for Children’s museums.

Some web search revealed there is a place called Dallas Children’s Museum. It is in Valley View Center, a sizable mall in Dallas. So my son and I drove there for a visit.

Dallas is a big city, one would think a museum bearing its name should be interesting. However, it certainly cannot live up to that big name. The place is very small, occupying one or two rooms with old toys and equipments. If you are expecting exciting and interactive games and activities, you will be disappointed. If you happen to be there, it is all right for pre-schoolers to play a little bit and have some fun, but I would not just make a separate trip to it. Not worth it.

However, all was not lost. I saw computers there;)

Above is a shot of one of 2 computers that proudly sport Windows 3.1 operating system. Now see if you remember what happens if you do Ctrl-Alt-Del:

I couldn’t remember exactly when I first saw a computer, but it has to be after 1990, the year I went to Xiamen University. We had an introductory course to PC (IBM PC-XT). The lab was held at a computer center of the university. It seemed that 2 students had to share one PC during lab at the time. The PC is some kind of IBM PC XT based clone. The operating system was DOS, whose version I cannot recall now.

Those PCs were housed in the computer center, which was air-conditioned. Before we enter, we had to take off our shoes and put on quite, soft sandals, as if shoes would somehow disturb those state of the art, expensive machines. All PCs had a reset switch. We quickly learned its purpose and used it many times to get out of trouble. The teacher actually kept an eye on our reset-button usage to gauge our skill level.

Within one or two years, there seemed to be a lot more computers on campus. I started learning Windows 3.1. I can still remember the time when I took a mouse usage tutorial (single-click, double-click, moving around, resizing Windows) on Windows 3.1, and was very impressed and amused by the whole GUI thing.

This is a shot of the desktop, Windows 3.1:

Below is a shot of Microsoft Word on the museum’s computer. I forgot to check its version. If you ask me, from a usability point of view, I think today’s Word is actually worse than the version pictured here:

I actually don’t use Word anymore. With the exception of Outlook, I hardly ever use any of the rest of Office Suite applications. Even Outlook is kind of forced on me, because I really have no other choice. Now mind you, I still consider myself a power user of Word, Excel, Access, PowerPoint, and other Office Suite applications if situation calls for them. I know how they work, and I could whip out useful VBA macros without too much trouble.

Finally, you can play Wheels of Fortune on Windows 3.1:

PS. I love the Dallas area. The big draw is affordable housing. Plus, Plano seems to be very family-friendly with nice parks and lots of community activities.

SQL Server 2005 was released in November, 2005. I actually played with it much earlier than that. Overall, I am pretty happy with it. I think it is a great product, and will give Oracle’s namesake RDBMS a run for its money.

Previously, there were a lot of arguments against Sql Server for its perceived lack of features and sophistication. With this new release, you can almost see SQL Server 2005 is aimed squarely at those charges, and by extension, Oracle. Let’s just look at a few new or enhanced things:

1. Dynamic Management Views

Dynamic Management Views, or DMVs, is a new feature introduced to SQL Server 2005. You can use it to get server state information such as CPU, memory, IO, index usage, etc. These information will be helpful for troubleshooting, diagnosis, and performance tuning.

Oracle has had similar things for a while, except it is called Dynamic Performance Views. Those are usually views whose names start with v$, such as v$session, v$sysstat, etc.

Yes, v$ views are handy in Oracle and I expect DMVs in Sql Server to be equally useful.

2. The enhanced concept of schema

The concept of schema is not new to Sql Server. However, the implementation of it is quite different in SQL Server 2005. In SQL Server 2005, schema will be object owners, not users. That’s a departure from SQL Server 2000. However, schema in Sql Server and Oracle are still different. In my understanding, a schema in Oracle is like a database in Sql Server.

3. The introduction of synonym object

Oracle has had synonyms for a while. To SQL Server 2005, this is a new concept. The implementation is very similar to Oracle.

Conceptually, synonyms are similar to views, in the sense that they are all sitting on top of base objects. Synonyms provide alternative names for all SQL Server 2005 base objects, such as tables, views, UDFs, stored procedures, assemblies, etc. As such, if you reference them in your application, they can provide a layer of abstraction, in case the name or location of the base objects change.

4. Enhanced partitions implementation

Partition creation and management is greatly enhanced in SQL Server 2005. In Sql Server 2000, Microsoft had the concept of horizontal partition and vertical partition. The concept is somewhat awkward and so is its implementation.

With SQL Server 2005, partition management has been enhanced greatly. Not only can you do table partition, index partition is also possible. Plus, the way to implement them is also more consistent with industry standard. I think this eliminated one gap between Oracle and Sql Server.

5. sqlcmd

Microsoft also totally revamped the command line tool. I’ve talked quite a bit of sqlcmd in my blog. I think it is a powerful utility for a lot of automation tasks. To compete with SQLPlus, sqlcmd introduced quite a few enhancements. The ed command will invoke default editor in both sqlcmd and SQLPlus.

6. More advanced query tuning options

Microsoft also introduced more query tuning options in this release. For starters, there is the newly introduced plan guides. Plan guides influence optimization of queries by attaching query hints to them, without changing the query itself.

Oracle has often touted its many ways to tune queries, guide execution path, and adding hints. I don’t necessarily think that is something to be proud of. I think once again, Microsoft is trying to close the gap (whether perceived or real) here.

All the above mentioned things are great stuff. (I haven’t even talked about SSIS yet.) But Microsoft has to maintain a good balance and do not over do it. Simplicity and ease of management is the great strength of Sql Server. It will be a mistake if Sql Server try to match against Oracle feature by feature, and vice versa for Oracle. After all, who wants to manage and tweak hundreds of parameters in init.ora file in Oracle? As I said, Sql Server has the reputation of easy management and “working out of the box”, and it is something that should be treated as a competitive edge. With the somewhat bloated Sql Server Management Studio, Business Intelligence Development Studio, the misleading Visual Studio program group in your program files if you do a full install, and the slowness of those tools, I actually wonder if Microsoft over-engineered things and made them more complicated than they need to be.

As mentioned in the beginning of this article, overall, I like SQL Server 2005. Combined with Windows 2003 64-bit, SQL Server 2005 will take market share away from Oracle for both low end and high end database management systems. Oracle’s DBMS has been plagued with bugs, over-complication, and pricing issues. I don’t think Oracle’s outlook is very bright in the near future.

Besides, what’s up with Larry Ellison? It seems that his only obsession is to surpass Bill Gates as the richest man in the world. Bill Gates, warts and all, at least contributed billions of dollars to world health and charity. Yeah, I think PR is not a small factor in Bill’s charitable contributions, but you still have to give the man some credit. The most noise I heard from Larry was his procurement of a MiG fighter, his yacht, his Japanese style villa, and his many egomaniac antics. What’s up with that?

Don’t get me wrong, though. I am not a big fan of Windows monopoly. And I don’t like Microsoft’s predatory practice based on its dominate position on desktop, especially in the late 90’s. But it does seem that Microsoft has backed away from that in recent years.

Ken’s question made me think about Vista’s impact. Virtually all Microsoft’s products depend on the win32 API, so I wonder how much of change can Vista make? In other words, if Vista breaks win32 API calls, the cascading effects it has on other products like SQL Server, Exchange, BizTalk, Office, etc., would be tremendous. It may not come down to rewrite everything, but suffice it to say that the changes and additional development work and QA regression testing effort will be significant. So is it safe to say that Vista would be an evolutionary process of Windows, not a revoluntionary change that some people are led to believe? It will be interesting to see what unfolds, as far as Vista’s impact on server applications is concerned.

To extend the thinking a little further, once the foundation is set, the rest of stuff has to build on top of it and you are limited in ways to innovate and extend your stuff. Minor modification on the framework can be done but it is going to be costly. I guess that kind of explains why AJAX and Web 2.0 is all the rage recently. The idea is that you will do most of stuff through a web interface, no custom installation and setup is needed on your workstation. Google is a pioneer in this arena. The famous AJAX/Web 2.0 products are Gmail, Google Maps. Yahoo is not too far behind. Flickr and Yahoo Mail beta are good examples.

It will be interesting to see what comes out of the recent alliance between Sun and Google. If Google can deliver OpenOffice, a competitor of Microsoft Office Suite, through a web interface, much like how it does with GMail and Google Maps, that will give Microsoft Office Suite a run for its money.

A couple of years ago, Sun’s CEO Scott McNealy mentioned that “Network is the computer”; all we need is a dumb terminal to access it. I don’t necessarily think the terminal will be dumb, because now we do need it for local storage, text editing, some file processing, especially media files like music, image, and video. But hey, I cannot pretict the future. Maybe the dumb terminal 10 years from now is 10 times smarter than the ones we have today.