Visual Studio 2008 DB Pro GDR CTP and max model size
If you are using the Visual Studio 2008 DB Pro GDR CTP (that's a mouthful) you may run into a schema model size limitation. This will seemingly be resolved in the CTP 15 release expected toward the mid to end of July but until them if you happen to be using a database that generates a model greater than 257MB you're out of luck. The following error will come up: The database file is larger than then configured maximum database size. This setting takes effect on the first concurrent database connection only. [ Required Max Database Size (in MB; 0 if unknown) = 257 ]
When I first found this message staring at me on my screen I googled it and found the a few posts all referring to SQL Server CE. I knew that the DB Pro GDR was a significant architectural change from the prior final release of DB Pro and that the design DB had gone off into the sunset; yet, I never suspected SQL Server CE had replaced it. It did not give me a warm fuzzy when my primary concern had been around scalability in the schema. But I also know very little on SQL Server CE and told myself I need to not think of Compact Edition as less performant for this situation (I certainly plan to investigate CE much more closely). I poked around in the options for the database settings in Visual Studio but found nothing. I may have looked in the registry but it turns out that regardless I would not have found a fix. I posted out on the MSDN Forums and received the following back from Gert Draper , a Group Engineering Manager at Microsoft in the Team System world and from my experiences in the past an all around good guy.
Gert says:
We know what the scenario is, we create the model database with a max 4GB limit, secondary connections do not specify the max, there seems to be a behavioral change in SQL CE3.5 where it now uses the default size of 256MB when not specified in the secondary connection, instead of inheriting from the connection that created and opened the database to begin with.
More info on this can be found here:
http://blogs.msdn.com/sqlservercompact/archive/2007/06/13/the-story-of-max-database-size-connection-string-parameter.aspx
Right now I do not have a work around.
Gert went on to tell me later that this is expected to be resolved in the CTP 15 that should be available around mid to late July (2008). Gert and I had already shared this specific database schema in the past because under the current release version it simply will not load 75% of the time because of the current architecture of the DB Pro SKU. It just runs out of memory on a 3 GB machine and when it does load due to all the attempted real time background compile it becomes unusable when trying to edit the objects in the project.
I have been patiently but with great anticipation awaiting this GDR as my expectations were high that it would solve all my problems for me in terms of scaling to meet a very large database schema. The GDR is only a CTP and thus I will hold my determination for when it is released. I have thus far seen some improvements but I have lowered my personal expectations quite a bit from what they were and have become somewhat resigned that in managing this sort of database (having in excess of 13,000 stored procedures) in DB Pro will prove to just not be doable even on this coming release.
I hope, I hope I am wrong.