My experience vs the getting started guide. Also, dacpacs
I was following along with the guide here: https://documentation.red-gate.com/disp ... ng+started
I actually made a table rather than a view, but otherwise the process is the same. When I reached the "Create a view using the online-editing method" section, things did not go as expected. When I clicked view> ReadyRollDBResynch I did not get the window displayed in the guide, I got this window:
Also, I noticed in SQL Server object explorer that I have the (localDB) instances, but I also have the "Projects" folder, with an associated .sqlproj. When I hit the start button in the visual studio main menu the table I created (dbo.orders) isn't added to the sqlproj, nor is it added if I build the solution, so I guess this must be what happens if the DB Synch completes successfully. But it's a bit confusing.. I've added a table, "ran" the solution, bulit the solution... and yet my table still doesn't "really" exist.
Further... does this mean ReadyRoll relies on dacpacs under the covers? If so, does that mean that a project that cannot use dacpacs will also not be able to use readyroll - primarily as a result of references to sibling vendor databases whose schema we cannot change and thus are unable to turn into a dacpac? (Discussed by myself and Jamie Thompson in this thread: https://social.msdn.microsoft.com/Forum ... forum=ssdt )
I actually made a table rather than a view, but otherwise the process is the same. When I reached the "Create a view using the online-editing method" section, things did not go as expected. When I clicked view> ReadyRollDBResynch I did not get the window displayed in the guide, I got this window:
Also, I noticed in SQL Server object explorer that I have the (localDB) instances, but I also have the "Projects" folder, with an associated .sqlproj. When I hit the start button in the visual studio main menu the table I created (dbo.orders) isn't added to the sqlproj, nor is it added if I build the solution, so I guess this must be what happens if the DB Synch completes successfully. But it's a bit confusing.. I've added a table, "ran" the solution, bulit the solution... and yet my table still doesn't "really" exist.
Further... does this mean ReadyRoll relies on dacpacs under the covers? If so, does that mean that a project that cannot use dacpacs will also not be able to use readyroll - primarily as a result of references to sibling vendor databases whose schema we cannot change and thus are unable to turn into a dacpac? (Discussed by myself and Jamie Thompson in this thread: https://social.msdn.microsoft.com/Forum ... forum=ssdt )
Comments
Thanks for getting in touch, and may I apologize for the rough start you've had with ReadyRoll so far.
Firstly, the issue with the Refresh button (in the DbSync tool) is a bug. Here's a fix for that:
http://download.ready-roll.com/ReadyRol ... 4.1133.exe
Note that you may have to uninstall your current version of ReadyRoll first to install this version.
With regard to the issue applying the table change you mentioned, it seems you may have gone down a slightly wrong path there: instead of adding a table via the "Projects" node in the Solution Explorer, you actually need to use the tools within the "SQL Server" node (i.e. the connected designers). Looking at the getting started guide, I realize this is not made at all clear.
We've just done an update to the getting started guide to make these steps more explicit about how the changes are applied. Additionally, all the screenshots have been brought up-to-date with the latest version of ReadyRoll and Visual Studio 2015. Hopefully this makes the process of making online/connected changes a bit clearer:
https://documentation.red-gate.com/disp ... ng+started
In terms of dependencies, I can confirm that ReadyRoll does not have any dependency on DACPACs, due to the different way in which ReadyRoll database projects are deployed: DACPACs use the model-driven style of deployment, whereas ReadyRoll uses the migration-driven deployment style. The equivalent of DACPACs in ReadyRoll is the SQLCMD Package format. You can read more about this here:
https://documentation.red-gate.com/display/RR1/PowerShell+Deployment.
Product Manager
Redgate Software
I did indeed add the table to the localdb node, I was just watching the project folder to see what would happen when building.
Good to hear on the dacpacs, I did some more exploring myself, as well as working with cross database references. For a procedure everthing worked as expected (since SQL Server won't be parsing that). I expect that a view with cross database references would have issues.
I was reading the documentation here: https://documentation.red-gate.com/display/RR1/ReadyRoll+1.4+release+notes#ReadyRoll1.4releasenotes-1.4.0-December6th,2012, which made me wonder about dacpacs. I notice that one way to add an external database reference is to add a data tier application, which is what got me thinking about this.
The scenario that caused us problems with dacpacs is that the database referred to in the cross database references is a vendor system. Indeed, it's my company's main ERP. We can't touch the schema of that database, so if we want to write custom reports they need to live elsewhere - a sibling database on the same server that refers to the ERP database via three part names. Unfortunately, the ERP database does not compile into a dacpac (for a multitude of reasons), and of course the vendor is never going to do all the work to make their database schema dacpac compatible just so we can get our own development process sorted out.
Given that readyroll doesn't need to "compile" the database, but just needs to be able to create it on an actual SQL Server, I expect this problem doesn't exist for readyroll.
Further on that sort of topic, can readyroll handle circular references? For example, where a procedure in database A references a table in database B, while a procedure in database B references a table in database A? In dacpacs the solution is apparently to use partial projects (I never tried this - I heard it was pretty convoluted, but we couldn't use dacpacs anyway so I never bothered to try it).
In terms of support for circular cross-database references, we have customers that have managed to implement this in their ReadyRoll projects, but it can take a bit of tweaking of the solution/project settings to get it to work.
However you may need to adjust the order of project deployment. In ReadyRoll this is set by the project build order (configurable via the Project Dependencies dialog in Visual Studio), rather than by project references. Also, given that ReadyRoll deploys one database after another (i.e. you can't mix the order of deployment of specific objects across multiple databases), you may need to rely on SQL Server's Deferred Name Resolution functionality to allow you to create your stored procedures before the referenced tables have been deployed.
To take the example you mentioned, your project/script deployment order might look something like this:
Project A deployment
1) Create Database A
2) Create Table in Database A
3) Create Proc in Database A that references a non-existent Table in Database B (DNR should permit this)
Project B deployment
4) Create Database B
5) Create Table in Database B
6) Create Proc in Database B that references existing Table in Table A
In this example, compilation of the proc in project A would take place the first time the proc is called, rather than at deployment time, foregoing the usual validation checks.
So basically the idea is that ReadyRoll allows you to get away with anything that SQL Server allows, so long as you're able to organise your scripts into the one-project-per-database structure that ReadyRoll requires.
Product Manager
Redgate Software
I think I've had a bit of a "eureka" moment here. Since "validation" occurs on the shadow database, no kind of cross database reference will have any problem as long as the shadow database is on the same server as any referenced databases.
This is both good and bad.
On the good side, it means that there is a way to use readyroll that will have none of the issues that dacpacs have in the sense that the external dependencies (ie, the referenced databases) don't need to be imported into the project as such. Indeed, there isn't even any need to create database references in the project (as described in the "multi database support") documentation, nor is there any need to use sqlcmd variables in three part names.
As you've just described, there still might be deployment ordering issues where circular references exist. Procedures will be fine, but views (for example) can't use deferred name resolution. So if DB1.dbo.view1 refers to DB2.dbo.table1, and DB2.dbo.view2 refers to DB1.dbo.table2, even readyroll won't be able to handle it "natively" (due, as you descibed, to the database scoped nature of the deployment). Some manual work with pre or post scripts, or splitting single projects into multiple projects, would seem to be required. I'd still consider that to be an up-side to readyroll though, since most of the issues with dacpacs are eliminated.
There is a downside, though: As I mentioned, this all works if (and only if) the target database (with cross database references) is on the same instance as the shadow database, and the required sibling databases are also on that instance.
This implies two possible working cases:
1) The SQL server instance is a "shared" development environment. Basically a copy of the production environment. It therefore has all of the necessary sibling (referenced) databases. This means the target database is on the shared instance, and the shadow database is also on this shared instance. Which it turn means that, if you have a lot of developers, all of their "local" development environments (ie, the shadows) are actually databases on the shared instance. But this now pollutes the development environment. It's no longer "just a local environment for the developer", it's an environment in which everyone (all devs, the DBA's, etc) see everything (all of the individual shadows), it's managed by infrastructure and the DBA, etc.
2) The SQL server instance is local to the developer's machine (or the developer has an instance all of their own on some managed server). But this basically implies taking copy of all necessary databases on the production server (schema at least - not necessarily data) and replicating all of that to each developer machine. This nice thing about this approach is that the developers' development environments are indeed local and isolated to them, the downside is, of course, that we've basically replicated the whole enterprise (or at least a whole server) onto every developer's machine. This is probably OK if you can do schema only. But if developers start asking for data too, this becomes a bit problematic.
I expect option (2) is better than option (1).
I had (at first) hoped to have the developer shadow databases on their own localdb instances (since they don't really belong on a proper, managed SQL server), and the target database on the shared SQL server (so that devs can play with data), providing the best of both worlds. But separating the shadow from the target means breaking the "native" architectural cross database support that readyroll provides.
Indeed, in fact this the reason why we decided to place the Shadow db on the same server by default.
As a general rule, we recommend working case 2 if you plan to author your changes in a "connected" fashion (e.g. edit your database in SQL Management Studio and then use the ReadyRoll DbSync tool to Import those changes). This is to ensure that your developers don't accidentally import each other's changes into their ReadyRoll projects, which could result in changes being duplicated. As you pointed out, there is potentially a lot of work in setting up your projects so that an individual developer can recreate an environment entirely from sources, to provide your developers with the isolation that they need to make this work.
However if you were to adopt a "disconnected" approach, (i.e. prepare your change scripts manually and add them to your ReadyRoll project when you're ready to deploy & source control them), then you could feasibly utilise a shared environment in the interim. This would mean, though, that you wouldn't be able to use the DbSync tool to generate your scripts. But without using DbSync, you won't have to worry about the Shadow databases, as they won't be deployed.
Product Manager
Redgate Software