I currently work for myself and provide IT services to a hedge fund with around ~30 traders and analysts who have direct access to a home-grown operational analytics database. I have wanted to rip through and clean up the database in order to improve performance long-term, as every time I make a huge performance win, my hand giveth and the firm's growth taketh away: they've grown about 50% since I started helping them.
My current idea is to push all data they currently have read access to through direct SQL connection to the operational analytics db, and move it into a new database OperationalAnalyticsNew, rename the operational db OperationalAnalyticsCore, and rename the new database again to OperationalAnalytics. In this way, I won't break anyone's SQL connections when they come into work on Monday morning.
I'm trying to think through everything that could go wrong with this:
1. Is it too clever by half? If so, why? ~12 years ago when I got my first professional job, I had the "bright idea" to use "INSTEAD OF" triggers, which backfired on me. Since then, I've learned to try not to be too clever.
2. Lose support for SCHEMABINDING on views, as the below example demonstrates:
CREATE DATABASE OperationalAnalyticsNew
CREATE VIEW dbo.Accounts
Version FROM OperationalAnalyticsCore.dbo.Accounts
Msg 4512, Level 16, State 3, Procedure Accounts, Line 4 [Batch Start Line 48]
Cannot schema bind view 'dbo.Accounts' because name 'OperationalAnalyticsCore.dbo.Accounts' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
Interestingly, the error message is correct, but not complete, since if you use a two-part name that is a synonym, it will also fail (I knew this, but I'm documenting it here so that I can refer my teammates to this thread later on when they may ask):
CREATE SYNONYM dbo.CoreAccounts FOR OperationalAnalyticsCore.dbo.Accounts
CREATE VIEW dbo.Accounts2
Version FROM dbo.OperationalAnalyticsCoreAccounts
3. Because you can't use SCHEMABINDING, you cannot Create Indexed Views, because Indexed Views require SchemaBinding. https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-2017
4. Potential issues with WAIT resource types being confusing - I'm not really sure how a lock escalates on a cross-database join, for example, because I've never really used this approach except for unifying yearly archive databases with barely any activity.
5. Both databases should probably have AUTO_CLOSE OFF, which is a SQL Server best practice, anyway.
6. Both databases should probably use the same collation. Ideally, any joins would take place on non-character columns, but "iff if was a skiff, we'd all be sailing", right?
7. Would need synonyms so that code doesn't break in development environments.
8. Somebody might later get the "bright idea" to enable cross-database ownership chaining. Users would then need to exist in both databases. Rather than go over the security risks and administrative burden of this, I'll just link to Soloman Rutzky's blog post (scroll down to "Reasons to Avoid These Methods"): https://sqlquantumleap.com/2017/12/30/please-please-please-stop-using-impersonation-execute-as/
9. Even heeding good advice and using certificate signing, after I am gone, developers might find certificate signing to be too much of a hassle to maintain. I may just create a situation where someone will look at it in 10 years from now and think, "This guy was a cowboy/idiot for doing it this way with all these certificates". As Erland summarizes: "If you have a multi-database application where there are cross queries all over the place, it is likely that you will find certificates to be a bit too much hassle. Even if you can mitigate the situation with good automation, it will have to be admitted that certificates are good for the few and exceptional cases, but if you need to sign everything to handle permissions it gets a little out of hand." http://www.sommarskog.se/grantperm.html
10. Database integrity is not possible, because foreign keys cannot cross databases, but also not an issue if the Excel-facing database ONLY and ONLY contains views.
11. If someone writes business logic with a BEGIN TRANSACTION wrapping the view, then conceivably this could cause the resource manager to think it needs to escalate the transaction into a distributed transaction. https://docs.microsoft.com/en-us/sql/t-sql/language-elements/commit-transaction-transact-sql?view=sql-server-2017
If the transaction committed was a Transact-SQL distributed transaction, COMMIT TRANSACTION triggers MS DTC to use a two-phase commit protocol to commit all of the servers involved in the transaction. When a local transaction spans two or more databases on the same instance of the Database Engine, the instance uses an internal two-phase commit to commit all of the databases involved in the transaction.
Have I missed anything else?