Yes, SQL Azure's contained database model will take some getting used to, but the issue isn't just about security, there are technical reasons why this is done. In a cloud hosted environment, the infrastructure topology isn't as static as it is with on-premises databases. Databases are routinely shuffled between VMs and data centers, so joining across databases is problematic.
1. Contain your user add-on tables in a separate schema rather than a separate database.
2. SQL Azure and on-premises are not the only two options, there is also Azure IaaS hosting where Microsoft provides an instance of Windows Azure upon which you provision a preconfigured non-Azure instance of SQL Server, or you can install SQL Server using your own media and license.
Now that is a good point that I hadn't considered.
1. Deploy the vendor database.
2. Add custom objects with a different schema which keeps the objects in the database. However, if the vendor reloads or changes anything, custom changes aren't necessarily lost. That is a good point.
3. That should help query performance by keeping the objects in the same database.
Good point!
Thanks.<
Yes, confirm with the vendor if they support creation of user defined schemas and objects. I'm not sure what product this is, but not usual for a vendor to dump and reload the database practically ever, especially without advanced warning.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho