Database Deployments
Stephen Curial
database deployments and how to synchronize them with code changes.
There were a few tools (which I didn't catch their name) that I'd like
to learn more about and thought it would be a good discussion topic.
Personally I use Visual Studio's database projects. I found a quick
introduction to them if anyone is interested:
http://weblogs.asp.net/gunnarpeipman/archive/2009/07/29/visual-studio-2010-database-projects.aspx.
The way the tutorial shows isn't really how I use them but it's a good
introduction.
I create my schemas, foreign keys, etc. in SQL Server because they
have a good GUI for this. Then I use the Schema Compare tool to look
at the DB, find the changes and save them to sql files that are part
of the project and included in source control. Then as part of our
deployment process we use the Schema Compare tool to do a comparison
between the sql files that describe our DB and the current DB in SQL
Server. The schema compare tool will automatically make sql script to
alter the existing tables, create missing tables, drop tables, etc.
This script can then be executed on your DB when you deploy your code.
What are some other ways that you're managing your database
deployments?
--
Stephen Curial
Twitter: @StephenCurial
Pure Project Tools | Online Project Management Software | www.PureProjectTools.com
Jeff MacDuff
Stephen your work flow seems pretty good, I need to try it out. We have a good workflow for webservices / website / services but honestly for database updates we do everything manually today.
How do people handle "default" data for the databases? I understand the concept of adding the schema information into source control, so it could be re-created. What about the basic "getting standard" data , are you checking that in as well? I am not talking about the runtime data (generated per user/group/etc) but the "default" data in the DB even before user #1 (whatever that means for your service) signs up.
For example at buddy we have about 50 gigs of "default" data we need in any new DB instance without any users. We don't have that scripted because from our experience, the tools always timeout trying to import that much data from a script or file based resource.
Jeff MacDuff
CTO & Co-Founder, Buddy
Email: je...@buddy.com
Jeff MacDuff
Azure scale mobile services -- http://msdn.microsoft.com/en-us/magazine/hh781021.aspx
Stephen Curial
after the database is created/updated. That will probably cover most
use cases but I don't know about your case where you are loading a
fairly large amount of data into the DB before deployment.
What type of data are you loading, Jeff? Is it transactional or
relation data? Is it mostly large BLOBs? Is it Key/Value data?
(Perhaps a different, non-RDBMS would have a better solution for you?)
On Feb 10, 9:23 am, Jeff MacDuff <j...@buddy.com> wrote:
> Special thanks to Marcelo for setting up the event and Scott for speaking , I thought it was great and I am looking forward to the next meetups.
>
> Stephen your work flow seems pretty good, I need to try it out. We have a good workflow for webservices / website / services but honestly for database updates we do everything manually today.
>
> How do people handle "default" data for the databases? I understand the concept of adding the schema information into source control, so it could be re-created. What about the basic "getting standard" data , are you checking that in as well? I am not talking about the runtime data (generated per user/group/etc) but the "default" data in the DB even before user #1 (whatever that means for your service) signs up.
>
> For example at buddy we have about 50 gigs of "default" data we need in any new DB instance without any users. We don't have that scripted because from our experience, the tools always timeout trying to import that much data from a script or file based resource.
>
> Jeff MacDuff
> CTO & Co-Founder, Buddy
>
>
>
>
>
>
> -----Original Message-----
> From: dotnet...@googlegroups.com [mailto:dotnet...@googlegroups.com] On Behalf Of Stephen Curial
> Sent: Tuesday, February 07, 2012 10:14 PM
> To: Dot Net Startup
> Subject: Database Deployments
>
> At tonight's meetup there was a lot of discussion around how to manage
> database deployments and how to synchronize them with code changes.
> There were a few tools (which I didn't catch their name) that I'd like
> to learn more about and thought it would be a good discussion topic.
>
> Personally I use Visual Studio's database projects. I found a quick
Jeff MacDuff
One of the scenarios Buddy supports is geolocation, and to support geolocation scenarios we have a white label location of places. We have approx. 50 million + locations/places which are "default" in our database and are adding more all the time.
The best solution we have today for "large" default data is to import directly from a live DB into a new instance. Trying to do with from a file set hasn’t been very successful.
The location data needs to be relational because of the complex queries we need to run. For example a mobile app can query buddy for "give me the top 10 coffee shops , within 100 yard of a friend anywhere in the world, where I have rated it 4+ stars, and has metadata = foo". That’s hitting users + friends + locations + metadata string compare all in the same query… and everything is based on location / distance away.
Doing these type of queries without relations (from our experience) can be very inefficient. We looked at different database solutions and most of them worked awesome at ~10 million locations but at ~50 million locations (and the complex queries we do) they fell apart.
Of course once you have 50 million locations + 10 million users + 5 million friend connections that query gets expensive J
-Jeff
Scott Porad
Jeff MacDuff
Hey Scott, hopefully we can get a copy of your slides soon.
Yes we have thought about exposing the location data through a service, however the queries we then need to run become almost not possible from a performance perspective.
For example consider a simple query where you have 50 million places, and you want to find all the places where your (user account in a app) friends are (other user accounts with a link). If you access the location database from a service you would encounter a “world search” rather than a localized search. The idea of serving the location data from a service quickly breaks down if you try and write a performant query in this model. I would like to hear any ideas about how to solve that problem.. from a performance perspective I don’t have a solution expect for a connection.
Very happy to have a separate smaller meeting to talk about these type of location queries, we have tons of resources optimizing our system including looking at alternatives.
Jeff MacDuff
CTO & Co-Founder, Buddy
Email: je...@buddy.com