Database initialization

Automated deployment for web applications and databases

Moderators: Mike Upton, justin.caldicott, Sean.newham, csmith, chirayu, DavidSimner, david.conlin

Database initialization

Postby swinghouse » Thu Jan 10, 2013 6:37 pm

During testing, we need to pull (quite a lot of) data from our production server into the test environment(s).

I've setup a TeamCity projects that:

1. executes SQL Data Compare which generates a script file;

2. packages that script file into a NuGetPackage.

This is accompanied by a Deployment Manager project that:

1. pulls the NuGet package from the NuGet server.

2. runs a PowerShell script (located in PostDeploy.ps1) which tells sqlcmd.exe to execute the sql script generated by Data Compare.

When the amount of data in the script file is "moderate" (I haven't figured it exactly what I mean by that just yet!), everything works fine.

However, when the script file grows larger, I get the following error from sqlcmd.exe:

There is insufficient system memory in resource pool 'internal' to run this query


From http://www.red-gate.com/messageboard/vi ... hp?t=15997 I gather that you can let SQL Data Compare break the script into transactions, but - like I point out in that thread - I haven't figured out if that option can be set from the commandline.

My question regarding Deployment Manager is:

Are there better ways of accomplishing what I'm trying to do? (I could very well be heading in the wrong direction here!)

/Mattias
swinghouse
 
Posts: 99
Joined: Mon Sep 12, 2011 10:51 am

Postby chirayu » Fri Jan 11, 2013 3:59 pm

Hi Mattias!

I got in touch with Compare Team here at Red Gate.

So the script fails because of not enough memory. The Compare team say, "you usually fix it by changing a setting in Application Options, but I don’t think we expose that setting in the command line at all."

I have checked Uservoice forum for Data Compare and they do not have any idea posted around this topic. It would be great if you could post this on the Data Compare user voice forum. I would post it myself, but I think you as a user could I describe the new feature suggestion better.

Thanks!
Chirayu Shishodiya
Software Engineer - Deployment Manager
Red Gate
chirayu
 
Posts: 68
Joined: Mon Sep 17, 2012 5:48 pm

Postby swinghouse » Fri Jan 11, 2013 4:39 pm

Hi Chirayu,

Thanks for looking into this! I'll post a suggestion on the Data Compare User Voice forum.

/Mattias
swinghouse
 
Posts: 99
Joined: Mon Sep 12, 2011 10:51 am

Postby swinghouse » Fri Jan 11, 2013 4:40 pm

Do you have any feedback regarding my second, more "Deployment Manager-y" question:

Are there better ways of accomplishing what I'm trying to do? (I could very well be heading in the wrong direction here!)


/Mattias
swinghouse
 
Posts: 99
Joined: Mon Sep 12, 2011 10:51 am

Postby chirayu » Fri Jan 11, 2013 6:52 pm

Hi Mattias!

So there are two different ways you could possibly achieve what you have said above:
- You can use SqlCI.exe Teamcity plugin - It creates packages to update databases to upgrade to specific versions of a database (it deals with schema and static data).
- On your target server deployment manager can make a call to SQL Data Compare to generate a script and make changes on the fly from postDeploy script. This wont be calling SQLCmd.exe and hence you should be able to escape the insufficient memory error.

Hope that helps!
Chirayu Shishodiya
Software Engineer - Deployment Manager
Red Gate
chirayu
 
Posts: 68
Joined: Mon Sep 17, 2012 5:48 pm

Postby swinghouse » Mon Jan 14, 2013 5:21 pm

Hi Chirayu,

Thanks for the feedback!

The first method you describe is the one we use for the database structure and static data - works wonderfully! Unfortunately, we can't pull all relevant test data from production this way since we need to get data that simply can't be defined as static data in a version control system.

Enter the second method involving SQL Data Compare. This would certainly work, but doesn't it require a separate Data Compare license on each target machine? That wouldn't go down very well with our bean counters...
:? I hope I'm wrong...

/Mattias
swinghouse
 
Posts: 99
Joined: Mon Sep 12, 2011 10:51 am

Postby chirayu » Mon Jan 14, 2013 5:59 pm

Hi Mattias,

In its currently released form, you do require a separate licence.

The good news is that there is a team working on this. In the next month or two, you would not need a licence for Data Compare or Compare on your target machine. It will be covered by the Deployment manager licence for that machine.

Thanks!
Chirayu Shishodiya
Software Engineer - Deployment Manager
Red Gate
chirayu
 
Posts: 68
Joined: Mon Sep 17, 2012 5:48 pm

Postby swinghouse » Tue Jan 15, 2013 9:12 am

Hi Chirayu,

Thanks for the heads-up about current development! I knew about the move away from requiring an SQL Compare license on each machine, but didn't know that this will apply to SQL Data Compare as well. That is excellent news! :D

In the meantime, I'll probably trigger Data Compare directly from the TeamCity server, which is doable since this server thankfully - in this case! - can access all involved target database servers. It's not totally ideal since I'd naturally like to keep all actual deployment work in Deployment Manager, but it will work for now.

Thanks for all the feedback - most appreciated!

/Mattias
swinghouse
 
Posts: 99
Joined: Mon Sep 12, 2011 10:51 am

Postby swinghouse » Tue Jan 15, 2013 9:37 am

Regarding the other question in this thread, about letting Data Compare break its script into transactions when you run it from the commandline, I've now added a feature request on the UserVoice forum for Data Compare: http://redgate.uservoice.com/forums/147879-sql-data-compare-feature-suggestions/suggestions/3551991-make-the-split-transaction-batch-operations-opti
swinghouse
 
Posts: 99
Joined: Mon Sep 12, 2011 10:51 am

Postby chirayu » Tue Jan 15, 2013 10:59 am

Thanks Mattias!
Chirayu Shishodiya
Software Engineer - Deployment Manager
Red Gate
chirayu
 
Posts: 68
Joined: Mon Sep 17, 2012 5:48 pm


Return to Deployment Manager

Who is online

Users browsing this forum: No registered users and 0 guests