Red Gate forums :: View topic - Database initialization
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
Deployment Manager
Deployment Manager forum

Database initialization

Search in Deployment Manager forum
Post new topic   Reply to topic
Jump to:  
Author Message
swinghouse



Joined: 12 Sep 2011
Posts: 95

PostPosted: Thu Jan 10, 2013 6:37 pm    Post subject: Database initialization Reply with quote

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:

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


From http://www.red-gate.com/messageboard/viewtopic.php?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
Back to top
View user's profile Send private message
chirayu



Joined: 17 Sep 2012
Posts: 51

PostPosted: Fri Jan 11, 2013 3:59 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
swinghouse



Joined: 12 Sep 2011
Posts: 95

PostPosted: Fri Jan 11, 2013 4:39 pm    Post subject: Reply with quote

Hi Chirayu,

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

/Mattias
Back to top
View user's profile Send private message
swinghouse



Joined: 12 Sep 2011
Posts: 95

PostPosted: Fri Jan 11, 2013 4:40 pm    Post subject: Reply with quote

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

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


/Mattias
Back to top
View user's profile Send private message
chirayu



Joined: 17 Sep 2012
Posts: 51

PostPosted: Fri Jan 11, 2013 6:52 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
swinghouse



Joined: 12 Sep 2011
Posts: 95

PostPosted: Mon Jan 14, 2013 5:21 pm    Post subject: Reply with quote

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...
Confused I hope I'm wrong...

/Mattias
Back to top
View user's profile Send private message
chirayu



Joined: 17 Sep 2012
Posts: 51

PostPosted: Mon Jan 14, 2013 5:59 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
swinghouse



Joined: 12 Sep 2011
Posts: 95

PostPosted: Tue Jan 15, 2013 9:12 am    Post subject: Reply with quote

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! Very Happy

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
Back to top
View user's profile Send private message
swinghouse



Joined: 12 Sep 2011
Posts: 95

PostPosted: Tue Jan 15, 2013 9:37 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
chirayu



Joined: 17 Sep 2012
Posts: 51

PostPosted: Tue Jan 15, 2013 10:59 am    Post subject: Reply with quote

Thanks Mattias!
_________________
Chirayu Shishodiya
Software Engineer - Deployment Manager
Red Gate
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group