Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to Migrate AXr3u13...
Finance | Project Operations, Human Resources, ...
Suggested answer

How to Migrate AXr3u13 Business Database from SQLServer2017 into SQLServer2016

(1) ShareShare
ReportReport
Posted on by CBNestor 44

Hello everyone,

I am pretty new in this forum, I hope you can give me a hand with this with a little patience since I am not an expert on Dynamics

When we started this dynamics we (mainly me) started to config all dynamics by using a fresh bought installation on SQL Server 2017, at the beginning everything was good and compatible unitl we started to install the SRSS component, then we realized that SRSS component is was not compatible with SQLServer2017 for AX 2012. So we managed to install a second SQL server 2016 instance and link it to our AX business database 2017.

Since I dont want any problems in the future when we get this in production, I would like you to please help me figure a way out how to Migrate the AX database from SQL2017 into SQL2016 so we can get everything running in a single intance as it should be. As far as I tried SQL server  does not support to downgrade a database.

Your help is greatly appreciated,

thanks a lot for your time,

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,622 Super User 2024 Season 2 on at
    RE: How to Migrate AXr3u13 Business Database from SQLServer2017 into SQLServer2016

    Hi CBNestor,

    I have not seen the issue with number sequences before. I also have not used this tool that much as I usually copy a full database(s). Not sure what exactly might be wrong here. I don't know what scripts you used and for that reason not sure how the number sequences were handled.

    Related to your second question: Yes, you might have overlooked something that is not handled correctly by the TDTT. As mentioned before, it is not supported to copy the data to a production database.

  • CBNestor Profile Picture
    CBNestor 44 on at
    RE: How to Migrate AXr3u13 Business Database from SQLServer2017 into SQLServer2016

    Hi Brandon, I appreciate again for your Answer. Well I have tried different things, since I am not expert make things a little bit difficult.

    I was wondering If a install a fresh new ax database in SQL 2016 and make everything by again by hand. what would be the best way to migrate the as much information  as possible with a tool that is 100% supported by Microsoft dynamics for production. so I could safe as much time as possible,

  • CBNestor Profile Picture
    CBNestor 44 on at
    RE: How to Migrate AXr3u13 Business Database from SQLServer2017 into SQLServer2016

    Hi André thanks for info. I already tried with some of the answers from SQL server Forum. they say I have to script the whole database, and there is indeed a lot of work to do there that I honestly cannot handle myself. I think of two opcions:

    1. I have installed a new AX database (SQL 2016 instance) through the AXsetup, I used the Data beta transfer tool, migrate the data from sql2017 and imported into the AX sql2016 database. and every looks almost perfect... EXCEPT for the number sequences. Everything is wrong and messed up. non of them are incrementing correctly. If I create a new one on a different company it says that the record already exists in the NumberSequenceReferenceTable. no matter if I delete all tables and create new Numbers sequence from start even with the Wizard they do not work properly. So I would like you to tell wich Tables shoould I have to Filter from Importing so I can Import all tables except for the NUmber sequences.

    2. If I want to Use the database in production in the SQL Server 2017 and the SRSS in the SQL 2016. you think it would have any problems in the future?. I haven't install all the components yet like Business connector the would like to use in the future.. and maybe SharePoint also.. Can you help me with confirming this matter?

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,622 Super User 2024 Season 2 on at
    RE: How to Migrate AXr3u13 Business Database from SQLServer2017 into SQLServer2016

    Hi CBNestor,

    To answer the question you asked me above:

    - DIXF will not help you here. The tool is designed for master, reference and journal data. It is not intended to use it for transaction tables. There are just a limited number of entities available out of the box in AX2012.

    - The test data transfer tool is quite slow and might also not be correct. Microsoft is not supporting import into a production database. It is only intended to use it for copying data to a test environment.

  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: How to Migrate AXr3u13 Business Database from SQLServer2017 into SQLServer2016

    You should be able to migrate the model store back using the AXUTIL to export the store from SQL Server 2017 and import the store back into a fresh model store database on SQL Server 2016.  Basically that writes it out as an XML file and reads that XML file back into a different database, of which the SQL Server version is not important.

    As far as the business database, I imagine it could pretty easily be done by writing a cursor to loop through all tables and copying the records using an INSERT INTO .. SELECT statement after a TRUNCATE.  Of course the business database on SQL Server 2016 receiving the records should be a fresh database with the identical schema.  The business database does not use IDENTITY fields or TIMESTAMP field or any data type that can present challenges to this approach.  You would need to work with someone with strong SQL Server skills to explore that approach, but it seems like a reasonable solution to me (not having actually done it before).

  • CBNestor Profile Picture
    CBNestor 44 on at
    RE: How to Migrate AXr3u13 Business Database from SQLServer2017 into SQLServer2016

    Hi Brandon thanks for the answer. I am already working with those intances as you suggested. but since there are no direct support from microft for SQL2017 and do not want any other problems in the future with any compatibility related problem. I prefer reinstall in a cleaner one instance approach and also save little bit of Ram memory getting rid of one that additional instance.

    Do you know Any tool procedure for that data migration.

  • CBNestor Profile Picture
    CBNestor 44 on at
    RE: How to Migrate AXr3u13 Business Database from SQLServer2017 into SQLServer2016

    Your approach is very logical André and will ask there. but have you tried with any Import export feature from AX. any built-in functionality like Data beta transfer tool or DIFX something like that. Thanks a lot for your replay!

  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: How to Migrate AXr3u13 Business Database from SQLServer2017 into SQLServer2016

    You do not technically have to downgrade the database. 

    You can leave the SQL Server Database Engine component at 2017 and co-install side by side a SQL Server Reporting Services version 2016, as long as both are a supported edition in both cases (Standard, Enterprise, etc.).  Your SQL Server license should give you some down version rights to support this configuration.

    For example, I have a SQL Server Database Engine 2016 on one OS and SQL Server Reporting Services 2014 on another OS that uses the 2016 Database Engine instance to host its database.  Works great, no problems installing any of the components or running any reports.

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,622 Super User 2024 Season 2 on at
    RE: How to Migrate AXr3u13 Business Database from SQLServer2017 into SQLServer2016

    Hi CBNestor,

    In fact, it is not Dynamics AX related, but your question is about moving databases between different versions of the SQL Server. You can probably better ask your question on the SQL forum: social.msdn.microsoft.com/.../home

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,622 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,354 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans