RSS

Category Archives: Uncategorized

Copy a Finance and Operations database from Azure SQL Database to a SQL Server environment


Overview

To move a database, you use the sqlpackage.exe command-line tool to export the database from Azure SQL Database and then import it into Microsoft SQL Server 2016. Because the file name extension for the exported data is .bacpac, this process is often referred to as the bacpac process.

The high-level process for a database move includes the following phases:

  1. Create a duplicate of the source database.
  2. Download the latest SSMS  Link version number should be greater than Release number: 17.7
  3. Run a SQL script to prepare the database.
  4. Export the database from the Azure SQL database.
  5. Import the database into SQL Server 2016.
  6. Run a SQL script to update the database.

Before you begin

Stop the following services

  • Microsoft batch server
  • Data import/ Export Service
  • IIS services

Now Create Copy of the source database with the help of below script.

CREATE DATABASE AxDB_XXX AS COPY OF axdb_mySourceDatabaseToCopy

This SQL statement runs asynchronously. In other words, although it appears to be completed after one minute, it actually continues to run in the background. For more information, see CREATE DATABASE (Azure SQL Database). To monitor the progress of the copy operation, run the following query against the MASTER database in the same instance.

SELECT * FROM sys.dm_database_copies

After compilation of copy database remove the extra schemas if they are there

  • SHADOW
  • BACKUP

Above mentioned schema need to remove from your newly copy database.

After above action remove some user manually the script provided by Microsoft will not remove them even throw an error while deleting. So you have to remove them manually.

After this execute the below script which is available in MS docs.Microsoft.com

–Prepare a database in Azure SQL Database for export to SQL Server.

–Disable change tracking on tables where it is enabled.

declare

@SQL varchar(1000)

set quoted_identifier off

declare changeTrackingCursor CURSOR for

select ‘ALTER TABLE ‘ + t.name + ‘ DISABLE CHANGE_TRACKING’

from sys.change_tracking_tables c, sys.tables t

where t.object_id = c.object_id

OPEN changeTrackingCursor

FETCH changeTrackingCursor into @SQL

WHILE @@Fetch_Status = 0

BEGIN

exec(@SQL)

FETCH changeTrackingCursor into @SQL

END

CLOSE changeTrackingCursor

DEALLOCATE changeTrackingCursor

–Disable change tracking on the database itself.

ALTER DATABASE

— SET THE NAME OF YOUR DATABASE BELOW

MyNewCopy

set CHANGE_TRACKING = OFF

–Remove the database level users from the database

–these will be recreated after importing in SQL Server.

declare

@userSQL varchar(1000)

set quoted_identifier off

declare userCursor CURSOR for

select ‘DROP USER ‘ + name

from sys.sysusers

where issqlrole = 0 and hasdbaccess = 1 and name <> ‘dbo’

OPEN userCursor

FETCH userCursor into @userSQL

WHILE @@Fetch_Status = 0

BEGIN

exec(@userSQL)

FETCH userCursor into @userSQL

END

CLOSE userCursor

DEALLOCATE userCursor

–Delete the SYSSQLRESOURCESTATSVIEW view as it has an Azure-specific definition in it.

–We will run db synch later to recreate the correct view for SQL Server.

if(1=(select 1 from sys.views where name = ‘SYSSQLRESOURCESTATSVIEW’))

DROP VIEW SYSSQLRESOURCESTATSVIEW

–Next, set system parameters ready for being a SQL Server Database.

update sysglobalconfiguration

set value = ‘SQLSERVER’

where name = ‘BACKENDDB’

update sysglobalconfiguration

set value = 0

where name = ‘TEMPTABLEINAXDB’

–Clean up the batch server configuration, server sessions, and printers from the previous environment.

TRUNCATE TABLE SYSSERVERCONFIG

TRUNCATE TABLE SYSSERVERSESSIONS

TRUNCATE TABLE SYSCORPNETPRINTERS

–Remove records which could lead to accidentally sending an email externally.

UPDATE SysEmailParameters

SET SMTPRELAYSERVERNAME = ”

GO

UPDATE LogisticsElectronicAddress

SET LOCATOR = ”

WHERE Locator LIKE ‘%@%’

GO

TRUNCATE TABLE PrintMgmtSettings

TRUNCATE TABLE PrintMgmtDocInstance

–Set any waiting, executing, ready, or canceling batches to withhold.

UPDATE BatchJob

SET STATUS = 0

WHERE STATUS IN (1,2,5,7)

GO

— Clear encrypted hardware profile merchand properties

update dbo.RETAILHARDWAREPROFILE set SECUREMERCHANTPROPERTIES = null where SECUREMERCHANTPROPERTIES is not null

Export the database

Open a Command Prompt window and run the following commands.

cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin

SqlPackage.exe /a:export /ssn:.database.windows.net /sdn: /tf:D:\Exportedbacpac\my.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /sp: /su:

Here is an explanation of the parameters:

  • ssn (source server name) – The name of the Azure SQL Database server to export from.
  • sdn (source database name) – The name of the database to export.
  • tf (target file) – The path and name of the file to export to.
  • sp (source password) – The SQL password for the source SQL Server.
  • su (source user) – The SQL user name for the source SQL Server. We recommend that you use the sqladmin user. This user is created on every Finance and Operations SQL instance during deployment. You can retrieve the password for this user from your project in Microsoft Dynamics Lifecycle Services (LCS).

Screen shot of exporting database

After compilation of Export of the database upload over LCS and download on the target Machine where you need to import database.

Import the database

When you import the database, we recommend that you follow these guidelines:

  • Retain a copy of the existing AxDB database, so that you can revert to it later if you must.
  • Import the new database under a new name, such as AxDB_XXX.

To help guarantee the best performance, copy the *.bacpac file to the local computer that you’re importing from. Open a Command Prompt window and run the following commands.

Use the following script to import database

cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin

SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn: /p:CommandTimeout=1200

Here is an explanation of the parameters:

  • tsn (target server name) – The name of the SQL Server to import into.
  • tdn (target database name) – The name of the database to import into. The database should not already exist.
  • sf (source file) – The path and name of the file to import from.

For me Script look like…

SqlPackage.exe /a:import /sf:C:\backup\AxDB.bacpac /tsn:localhost /tdn:AxDBUAT /p:CommandTimeout=1200

Update the database

Run the following SQL script against the imported database. This script adds back the users that you deleted from the source database and correctly links them to the SQL logins for this SQL instance. The script also turns change tracking back on. Remember to edit the final ALTER DATABASEstatement so that it uses the name of your database.

CREATE USER axdeployuser FROM LOGIN axdeployuser

EXEC sp_addrolemember ‘db_owner’, ‘axdeployuser’

CREATE USER axdbadmin FROM LOGIN axdbadmin

EXEC sp_addrolemember ‘db_owner’, ‘axdbadmin’

CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser

EXEC sp_addrolemember ‘db_datareader’, ‘axmrruntimeuser’

EXEC sp_addrolemember ‘db_datawriter’, ‘axmrruntimeuser’

CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser

EXEC sp_addrolemember ‘DataSyncUsersRole’, ‘axretaildatasyncuser’

CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser

EXEC sp_addrolemember ‘UsersRole’, ‘axretailruntimeuser’

EXEC sp_addrolemember ‘ReportUsersRole’, ‘axretailruntimeuser’

CREATE USER axdeployextuser FROM LOGIN axdeployextuser

EXEC sp_addrolemember ‘DeployExtensibilityRole’, ‘axdeployextuser’

CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]

EXEC sp_addrolemember ‘db_owner’, ‘NT AUTHORITY\NETWORK SERVICE’

UPDATE T1 SET T1.storageproviderid = 0    , T1.accessinformation = ”

    , T1.modifiedby = ‘Admin’    , T1.modifieddatetime = getdate()

FROM docuvalue T1

WHERE T1.storageproviderid = 1 –Azure storage

ALTER DATABASE [] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)

GO

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2

GO

— Begin Refresh Retail FullText Catalogs

DECLARE @RFTXNAME NVARCHAR(MAX);

DECLARE @RFTXSQL NVARCHAR(MAX);

DECLARE retail_ftx CURSOR FOR

SELECT OBJECT_SCHEMA_NAME(object_id) + ‘.’ + OBJECT_NAME(object_id) fullname FROM SYS.FULLTEXT_INDEXES

      WHERE FULLTEXT_CATALOG_ID = (SELECT TOP 1 FULLTEXT_CATALOG_ID FROM SYS.FULLTEXT_CATALOGS WHERE NAME = ‘COMMERCEFULLTEXTCATALOG’);

OPEN retail_ftx;

FETCH NEXT FROM retail_ftx INTO @RFTXNAME;

BEGIN TRY

      WHILE @@FETCH_STATUS = 0

      BEGIN

            PRINT ‘Refreshing Full Text Index ‘ + @RFTXNAME;

            EXEC SP_FULLTEXT_TABLE @RFTXNAME, ‘activate’;

            SET @RFTXSQL = ‘ALTER FULLTEXT INDEX ON ‘ + @RFTXNAME + ‘ START FULL POPULATION’;

            EXEC SP_EXECUTESQL @RFTXSQL;

            FETCH NEXT FROM retail_ftx INTO @RFTXNAME;

      END

END TRY

BEGIN CATCH

      PRINT error_message()

END CATCH

CLOSE retail_ftx;

DEALLOCATE retail_ftx;

— End Refresh Retail FullText Catalogs

Enable change tracking

If change tracking was enabled in the source database, ensure to enable change tracking again in the newly provisioned database in the target environment using the ALTER DATABASE command.

To ensure current version of the store procedure (related to change tracking) is used in the new database, you must enable/disable change tracking for a data entity in data management. This can be done on any entity as this is needed to trigger the refresh of store procedure.

Re-provision the target environment

When copying a database between environments, you will need to run the environment re-provisioning tool before the copied database is fully functional, to ensure that all Retail components are up-to-date.

Follow these steps to run the Environment reprovisioning tool.

  1. In the Shared asset library, select Software deployable package.
  2. Download the Environment reprovisioning tool.
  3. In the asset library for your project, select Software deployable package.
  4. Select New to create a new package.
  5. Enter a name and description for the package. You can use Environment reprovisioning tool as the package name.
  6. Upload the package that you downloaded earlier.
  7. On the Environment details page for your target environment, select Maintain > Apply updates.
  8. Select the Environment reprovisioning tool that you uploaded earlier, and then select Apply to apply the package.
  9. Monitor the progress of the package deployment.

Start to use the new database

To switch the environment and use the new database, first stop the following services:

  • World Wide Web Publishing Service
  • Microsoft Dynamics 365 Unified Operations: Batch Management Service
  • Management Reporter 2012 Process Service

After the services have been stopped, rename the AxDB database AxDB_orig, rename your newly imported database AxDB, and then restart the three services.

To switch back to the original database, reverse this process. In other words, stop the services, rename the databases, and then restart the services.

Reference : http://d365technext.blogspot.com/2018/06/copy-finance-and-operations.html

 

Advertisements
 
Leave a comment

Posted by on June 14, 2018 in Azure database, Uncategorized

 

Tips & Tricks for Debugging in Visual Studio for D365


In this blog, I have covered some tips and tricks supported for D365 in Visual studio.

Tip # 1 – Pin data tips
While debugging code we have frequently hover over data tips in order to see the values contains in variables. In VS we can pin the data tip for the variable to give our-self quick access. To pin the data tip, click the pin icon while hovering over it. You can pin multiple variables.

First way to pin is to select your variable and right-click it as shown in image.

Second way to pin is to hover your variable click pin icon.

Tip # 2 – Conditional Break points
If it is difficult or time-consuming to recreate a particular state in your app, consider whether the use of a conditional breakpoints can help. Right-click a break-point icon (the red ball) and choose Conditions. In the Break-point Settings window, type an expression.


Tip # 3 – Track an out-of-scope object
We can view variables values using debugger window. However, when a variable goes out of scope in the Watch window, you may notice that it is grayed out. In VS we can track those variable by creating an Object ID for it in the Watch window.

To Create an object Id:
– Set a break-point near a variable that you want to track.
– Stop your break-point at your variable.
– Find variable in the Locals window (Debug > Windows > Locals), right-click the variable, and select Make Object ID.
– Right-click the object ID variable and choose Add Watch.



Tip # 4 – View return values for functions
In order to view return values for your functions, look at the functions that appear in the Autos window to see the return value for a function, make sure that the function you are interested in has already executed.



Tip # 5 – Format your string in a visualizer
When working with strings, it can be helpful to view the entire formatted string. To view a plain text, XML, HTML, or JSON string, click the magnifying glass icon Visualizer Icon while hovering over a variable containing a string value.

Tip # 6 – Manage breakpoints
In VS when we set-up some breakpoints and now we need to switch one-off for as it’s getting hit too much but we will need it again for debugging. If we remove the break-point we’ll have to come back and find it again. So instead of removing the break-point we can use Break-point window. This window will show all breakpoints you have set but crucially lets you disable them without un-setting them by simply removing the check-mark. Check it again to re-enable it.

Tip # 7 – Break into code on handled exceptions
The debugger breaks into your code on unhandled exceptions. However, handled exceptions can also be a source of bugs and you may want to investigate when they occur. We can configure the debugger to break into code for handled exceptions as well by configuring options in the Exception Settings dialog box. Open this dialog box by choosing Debug > Windows > Exception Settings. Also in the dialog box window you can search your relevant exception in which you want to break the code when exception occur.







Reference Link: 
1- Tips & Trick to debug in visual studio
2- Visual Studio Debugging Tips That Will Lighten Your Load

 
Leave a comment

Posted by on May 21, 2018 in D365, Debugging, Uncategorized

 

Tags:

Debugging in D365 using VS2015 (When symbols are not loaded)


 
Leave a comment

Posted by on March 28, 2017 in Uncategorized

 

Doodle/Notes Control for AX7


Great post and I want reblog it becaue I am loving this post.

Xalentis

As I’m in the process of learning what can and can’t be done in AX7, I’ve started porting a cloud-based forms system over to AX. One item I require is the ability to allow users to digitally sign documents using a pen or mouse, which is then “stamped” into the PDF document they are signing.

This requires some interesting JavaScript usage within an AX7 Extensible Control and I’ve learned quite a lot about how the Extensible Control Framework hangs together while doing so. In building the signature control I thought I’d give something back to the AX community and created a control that will no doubt be used extensively during those exciting project implementation meetings – a doodle control. Feel free to embed this into your own module or hide it as an Easter Egg if you want.

The basic container form code is shown below, and does nothing but…

View original post 501 more words

 
Leave a comment

Posted by on March 23, 2017 in Uncategorized

 

Renaming product dimensions

Renaming product dimensions

Hi I want to reblog your post because it is very helpful for me.

Dynamics Ax

The task of renaming product dimensions is a relatively common one in nearly every project.
When it comes to Ax 2012 there was an easy way of doing this. You just had to use the default logic which was bound to the rename button.

Dimensions

However this approach is not viable anymore because of the technical limitations of Dynamics 365. Since it’s not possible to change labels at runtime for the new system, there is no way of altering the dimensions on the user interface.

But there is a way around it. Almost all of the controls are using the following edts:

Unbenanntes Bild

As shown above you can create extensions of this edts and change their labels to your needs. This will cover most of the controls. For example i altered the labels of the product dimensions to “Dim1, Dim2, Dim3, Dim4”.

Unbenanntes Bildclip_image002

While this is good workaround and doesn’t take a lot of…

View original post 53 more words

 
Leave a comment

Posted by on March 21, 2017 in Uncategorized

 

Dimension Controls in a Dialog for Dynamics 365 for Operations


 

To put it simply, we just need to create our own custom control type, let’s call it “Dimension Entry No Datasource” control.

Firstly, create an extension package so that you are not tempted to overlayer code. Be sure to include the Dimensions model in your package.  Visit the Dynamics ‘AX7’ menu and create a new model:

Name it however you wish:

And select extension package:

Include any other packages you want to reference.  Think of this as an assembly (DLL) referencing other assemblies.

Extensions is the new bag, baby!

Create a new class in your model, and extend the DimensionEntryControl class:

  1. [FormControlAttribute(‘Group’, ”, classStr(DimensionEntryControlBuildNoDS))]
  2. class DimensionEntryControlNoDS extends DimensionEntryControl
  3. {
  4. public DimensionDefault getDimensionRefRecId()
  5. {
  6. return this.parmDimensionValueSetId();
  7. }
  8. }

You’ll notice in the metadata I’ve also drafted a separate extension class for DimensionEntryControlBuildNoDS as well.

In the class example above, you’ll see we have overridden the getDimensionRefRecId() method which is declared as Protected in the parent class.  By setting it to public, we can use it as required in our dialog form.

Build your package from the AX7 menu.

Now it’s time to make use of your training in SysOperation Framework from AX 2012! Create a new controller class which extends SysOperationServiceController, in this example I’ve called it MyController.  Override the templateForm() method and specify a new form:

  1. class MyController extends SysOperationServiceController
  2. {
  3. /// <summary>
  4. /// Swenka internal use only.
  5. /// </summary>
  6. /// <returns>
  7. /// A <c>formName</c> value.
  8. /// </returns>
  9. protected FormName templateForm()
  10. {
  11. return formStr(MyTemplateForm);
  12. }
  13. public static void main(Args _args)
  14. {
  15. MyController c = new MyController();
  16. c.initializeFromArgs(_args);
  17. c.startOperation();
  18. }
  19. }

The new form I’m using above called MyTemplateForm is just a duplicate of the SysOperationTemplateForm.  Expand the design to the DialogStartGrp group which is the main group for any SysOperation dialog.

Add your new custom dimension control here by right-click and new control:

Typically a dimension entry control can infer the type of dimension entry we are requiring based on the Extended Data Type (EDT) of the table field that is associated to the control.  This will either be a Default Dimension such as on master data, or a Ledger Dimension such as on General Journal lines.

In our case however, we have no datasource on the form. So we must specify explicitly the default dimension controller class in the control properties.  Also set the auto declare to True.

From here it is a quick couple lines of code in the init() of the form to tell the control to display values upon loading:

  1. public void init()
  2. {
  3. super();
  4. DimensionEntryControlNoDS.parmDisplayValues(true);
  5. }

And upon closing the dialog we want to capture that default dimension recId and save it to our data contract for use in our service:

  1. // <summary>
  2. /// Command control message called when the form’s OK button is clicked.
  3. /// </summary>
  4. public void closeOk()
  5. {
  6. if (this.controller().checkCloseDialog())
  7. {
  8. super();
  9. var someRecId = DimensionEntryControlNoDS.getDimensionRefRecId();
  10. //now we need to save this in the data contract
  11. //via calling…
  12. DataContract c = this.controller().getDataContractObject(‘_myContractParameterName’);
  13. c.parmDim(someRecId);
  14. //now we can close the form, and execute the job
  15. if(this.controller().skipRunOperation())
  16. {
  17. this.controller().skipRunOperation(false);
  18. }
  19. else
  20. {
  21. this.controller().dialogClosedWithOk(this.dialog());
  22. }
  23. }
  24. }

Using a simple action menu item to invoke the service, we can see the dialog displays our default dimensions for the current legal entity:

 

Tags:

Model, Model store, Importing, Compilation, IL generation and Upgrade scripts.


Nice post can I reblog it on my blog if you do not having problem.

Raziq D365FO's Blog

I prepared this post from different sources which would give you clear cut picture on Importing & Compilation objects and IL generation. So, let’s begin withModel and Model store.

What is Model?
A model is a set of elements in a given layer. Each layer consists of one or more models. Each layer contains one system-generated model that is specific to that layer. Every element in a layer belongs to only one model. In other words, no element can belong to two models in the same layer, and every element must belong to a model.

A default model owned by Microsoft exists in each layer. Default models cannot be modified.

A model is permanently associated with the layer that the model was created in. If you need to move one of your models from one layer to another, you must create a project from the model in the Application…

View original post 804 more words

 
Leave a comment

Posted by on September 14, 2016 in Uncategorized

 
 
Anitha Eswaran - Dynamics Ax

Microsoft Dynamics Ax blog

AXAPTA Hut

AxaptaHut ax = new AxaptaHut();

All About Dynamics 365

Dynamics 365, D365, Implementor

Syed Rafay Ali

This blog contains information about Functional techniques and guidelines in Microsoft Dynamics AX, including tips, tricks, tutorials, tools and upcoming news enhancement in Microsoft Dynamics Ax

Philippsen's Blog

Everyday findings in my world of .net and related stuff

Microsoft Dynamics AX

A great WordPress.com site

Xalentis

All things Blockchain, AI and IoT

Dynamics Ax

Technical Knowledge

timsaxblog

A blog about implementing Microsoft Dynamics AX and Dynamics 365 for Operations

Microsoft Dynamics 365 Blog

Start your organization's digital transformation with Dynamics 365, A Blog by Sandeep Chaudhury

DEVSerra - Dynamics AX development blog

Your official Microsoft Dynamics AX blog.

OrganicAX

Discovering Dynamics

AX

A blog by Hai Nguyen

Learn Dynamics Ax with Johnkrish

Live as if you were to die tomorrow. Learn as if you were to live forever - Mahatma Gandhi ****** The more I learn, the less I know - Albert Einstein

Twisted Untwirled

Just another WordPress.com site

ramdynamicsax

Just another WordPress.com site