I got some confusions about the connection string for SQL express 2005. This is the default connect string for SQL express 2005 in DNN. I have done some testing about the connectionstring setting and got some question about the result. I wish somebody can help me to clearify my confusion.
<add
name="SiteSqlServer"
connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;"
providerName="System.Data.SqlClient" />
Please see the red words "User Instance = true".
I search the definition of User Instance in MSDN. I found this used for SQL express and AttachDBFilename only.
With "User Instance=True", the connectstring has AttachDBFilename will let SQL express create a new database instance to avoid interfering among each database process created by non-admin user. This look like said if I created two DNN installation in my server. It will create two SQLServer instance. The testing result seems this is right. I really found two SQL instance(two process) if I run two DNN installation. For the User Instance case, did I think right?
Then I do some more testing. I found if I don't create a database called DotNetNuke and set related setting in the SQL express. The DNN installation will fail. The message said can't login into database specify in connection string. But I don't know why I need to create the Database named DotNetNuke. Because I don't find any informaion about the database name in the connection string. I suppose I should find "Database=DotNetNuke" in the connection string, but I don't . Does anybody know why?
Then I try another connection string.
<add
name="SiteSqlServer"
connectionString="Data Source=.\SQLExpress;Integrated Security=True;AttachDBFilename=|DataDirectory|Database.mdf;"
providerName="System.Data.SqlClient" />
This is the same as the default one except I remove the "User Instance=True;". This time, I can install DNN even I don't have database DotNetNuke in the SQL express. So I got confuse about the result. Why? Why the DNN install will become successful this time? Actually I think two connection should both work, but it doesn't.
Can somebody help me to clearify my confuse?
One more question about the database, but doesn't related to DNN. How can I setting the database created by AttachDBFilename. I can't load the mdf in the Management studio express. Why I want to set the mdf file is because I found the transition log grow so quickly in my website but I can do nothing about it. I have a license of SQL Server 2005 developer version. Can I found some tools in it to reslove this issue. Of course management studio express is my first priority.
Thank you for spending so much time reading this question.