You can get to the download and a bunch of information at http://www.microsoft.com/sql/editions/compact/default.mspx. Note that the Choosing Between SQL Server 2005 Compact Edition and SQL Server 2005 Express Edition whitepaper actually has some more content than the title would indicate, and does a pretty good job at putting the SQL Server 2005 Compact Edition in the context of all the other editions, not just Express Edition.
Personally, I’m thrilled at the possibilities that the Compact Edition brings by virtue of having a very small footprint, an in-process architecture and being ClickOnce-enabled. There are some changes in development habits that need to kick in along with the new possibilities (read up on stored procedure support and the rationale for in-process databases in the “choosing” whitepaper, for example).
It’s going to be very cool to see where developers will take this!