First, if you are considering migrating a project to SQL Express, I highly recommend waiting just a bit longer for AutoCAD Plant 3D or AutoCAD P&ID 2012.
Pros for using SQL Server are primarily improved multi-user performance (and indeed avoiding a Multi-user limitation).
Con are increased admin requirements to deal with the SQL Server, such as backing up the database separately. Using the default database, you can simply copy the project files.
For Plant3D, it can run with a SQLite database, or SQL Server. There is a file in the Plant installation folder to convert the SQLite project to SQL if you want.