Windows Installer - SQL Server Installation Issues and a Solution.
Posted on October 25, 2007
Filed Under .Net Code, VB.Net, Windows Deployment, Windows Development
Here is the problem in a nutshell: You have a custom client application that uses SQL Server 2005 Express. The server installation is a little bit to be desired. First, you have to install SQL Server Express, then you have to configure the options (unless you did command line options), then you have to execute the scripts to create your database, tables, and possibly populate it with some data. Or conversely, execute scripts or install and use the management console to attach a database. Messy, at best.
So what if you want to have a single installation program that will install all the prerequisites (.Net), then install SQL Server 2005 with your custom options (instance name for example), and then have it execute scripts and fill it with data? After working at it for hours, I came to the conclusion that you could be out of luck. Until I did some reconfiguring.
First things first, you need to get SQL Server Express to install properly. I struggled with this for a while, until I had the bright idea of using a bootstrapper… But can’t we use the one that comes with the Visual Studio Package and Deployment Wizard? Yes you can!
Go into your Visual Studio 8 folder and find the SDK/v2.0/Bootstrapper/Packages folder. Make a copy of the SqlExpress folder and name it something like, MyAppSqlExpress. Inside that folder, there is a product XML file. Edit that product.xml file and change the product code. Mine was Microsoft.Sql.Server.Express.1.0 and I changed it to MyAppName.Microsoft.Sql.Server.Express.1.0 … Just change the MyAppName to the app name of the product it goes with. You will also see a En folder for the English installation. The package.xml file is the one you want to edit in that folder. The first item to edit, is the arguments line. This passes arguments to the SqlExpr32.exe file (the setup for Sql Express). The arguments are the same as any other SQL Express install… So change it to be something like this:
Arguments='-q /norebootchk /qb reboot=ReallySuppress addlocal=all INSTANCENAME=MyApp SECURITYMODE=SQL SAPWD=MyStrongPassword DISABLENETWORKPROTOCOLS=0 SQLAUTOSTART=1'
This will do a silent installation of SQL Server Express using all protocols, a custom instance name (MyApp), turn on the SQL security mode and specify a strong SA password.
Next, there is the strings block at the bottom. Edit the StringName “DisplayName” to look like this:
MyApp SQL Server 2005 Express Edition
Save it, and go into your setup and deployment project you created, go to the project properties, then to the prerequisites, and in the list you should now see your custom install of SQL Server Express!
This does work, I just tested it out today.
So that solves problem #1. What about the problem of executing scripts? That seems to be the easy part… From my earlier post you can incorporate that code into a custom action.
Create a new project, and select Installer Class from the templates. This creates a new installer class for you to augment.
Private Sub ExecuteSQL(ByVal SQL As String, _
ByVal ConnectionString As String)
Dim srv As New Server(New ServerConnection( _
New SqlClient.SqlConnection(ConnectionString)))
srv.ConnectionContext.ExecuteNonQuery(SQL)
srv.ConnectionContext.Disconnect()
End Sub
Private Sub BulkLoad(ByVal ConnectionString As String, _
ByVal TableName As String)
Dim bulk As New SqlClient.SqlBulkCopy(ConnectionString, _
SqlClient.SqlBulkCopyOptions.KeepIdentity)
Dim ds As New DataSet
Dim path As String = My.Application.Info.DirectoryPath
ds.ReadXmlSchema(path & "\" & TableName & ".xsd")
ds.ReadXml(path & "\" & TableName & ".xml")
bulk.BulkCopyTimeout = 0
bulk.DestinationTableName = TableName
bulk.WriteToServer(ds.Tables(TableName))
bulk.Close()
End Sub
Public Overrides Sub Install(ByVal stateSaver As _
System.Collections.IDictionary)
MyBase.Install(stateSaver)
Dim ConnBuilder As New SqlClient.SqlConnectionStringBuilder()
ConnBuilder.UserID = "sa"
ConnBuilder.Password = "MyStrongPassword"
ConnBuilder.DataSource = "localhost\MyApp"
ConnBuilder.InitialCatalog = "master"
ExecuteSQL(My.Resources.CreateDatabaseSQL, _
ConnBuilder.ConnectionString())
ConnBuilder.InitialCatalog = "MyDatabase"
BulkLoad(ConnBuilder.ConnectionString(), "MyTable")
End Sub
In this example, I just stored the data files (an XML data file and an XSD schema file) in the setup project, which gets installed along with the custom installer DLL. The create database SQL stuff was all saved as a store resource in the installer class project. I did this so that some of the structure was hidden, which may or may not be an issue for others.
Now, add the custom action to a setup project and you are finished!
Comments
19 Responses to “Windows Installer - SQL Server Installation Issues and a Solution.”
Leave a Reply
Hi,
Strange - I did just what you describe in step 1, and the new option does not show up in the list of Prerequisites. Why would that be?
It’s my first time dealing with incorporating SQL Express in the setup. Also, in my case I don’t want to make it a prerequisite for the client application. Rather, I want to install it just once on the “server”. So, I created an empty setup project with SQL Express is a prerequisite. Hope this works - not tested yet.
Thanks in advance if you have any suggestions,
Anatoly Molotkov, MCSD, MCPD
You know, I may have left out a step. Inside the folder for SqlExpress, there is a product XML file. In the NEW folder you copied this one from, change that product.XML and change the product code. Mine was Microsoft.Sql.Server.Express.1.0 and I changed it to MyAppName.Microsoft.Sql.Server.Express.1.0 … Just change the MyAppName to the app name of the product it goes with. I think the reason it’s not showing up for you is because that name was never changed.
That worked - thanks so much!
This is great, the only problem i have is that if i install my application on a machine that already has SQLEXPress installed it does not install my new instance. any ideas?
Benjamin,
I remember trying this on a machine that had a default instance of SQL Express installed, and I remember it working (because I was testing it on my dev machine which already had SQL Express from Visual Studio 2005). I went to try it again today, but the install will not work because my dev machine is now Vista x64 and I only ship out the 32-bit version of SQL Express at the moment. If I get a chance I will try to install it into a XP Pro virtual machine to test it out.
Aaron
I’ve follwed every step of this, altering the XML files to the right values, but my custom prerequisite is still not showing in the list in Visual Studio.
I’ve tried this 5 times now, and still can’t get the prerequisite to show up. Any ideas?
Andy
Hi again. You know, it’s strange, but I could never get this bootstrapper approach to work on my end. I am trying a silent installation, and it keeps showing all the SQL Server Express setup dialogs. Were you actually able to build a setup and confirm that a silent install works? I have a MS support case open, they seem to think I need to run SQL Server setup from command line to get a silent install to happen. Thanks!
Andy - I’m not really sure why yours isn’t showing, but make sure you changed the product name in the product.xml file.
Anatoly - I don’t do a completely silent install. My users don’t have to click anything, but the setup windows show them what is going on. If you want it to be completely silent, you will have to explore the options in the sqlexpr.exe file to get that to happen and then use those options in the package.xml file. See this site: http://msdn2.microsoft.com/en-us/library/ms144259.aspx
I think it’s the /qn switch. I use the /qb switch which displays the basic dialogs so they know what’s happening. I think with the /qn switch, error messages are suppressed to, which could be a bad thing… But I’m not 100% sure of that.
Very Cool, just a quick question before I try it out:
Will my instance of sql server express be removed silently if the user chooses to remove my program with the add/remove programs option in the control panel?
Shagohod,
No, it will not uninstall it if you remove your program. Like all prerequisites that are installed by Windows Installer, those components are left there.
Havin a bit of trouble with it, I’ve changed the product code in my product.xml but it detects my other instance of SQLEXPRESS and does not install my custom instance. I see both instances available for prereq’s and I only have my custom instance selected…….
Shagohod, I had to redo this for Visual Studio 2008 since the bootstrapper folder is in a different spot now and I discovered there is a tag in the package.xml file you need to remove to stop this from happening.
Find all occurances of this:
and remove it.
Hope this helps (albeit a month later!).
[…] solution presented here works exceedingly well. May 22, 2008 | Filed Under […]
Hello.
Does anyone know whether I can enable the CLR during silent installation? Is there an argument for this operation?
Thanks in advance.
This is the Microsoft documentation on command line installation:
http://technet.microsoft.com/en-us/library/ms144259.aspx
I have putted The Solution Here
Plz , Visit It
http://hamidseta.blogspot.com/2008/05/install-sqlexpr32exe-silently.html
Hope So This Will U
Regards
Seta Hamid S
[…] solely SQL Server 2005 Express with a database including stored procedures and triggers, we have an installation package where you just have to click on setup.exe and let it do its thing. When I called the main contact […]
Really nice.Saved my lot of time.Thanks
hi all
I am trying to install sql server 2005 , facing a problem.
when i start installation , at one step it should show options for database,client components and all but it is just showing online documentation..
can anyone suggest me a solution to this problem
plz mail me at harish_trash@rediffmail.com
thank you
Harish