November 9, 2016 at 1:21 am
Hi All,
I apologise in advance if this has already been covered but searching for deploying, distributing or packaging a database was not giving me the results I was looking for.
I am creating an application which will hopefully be used by customers. This application is based on SQL Server database and also includes SSRS Reports and SSIS packages.
While 90% of the data will come from the customer there are some reference and other tables that need to be populated from the start.
These organisations will have someone either internally or externally that will be able to take the roll of 'dba' for setting up the database so I am asking what the best approach would be.
I figure there are 3 ways I can provide the database and basic data:
1. database to be attached
2. backup to be restored
3. provide scripts to create and populate the database
Which would you prefer or what is the best practice?
Thanks
Steve
November 9, 2016 at 2:37 am
If the database is small then my choice would be to provide scripts to create and populate the database.
😎
Question, will you at any stage have to update or refresh the data provided to the customers?
November 9, 2016 at 3:13 am
Hi Eirikur
This would be considered small compared the the databases I am use to working on but here are counts from sys.objects
User Defined Tables: 54
Views: 46
Stored Procedures: 77
It is possible I would be updating data, but that would be system data, e.g. reference/lookup tables. Also as with any application it is possible I would be updating structures, enhancing not removing though.
Thanks
Steve
November 9, 2016 at 4:40 am
This is well within the limits of using a script file, assuming that the actual data isn't too large and that the end customer can unzip the script file;-)
😎
Additions and updates can easily be done in the same manner, are you using any kind of source / version control for the database and the data?
November 9, 2016 at 5:04 am
Thanks again Eirikur.
I am currently using TortoiseSVN for all application related files but am still really in development mode, though some people are doing alpha testing for me.
I would love to have something which could generate update scripts for me by comparing versions but I assume any tools like that will be way out of my budget which is pretty much non-existent at this time.
Take care
Steve
November 9, 2016 at 5:26 am
November 9, 2016 at 9:01 am
I agree with Eirikur (I type that a lot).
You can easily script this install. It's quite small. My one addition to what has already been said is that you should get your database and it's scripts into source control as a part of your development and deployment processes. This is the best way to understand and manage what versions you are developing, releasing, etc.
The database tools that Eirikur linked to are good. You should also take a look at Redgate Database Lifecycle Management[/url] tools. (DISCLAIMER: My employer).
We not only have the tools, but there are a bunch of articles available there and over at Simple-Talk.com on deployment and development processes around databases.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 9, 2016 at 9:16 am
Good point Grant, forgot to mention the Red Gate tools, DLM, source control and all the available articles, the tools/source control takes the support for generating differential scripts etc. to the next level, will save lots of time on the long run.
😎
November 9, 2016 at 9:51 am
Asking your customers to restore a .BAK file or attach an .MDF file will not be possible if they are not on the same version of SQL Server. For that reason, it makes sense to package your database as DDL and SQL insert scripts.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 9, 2016 at 9:58 am
SteveD SQL (11/9/2016)
...I figure there are 3 ways I can provide the database and basic data:
1. database to be attached
2. backup to be restored
3. provide scripts to create and populate the database
Which would you prefer or what is the best practice?
Thanks
Steve
I've worked at companies that distributed the database using the scripting method and it works nicely. I'd just make sure the script is written to consider the following:
- handle potential differences in the versions of SQL Server that this would be installed on
- consider how you will handle different versions of your software, as each version could have different schema changes
- consider how you will handle a client upgrading from one version of your software to another
You may even want to track what clients have what version if possible.
November 9, 2016 at 11:39 am
Eric M Russell (11/9/2016)
Asking your customers to restore a .BAK file or attach an .MDF file will not be possible if they are not on the same version of SQL Server. For that reason, it makes sense to package your database as DDL and SQL insert scripts.
This is the main reason for why I'd rather do a script based distribution, easy to detect the @@version etc. and play it from there.
😎
November 9, 2016 at 2:35 pm
wouldn't creating a dacpac be an option? i
m not sure if they are SQL Server version agnostic or not, but the dacpac has the object schemas, ; i though a bacpac was for data, but it looks like that's really for Azure deployments, now that i started fiddling with it again.
Lowell
November 9, 2016 at 2:55 pm
Lowell (11/9/2016)
wouldn't creating a dacpac be an option? im not sure if they are SQL Server version agnostic or not, but the dacpac has the object schemas, ; i though a bacpac was for data, but it looks like that's really for Azure deployments, now that i started fiddling with it again.
Sure. SQL Server Database Tools use the dacpac for deployments. It's a viable approach. It's not my personal preferred approach, but it can, and does, work well. The primary issue with using a dacpac is around changes that could result in data loss. The dacpac has a choice of not making that change, or making the change by throwing away the data. It's something to be aware of so you can work around it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 9, 2016 at 11:29 pm
Thanks to all for great advice and the pointers to various tools.
Cheers,
Steve
November 11, 2016 at 10:50 am
I'm sure you already know this but its worth mentioning. In your source control dont forget to create a tag for your releases so that you know what version of your application goes with what version of the database.
Scripting is fine for creating the objects to then fill them with data. It is when updating objects (when there is already existing data to consider) that things can get quite complicated.
----------------------------------------------------
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply