September 18, 2023 at 5:50 am
Hi
We have a SQL Server 2008 on-prem DB server that consists of 4 databases, these 4 databases are one-to-one interconeted via store procedures.
We have planned to migrate those databases to Azure SQL Server, we have encountered an error during the .bacpac creation time due to a one-to-one database connectivity issue since store procedures.
If anyone has a proper idea of this, I would highly appreciate it if you give an advice on this.
Thanks
September 18, 2023 at 4:28 pm
I think the easiest way to do it would be to script out all of the stored procedures that do cross-database communication, drop them, generate the bacpacs, import the bacpacs, recreate the stored procedures from the scripts you created.
Although, are you sure you can import a SQL Server 2008 bacpac to an Azure SQL Server? I would probably double check the docs as you MAY (likely will) need to upgrade to a supported version of SQL before doing the import. I am fairly confident that 2008 won't be able to migrate to Azure without doing an upgrade first.
As far as I am aware, you cannot create a bacpac that contains multiple databases.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 19, 2023 at 6:43 am
Hi Brian
Thank you so much for your advise.
We have check with the test database without SPs it was successfull with bacpac restoration on the Azure. We are encountered during the multiple databases.
Yes, you are correct we are unable to create acpac with the mutile datases.
Thanks
September 19, 2023 at 9:19 am
You moving to Azure SQL DB (PaaS)?
You can't do cross database queries in the normal way on PaaS, you will need to use "SQL Elastic Query", so yes quite rightly your bacpac will fail.
You will need to re-architecture the design or move to IaaS/MI.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply