2

I know this is a sort of odd request. I myself have never had to even think about a scenario like this until today. I'll go straight to the point to make the question easy to read. I suspect the right answer will involve more of "thinking outside the box" than a hidden feature or capability that somehow I'm not aware of.

Here is the thing: I need a PowerBI dashboard to query my SQL Server, but the PowerBI dashboard will have a connection string that queries a database (let's call this database "DatabaseOne_Report") whereas the SQL Server will host a database with a different name (let's say "DatabaseOne").

So when the PowerBI dashboard runs a query to DatabaseOne_Report.dbo.MyTable I want SQL Server to provide data from DatabaseOne.dbo.Table (notice the "_Report" suffix is gone from the database name). Is that even possible somehow?

I can anticipate your first question would be why I am using a connection string with the wrong database name. The answer is that this dashboard connects to different environments and these environments host this database using either 1 of 2 different names for reasons that are beyond the scope of what I'm asking here. The bottom line is that we are trying to avoid re-writing the connection string of the PowerBI dashboard and hope we can use a single connection string to tackle both possible DB names.

I read a lot of questions in different forums to tackle aliases of 2 databases hosted in different boxes, but never seen anybody asking 2 databases hosted in the same box but with potentially two different names.

I thought about creating a empty "shell" of a database with the other name with nothing but synonyms, but that would be a lot of configuration... I was hoping to find something simpler.

Thanks!

  • Do you mean a database alias? docs.microsoft.com/en-us/sql/database-engine/configure-windows/… – McNets Jan 7 at 16:03
  • mmm... not really @McNets,a server alias is an alternate way to name a connection to a server (server name/protocol/port) and in my question I'm talking about aliasing a Database name (which is different) – Martin Surasky Jan 7 at 17:31
  • I did once something like this, the vendor won't allow objects or users to be created on the "source" db, and we had to create some reports, so we added a "new" database, on which created views that accessed the original tables, you only have to grant privileges on the original DB/tables so the views can access them. – dbamex Jan 7 at 23:01
3

Here's what it would look like: enter image description here

  • This is the essential way to go about this - a different DB that links the tables as views (to clarify, the Test DB above would be the DatabaseOne database, and the test2 DB would be the DatabaseOne_Report. This has the advantage of only exposing the tables desired, and the disadvantage of extra maintenance, but it does do what you are asking for. – Laughing Vergil Jan 7 at 17:27
  • Yes, this is the only approach I can think of myself. I know this may be an edge case, but wouldn't it be nice for SQL Server to provide something like a "Database Alias" right off the box? If something like that existed then I would not need to orchestrate all this by hand. I will yet not mark this as the final answer as I posted the question just a few hours ago and I would like to see if anybody else can come up with something that does not need to be maintained when the schema on the DB changes ( a new table for example). – Martin Surasky Jan 7 at 17:36
  • And depending on the activity on the main database, a database snapshot called "DatabaseOne_Report" could also be an option. – Dominique Boucher Jan 7 at 18:14
3

I see two options.

  • SQL Alias as mentioned in the comment by McNets. But I am not sure if it will work in your scenario, when it does work it is a great tool.

  • Use SQL Replication to create a copy of the database DatabaseOne named DatabaseOne_Report on the same sever. Depending on a few variables, the two versions of the database will be completely in sync to delayed a bit.

  • That would double the data (so the size it takes on disk)... but maybe a new database with the _report name and a set of views that would have the same name as the original tables and pointing on those table in the original DB would work... It will take time to implement and will need to be kept up to date which I guess is more work then just changing the PowerBI DataSource connection string but if you have no other choice.. – Dominique Boucher Jan 7 at 17:04
  • @DominiqueBoucher replication can be a subset of the data. So if they only need to report on part of the data, it could be much smaller then the primary. I am not sure what you mean by 'will need to be kept up to date', replication can keep the data as current as you need. – James Jenkins Jan 7 at 17:11
  • The "kept up to date" was for my suggestion of having views. If new tables are created in the original DB, the new views would need to be created as well. If there is only a subset of the data, then only some views would be required (which will still take less space then replication). Less space, less maintenance, less backup, etc. – Dominique Boucher Jan 7 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.