Database initialization

swinghouse

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

chirayu

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!

swinghouse

Hi Chirayu,

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

/Mattias

swinghouse

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

chirayu

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!

swinghouse

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

chirayu

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!

swinghouse

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!

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

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

chirayu

Thanks Mattias!