Database deployment from development to live

Thanks to Josh Morales for his post about Sitefinity 4 Database Deployment when developing locally and deploying to a live shared hosting environment. That's a good overview of the options to transfer the database to a remote server.

However, I still wonder how to do it when user data exists on the remote server. Many websites will have user membership data, forum posts, polls, etc. across various tables on the live server. That data is not available on the local development version and we don't want to loose it. It has been discussed before in this thread, but as far as I know, there isn't a procedure for this yet. Am I right? Is there any information available on how to facilitate this process? I guess it's a pretty common scenario.

Thanks!

I'm glad you found the post helpful, and I'd like to see if I can help with your question. If I understand you correctly, your concern is with updating a live site, when the recommended path is to update locally.

The way I have handled this in the past is to get a full backup of the live database, restore it into a local version of the site, then run the upgrade locally. If all is well, I then upload the updated website back to the live site and let it run the upgrade in place (because I know it works locally).

of course I'd have one more backup of the live site before deploying, just in case.

does that help any?

Hi Josh,

Doing the upgrade locally is an interesting approach. I guess that might make it a bit less complex and risky. My main concern however, is that I have a website with user data which is being updated continuously. So, simply restoring a database I have developed locally is not an option as that will delete the user data.

Currently, I am using only a fraction of Sitefinity 3.7 in just a part of my website. In the coming months, the whole site will be rebuild in Sitefinity 4.0. It will hopefully also include the Sitefinity forum module (assuming that it will be released in Q2).

Now, the forum module is a perfect example: Let's assume someone wants to upgrade SF4 Q1 on shared hosting to SF4 Q2. The way I work, I would prepare the upgrade on my local development PC. I would probably not only upgrade SF, but also add content, change some of my user controls, custom classes, and do whatever else is on my to do list. Now I have a new version sitting on my local PC, which includes everything but the up to date user membership data and the forum posts.

I wouldn't know how to proceed here. FTP'ing the new files to shared hosting is not the problem, but how to get the user data in the new version of my site? To make it even more complex: the schema of the database tables SF is using for the forum data may not even be exactly the same; Q2 may introduce minor changes to some tables that have to be configured in the life database.

This is just an example. Perhaps I'm missing something obvious? I feel it can all get rather complex but may be it's not :-)

Hello AP,

You could have several branches of the project and merge them. Upgrading a project should be done under dev environment only or if you have dedicated or virtual private server that you maintain.

Greetings,
Ivan Dimitrov
the Telerik team

Thanks Ivan,

I'll keep that in mind once I'm ready to perform such an upgrade.

This answer does not address the actual problems identified in the post. I have the same questions. I don't see how I can use Sitefinity 4 in a real production scenario until there is a good way to do upgrades and deployments on a live site that is in a continuous state of change.

The way I have actually done this in the past is to use  a tool like Red Gate Sql Data Compare or if you have at least the premium edition of Visual Studio 2010 you will have access to the database tools to also do DB compares or DB schema comparisons. 

Below are the steps that I would normally go through when doing this type of upgrade.

1. Take the web site offline by putting the file App_Offline.htm on the root of the site.
  a. This will ensure that nothing getting updated while you are doing the upgrade.
2. Get a complete copy of the files and database back into development.
3. Perform the upgrade to your Sitefinity site.
4. Test the change in your DEV or TEST enviroment.
5. Deploy the changes to your PROD enviroment.
6. Remove the App_Offline.htm file from the root of your web site and you should now be upgraded.

Also, if you are looking for a file comparison tool to compare files on your local DEV enviroment and the files on your PROD site, check out Beyond Compare 3.  You can find that at http://www.scootersoftware.com/.

Just a word of caution, I have never done this for a Sitefinity 4 web site, but there is always a first for everything.  Enjoy!

Thanks Craig,

I have RedGate SQL Data Compare as well. Good tool! Your steps are great for future reference. I'm still confused about which data from the sf_ tables to synchronize exactly though. I hope it will proof to be not that difficult once I reach the point of doing upgrades this way.

I would say, move all database schema changes and data up to PROD along with the web site files minus the database connection strings.  Just make sure you have the latest copy of your Sitefinity site copied down to your development enviroment before you start the upgrade.

I am actually dealing with this same thing right now, and once I get the entire process mapped out I will make a blog post.  Hope this helps.

Thanks, I'm looking forward to your experiences.

We are having exact same problem here, as we have more than 10 servers (used for testing) so what is our option here if we have some changes in eg: Page structure or generic content. To do it by hand on all servers? What would be the list of sf_ tables we could script and deploy further? Please, any info would be helpful.

Hi ,

-Use database replication and file mirroring tools
-Replace the database directly on the production if you have dedicated server  - you can create a temp subdomain with the same project that will handle backup restore and database connection which would take up to 10 minutes. Then you can make schema comparison to fix some missing data.
-use branches.

There is nothing specific here, what would be the case of another web application if we put it in the same context?

Kind regards,
Ivan Dimitrov
the Telerik team

Hello,

It's 2.5  years later now, so I wonder what the answer would be if I asked the question at the top of this thread again. When 6.1 was being developed, I think I noticed a feature for this in the roadmap, but now that 6.1 is here I can't find it (at least not in the documentation, I have not upgraded from 6.0 yet). Assume a standard license, so without Site Sync.

Hi,

You can transfer content with Site Sync. Configurations and users are still not covered by this feature.
http://www.sitefinity.com/developer-network/videos/sitefinity-site-synchronization

It's a long thread and I am not sure what exactly is the current issue. Please, let me know if you have a more specific question.

Hi Boyko,

Site sync looks great but it's not available for a site with a Standard license. My question is this: say I have a remote live site with a Standard license, and a local development station. On the dev station I create an updated version of the site: I add a module, add some pages, change some pages, etc. Now I need to push to changes over to the live site. How do I do that? I can't just restore the local database on the remote server as I would loose all data that has been added by users like new user accounts, forum posts, etc. I could use a database migration tool such as Redgate SQL Data Compare, but I wouldn't know which tables/columns to push to live and which to leave untouched.

Hello Arno,

The complexity of the task dictated that a feature is needed. This issue is handled by SiteSync and the other methods are not trivial.

A possible scenario is to manually track all changes to the development database. Replicate the production database and apply the changes one by one on the replica. When all changes are applied you can modify the DataConfig to point to the replica and restart the application. This way you have minimum downtime. 

Hi Boyko,

This scenario applies to all kind of websites, small to large, yet SiteSync is only available in the Enterprise edition. Shouldn't there be a limited version of Sitesync in all editions?

How would one manually track changes? I assume that adding a page already causes multiple changes in the database. I can think of one semi automated way, but I am unsure if that would actually work:
1. When development on a new website version starts, get a copy of the live database.
2. Replicate that to the development site and start developing.
3. Once development has completed, we have 3 databases: the live database (1), the copy of the live database when development started (2) and the development database (3). Now we can use RedGate SQL Compare AND RedGate SQL Data Compare to compare (2) and (3), and have scripts generated that will apply these changes.
4. Backup the live database.
5. Run the generated scripts against the live database.

Is this what you mean? It would be great to hear how other people do this.

Hi Arno,

I suggest you direct questions about the Sitefinity editions towards our sales department. This is a technical forum and I wouldn't know why some features are included in the Standard license and others are not.

The technique you described for deployment seems logical and straightforward. I would indeed be nice if we can get more techniques like that. I have personally experienced several deployments and never had a consistent strategy. With Sitefinity the challenge is to separate the metadata from the content. I would think that one will want to migrate the metadata and not to touch any content.

Hi Boyko,

Well, it seems that we agree on what sort of tool/strategy is missing. I will certainly contact Sales, thanks!

Has anyone tried one of the methods posted above? We too use a development and production machine where content is added on production and new templates/widgets/modules are being developed in development. What is the best way to merge the template/widget/page/module changes to production when using a standard license?

Hi Jurgen,

Merge of the databases using third party tools is not tested and is supported, so we can not confirm it this will works as expected.

Furthermore, even using the SiteSync tool, you can sync only one-way - all the changes and new content creation are performed on the source server and synced to the destination server when needed.

Regards,
Svetoslav Manchev
Telerik

Hi,

    Its 2016, I have sitefinity version 7 & after going through this whole thread I still cannot conclude, what exactly is the deployment model of a Sitefinity website. I am having the same questions/queries as the original poster had.

My Scenario:

I have a live production sitefinity website, &  the latest backup of production DB & Code before the development of a CR(Change Request) was started. Now as part of a new requirement, the client asked us to add/edit a custom module with custom functionality & add/edit several pages to the existing site. For that, I had to do some changes in the code (which is not an issue because I can replace the production code with the development code once development is finished), However, how am I going to sync the production db with the changes made in development db while creating a dynamic module or adding/updating pages/content types/content blocks etc.

If I am not wrong, if I create a new module through the module builder, sitefinity makes some changes in the Database (right?). So the changes in development database caused by my operations on CMS backend (Eg: create/modify module, add/modify content type, add widget on page, add/modify page) need to be copied over to my production database as well (right?), or do these operation don't cause any db change and only .config file changes? (If yes, then which config files are affected by the above operation?). If not, then how can I sync these changes made on development DB with the production DB without having to replace the production DB with the development one (Because, Although I am developing after taking the latest production DB backup, the database of the live site would still be constantly changing because of content add/update/delete). What exactly is the approach to this?

Any help would be greatly appreciated, Thanks.

Hi Zepp,

Currently such functionality to migrate/merge some data from one project to another if they are updated in parallel is not available out-of-the-box.

In that case you need to implement some custom services in order to transfer the data.

Regards,
Svetoslav Manchev
Telerik

Svetoslav, thanks for the reply.

I came across a functionality of importing/exporting Modules. This means that any work I have done on my Development environment relating to the module can be imported & shipped to production in a single click. But if I have added some pages, added, modified widgets on a page, That cannot be shipped to production & I have to perform the same activity again on production. Is that right? Is this the normal practice?

Hello Zepp,

Depending on the scenario, the solution could be using of SiteSync module. More information about its functionality could be found here:
- http://docs.sitefinity.com/synchronize-data-between-two-servers

Regards,
Svetoslav Manchev
Telerik