0

I'm in the final stages of building a Vb.Net WinForms app in a client/server scenario (forms over data stored in SQL Server)

In this particular project we won't be the ones deploying the software so we're looking for a way to automated the Database part of the setup as much as possible.

The client application is being deployed with ClickOnce, so that's sorted.

As for the Database setup, can you advise on the best way to automate this? Typically the deployment would go like (this is what we'd like to automate):

  • Install SQL Server Express Edition as an individual instance in mixed mode authentication (we must use Mixed mode because we can't gurantee we'll have AD). (if possible have the user specify SA password during install, if not possible, we can set it to a preset)

  • Configure the instance to use a fixed TCP/IP port

  • Open the TCP/IP port in the Windows Firewall

  • Finally run a SQL Script to Create the DB (Tables/stored procs/base date)

Please bear in mind we're using Visual Studio Express so we don't have InstallShield or the "Application Setup" template.

Can you please advise/suggest on the best course of action base don your experience?

Thank you Pedro.

Improve this question
1
  • Do you plan to support updates in the database or not? Commented Apr 22, 2015 at 21:16

2 Answers 2

Reset to default
3

We do something similar except we don't install the SQL Server instance ourselves. The rest (database creation, running a script with GO etc) we do using SMO (https://msdn.microsoft.com/en-us/library/ms162169.aspx) through C#. Alternatively, if you were using SSDT (https://msdn.microsoft.com/en-us/data/tools.aspx) it would be easy to deploy the database part as a Data-tier Application (https://msdn.microsoft.com/en-us/library/ee210546.aspx) to be able to control versioning etc.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

thanks. this is pretty much my fallback plan. in my case I was planning to script the entire database as T-SQL and run the script against the target server after installation. Is there a particular advantage of SMO over using a T-SQL script, in this particular scenario?
Basically we do run scripts but we do it using SMO. The main advantage is that you can have the same execution options as if you were running the script from SSMS i.e. using transactions, GO statements which makes altering views possible etc.
As for automating the deployment of SQL Express, have a look here: msdn.microsoft.com/en-us/library/dd981032(SQL.100).aspx and here: social.msdn.microsoft.com/Forums/sqlserver/en-US/…
1

I have shipped sql in apps before and it can be a right pain - you can script the install but it is complicated and error prone.

Do you need to use sql server express, maybe compact edition will give you what you want? The deployment is a lot simpler.

If you do need to distribute it and you don't want the users to set it up themselves you could look at virtualizing it, you can do it with spoon.net so there is no setup to do, just launch the exe when your program starts - this way you can configure it as you like, with secure sa password etc and just download and run.

Steps on how to do it:

https://support.spoon.net/customer/portal/articles/1782546-virtualizing-sql-server-express-2008-using-the-snapshot-method

Spoon is pretty cheap and in cases like this is awsome!

ed

Improve this answer

2 Comments

If I understand correctly this would package a kind of "portable" install of SQL server with my app/installer. Do you know if this is OK/allowed to be done from a licensing perspective? Also, in this case, how do you go solve the issue of using a named instance and/or setting a fixed port and opening the port on the firewall to allow for incoming client connections? thank you
Check the licensing but it should be ok (don't trust a stack overflow comment for licensing!), I would configure it to use the same port and connect to that rather than a named instance. You can script the windows firewall if you have the correct permissions

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.