Tag Archives: DB

D2DDynamics custom PowerShell module

31 Monday Aug 2015

Posted by in Powershell

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , ,

The D2DDynamics custom PowerShell module is finally available in Codeplex.  The 1.0.0 version includes 15 exportable PowerShell functions and 3 internal helper functions.  They are:

The 3 internal functions are:

This module is dependent on the 0.3.5 release of the DynamicsAXCommunity PowerShell module among others.  There are also some variables that should be set up in your profile if you are planning on using the functions.  The profile that I use can be found in Codeplex at Source Code -> Powershell -> Profile -> Modules -> Microsoft.PowerShell_profile.ps1.  Please use this as a reference for setting up your own profile.

To install the module, copy the D2DDynamics folder to the module folder for PowerShell.  On my Server 2012 server, the location is “C:\Windows\System32\WindowsPowerShell\v1.0\Modules”.  After the module is there, you should be able to open PowerShell and add it with the Import-Module cmdlet.

Future posts will include the steps necessary to create a custom build process using the module as well as more functions that can be used for deploying your build to other environments.  Please let me know what you think of the module and if you find any problems with using the functions in your environments.

Restore-AXDatabase custom PowerShell function

18 Thursday Jun 2015

Posted by in Powershell

≈ 2 Comments

Tags

, , , , , , , , ,

I think I have officially become a bi-weekly blog.  My current schedule isn’t going to be able to support a weekly blog right now.  The Restore-AXDatabase custom PowerShell function will restore a SQL Server database from a backup file.  This is used in my custom build process to reset the build environment back to a “vanilla” instance.

A development build environment needs to be reset to an instance that is missing all of your build code before starting the new build.  It is possible to delete the build models and recompile/resync everything to get back to “vanilla” but it is significantly faster to make a database backup of the AX database and model database without your code present and then restore these.  It was taking me 5-20 minutes to delete models and 90-150 minutes to recompile and resync everything.  It generally takes me 2-5 minutes to restore the 2 databases.

The Restore-AXDatabase function takes between 2-6 parameters:

  • ServerInstance (Server and SQL instance where the DB is located.  Defaults to the local default instance.)
  • AXDBName (Name of the database to be restored.)
  • BackupFilePath (The backup file to be restored.)
  • AdditionalSQLRestore (This defaults to the values that I use for SQL 2012.  I have added it as a parameter so it could be changed if necessary.)
  • TimeOut (The value defaults to 10 minutes but can be overridden if desired)
  • SMTPServer (SMTP server to use to send the email)
  • MailMsg (Net.Mail.MailMessage object used to send the email)
  • VariablePath (Path to a file used to default the parameters/variables)

This function can be found in Codeplex.  The steps of this function are:

  • Load the variables if a VariablePath parameter is used
  • Set up the SQL command
  • Restore the DB using Invoke-SqlCmd
  • Send the success/fail message

There is some setup that needs to be done to use this function.  The first thing necessary it to load the PowerShell module that contains it.  I wrestled with adding this to the function itself but decided against it.  The reasoning is that if you are using an earlier version of SQL Server than 2012, you need to load the SQL PowerShell Snap-ins vs loading the module.

LoadSQLModule

I have added 3 lines to my profile to accomplish this.  The first and third line are added to make up for a side effect of loading the SQLPS module, it changes your location in PowerShell.  The Push-Location cmdlet pops your location onto a stack and the Pop-Location cmdlet retrieves it.  This allows you to load the SQLPS module without changing your location.

The other step that you will need to do is to get your build environment cleaned up and create backups of the AX database and the model database.  You are going to want to have only the models that aren’t part of your build installed in AX.  In my case, this would be my SYS, SYP, ISV & ISP layers.  I only have a single custom model in my VAR layer that I build.  I compile the AOT, IL and sync the database to make sure everything is ready.  I then take a backup of both my AX database and my model database.  Now I can restore both databases and use my Clean-AXArtifacts custom Powershell function to clean up all of the compiled IL and when I start my AOS back up, I have a working “vanilla” instance of AX for my build.

Sync-AXDB custom PowerShell function

12 Sunday Apr 2015

Posted by in Powershell

≈ 3 Comments

Tags

, , , , , , , , ,

The Sync-AXDB custom PowerShell function will allow you to synchronize the AX DataDictionary from PowerShell.  If you compare this function/post against the Compile-AXCIL function/post, you will see that they are almost identical.  I had thought of creating a single Start-AXAutoRun function but eventually scrapped that idea in favor of simplicity for the user.  I may look at creating an internal function for the AX call at some point in the future to remove the code duplication.

The Sync-AXDB function takes between 1 and 7 parameters:

  • ConfigPath (Client configuration for the chosen AX environment)
  • LogFile (The value defaults to the temp folder but can be overridden if desired)
  • TimeOut (The value defaults to 2 hours but can be overridden if desired)
  • SMTPServer (SMTP server to use to send the email)
  • MailMsg (Net.Mail.MailMessage object used to send the email)
  • AXVersion (The AX version.  It defaults to 6.)
  • VariablePath (Path to a file used to default the parameters/variables)

I use the VariablePath parameter the same way that I do in the other functions I’ve posted that use it.  This function can be found in Codeplex.  The steps of this function are:

  • Load the variables if a VariablePath parameter is used
  • Get the AX environment info using Get-AXConfig
  • Get the Synchronize AX AutoRun xml using the Get-AXAutoRunXML function
  • Call the Start-AXAutoRun function to synchronize the database

This function should also only be run on the AOS server.  It has been tested using AX 2012 R2 CU7 but should also work for R3.  I have added the Version parameter to this function but it was initially missing from the Compile-AXCIL function/post.  I have gone back and added it to the earlier post as well to reflect the code change in TFS.