Recreating AX Environments / Moving Databases
Overview
On a frequent basis I am being requested to recreate / move AX databases between environments.
DEV to UAT. PROD to UAT. GOLD to LOCAL. PROD to LOCAL etc.
This is not officially supported by MS, they recommended that the DB’s never move because there are so many references to settings on a per environment basis that you are likely to cause problems. Hence we never ever move a DB to PROD, we can move from and to any other platform for testing and development, but never to PROD. In most cases, this is not a problem but there are circumstances that need to be considered to get the restored database functioning.
For example, I cloned the UAT DB onto the Developers machines, when they were adding code and compiling it, there was still references to the UAT AOS server which in turn caused the UAT environment to have wrong code installed on it and also the compilation caused the CPU and Disk to max out on UAT.
The following scenarios are where we may need to restore a Microsoft Dynamics AX database to a different environment:
- Testing a Microsoft Dynamics AX service pack, roll-up, or full version upgrade.
- Bringing Microsoft Dynamics AX 2012 data in house for testing or development.
- Restoring a copy of the production database into a test or development environment to work with the most recent changes.
Script Info
I have created a Powershell script to perform the action of moving AX Databases between environments in a safe manner ensuring all the relevant server name values are changed to reflect the new destination environment. It performs the following actions.
- Check for Local Admin Rights_
- Take new $Source AX Backup_
- Stop AOS on Target Environment_
- Backup $Target AX Databases_
- Restore $Source DB to $Target DB_
- Reconfigure $Target settings (i.e change server names and service accounts etc)_
- Delete XppIL Cache on $Target_
- Restart AOS on $Target_
- Couple of minute time-out to allow the service to fully start and the next steps complete successfully _
- Check Business Connector Install and Install if not present._
- If LOCAL or GOLD Desktop Devs are Admin and access is recreated_
- If DEV or CHDEV - Needs DEV Admin Group_
You can pass through the following Parameters to the script :
- Target - LOCAL, DEV, CHDEV, GOLD
- Source - DEV, UAT, PROD, GOLD, CHDEV
- RunHeadless - If this value is passed it runs silently
- If you pass through no Parameters it will prompt for a Source and Target Environment.
Examples of script usage :
- “Recreate_AX_Env.ps1 -Target LOCAL -Source PROD -RunHeadless”
- _”_Recreate_AX_Env.ps1 -Target CHDEV -Source UAT -RunHeadless”
If the script is called with no parameters it prompts for a Source and Target Environment. Dependant upon the Source, it must be ran as a SysAdmin of Source Environment in order to perform the action of promoting or demoting Admins for the Target Env.
Running Script
The script needs to be ran as Admin, so I have created a Batch File to call it with.Run the Batch file as Admin on the destination AOS you want to have the new DB’s.
Recreate_AX_Env.ps1 -Target LOCAL -Source PROD -RunHeadless
You will be presented with the following screen first to choose the Source Environment (not case sensitive) :
You will be presented with the following screen second to choose the Target Environment (not case sensitive) :
A final warning is presented before the process continues :
It roughly takes 30 - 40 minutes to run through the script depending upon performance and location of data.
1param(
2 [ValidateSet("local","dev","chdev","gold",ignoreCase = $true)]
3 [Parameter(Mandatory = $false)]
4 [string]$Target,
5 [ValidateSet("dev","uat","prod","gold","chdev",ignoreCase = $true)]
6 [Parameter(Mandatory = $false)]
7 [string]$Source,
8 [switch]$RunHeadless
9)
10
11################################################################
12# Recreate_AX_Env.ps1
13#
14# A powershell script for restoring and reconfiguring AX backups
15# to any AX Environment.
16#
17################################################################
18# Functions / Methods
19################################################################
20
21function Test-AdministratorPrivileges
22{
23 $identity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
24 $principal = New-Object System.Security.Principal.WindowsPrincipal ($identity)
25 $principal.IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)
26}
27
28function Reconfigure-AxConfiguration
29{
30
31 param(
32 [Parameter(Mandatory = $true)]
33 [string]$ComputerName,
34 [Parameter(Mandatory = $true)]
35 [string]$SqlServerInstance,
36 [Parameter(Mandatory = $true)]
37 [string]$DestTransDB,
38 [Parameter(Mandatory = $true)]
39 [string]$SourceTransDB,
40 [Parameter(Mandatory = $true)]
41 [string]$ReportServerHost,
42 [Parameter(Mandatory = $true)]
43 [string]$ServerURL,
44 [Parameter(Mandatory = $true)]
45 [string] $ManagerURL,
46 [Parameter(Mandatory = $true)]
47 [string] $RSInstance
48
49 )
50
51 process
52 {
53 Write-Host
54 Write-Host "Updating AX $target settings...."
55
56 Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlServerInstance -Query "
57 DECLARE @PartitionId bigint;
58
59 -- Get ID of the partition..
60 SELECT @PartitionId = RECID FROM [$DestTransDB].[dbo].[PARTITIONS] WHERE PARTITIONKEY='initial';
61
62 -- Drop Server GUID so that the Cache refreshes etc
63 UPDATE [$DestTransDB].[dbo].[SysSQMSettings] Set GlobalGUID = '{00000000-0000-0000-0000-000000000000}'
64
65 -- Reconfigure UAT to local..
66 UPDATE [$DestTransDB].[dbo].[SYSGLOBALCONFIGURATION] SET VALUE='http://${ComputerName}:80/DynamicsAX6HelpServer/HelpService.svc' WHERE NAME='HelpServerLocation';
67
68 TRUNCATE TABLE [$DestTransDB].[dbo].[SYSSERVERCONFIG];
69 TRUNCATE TABLE [$DestTransDB].[dbo].[SYSSERVERSESSIONS];
70 TRUNCATE TABLE [$DestTransDB].[dbo].[SYSCLIENTSESSIONS];
71
72 TRUNCATE TABLE [$DestTransDB].[dbo].[SRSSERVERS];
73 INSERT INTO [$DestTransDB].[dbo].[SRSSERVERS] (SERVERID, ISDEFAULTREPORTMODELSERVER, SERVERURL, ISDEFAULTREPORTLIBRARYSERVER, AXAPTAREPORTFOLDER, REPORTMANAGERURL, SERVERINSTANCE, AOSID, CONFIGURATIONID, ISSHAREPOINTINTEGRATED, RECVERSION, RECID)
74 VALUES ('$ReportServerHost', 0, 'http://$($ReportServerHost)/$($ServerURL)', 1, 'DynamicsAx', 'http://$($ReportServerHost)/$($ManagerURL)', '$RSInstance', '01@$($aosInstances)', '01@$($aosInstances)', 0, 1, 1 )
75
76 TRUNCATE TABLE [$DestTransDB].[dbo].[BIANALYSISSERVER];
77 INSERT INTO [$DestTransDB].[dbo].[BIANALYSISSERVER] (SERVERNAME, DESCRIPTION, ISVALID, ISDEFAULT, DEFAULTDATABASENAME, PARTITIONS, RECVERSION, RECID)
78 VALUES ('$ReportServerHost', 'Dynamics AX analysis server', 1, 1, 'Dynamics AX initial', @PartitionId, 1, 1);
79
80 TRUNCATE TABLE [$DestTransDB].[dbo].[BIANALYSISSERVICESDATABASE];
81 INSERT INTO [$DestTransDB].[dbo].[BIANALYSISSERVICESDATABASE] ([ANALYSISSERVICESDATABASENAME],[BIANALYSISSERVER],[ISDEFAULT],[LASTDEPLOYEDDATETIME],[LASTDEPLOYEDDATETIMETZID],[PARTITIONS],[RECVERSION],[RECID])
82 VALUES ('Dynamics AX initial', 1, 1, '01-01-1900', 37001, @PartitionId, 786535868, 5637144576);
83
84 UPDATE [$DestTransDB].[dbo].[SYSEMAILPARAMETERS] SET SMTPRELAYSERVERNAME='mail1.connect-distribution.net';
85
86 --TRUNCATE TABLE [$DestTransDB].[dbo].[EPWEBSITEPARAMETERS];
87
88 --INSERT INTO [$DestTransDB].[dbo].[EPWEBSITEPARAMETERS] ([COMPANYID], [INTERNALURL] ,[TYPE] ,[SITEID] ,[ANONYMOUSACCESS] ,[EXTERNALURL] ,[ENABLEENCRYPTION] ,[ENCRYPTIONEXPIRATIONINTERVAL] ,[IMAGERESIZE] ,[IMAGESIZESMALL] , [IMAGESIZELARGE] ,[IMAGERATIO] ,[COMPANYINDEPENDENT] ,[SITEINSTALLATIONTYPE] ,[USEDEFAULTREPORTSERVER] ,[PARTITIONINDEPENDENT] ,[PARTITIONKEY] ,[RECVERSION] ,[RECID])
89 --VALUES ('CDS', 'http://ax-uat-ent-01:93/sites/DynamicsAx', 2, 'F21351AC-5C38-4121-92B3-1D5743CB2B15', 0, 'http://ax-uat-ent-01:93/sites/DynamicsAx', 1, 1, 1, 75, 150, 1, 1, 1, 1, 1, 'initial', 1, '5637144576');
90
91 --TRUNCATE TABLE [$DestTransDB].[dbo].[AIFWEBSITES];
92 --INSERT INTO [$DestTransDB].[dbo].[AIFWEBSITES] (VIRTUALDIRECTORYSHARE, NAME, URL, RECVERSION, RECID)
93 --VALUES ('\\$(${ComputerName})\MicrosoftDynamicsAXAif60', '$(${ComputerName})-AXAifDEV-MicrosoftDynamicsAXAifDEV', 'http://$(${ComputerName}):92/MicrosoftDynamicsAXAiF60/', '999258545', '5637144576');
94
95 TRUNCATE TABLE [$DestTransDB].[dbo].[BICONFIGURATION];
96 INSERT INTO [$DestTransDB].[dbo].[BICONFIGURATION] ([PERFORMTRANSLATION] ,[CREATEROLES] ,[DATASOURCENAME] ,[CONNECTIONSTRING] ,[CREATECUBES] ,[ENABLELOGGING] ,[PROJECTNAME] ,[OPENGENERATEDPROJECT] ,[PROJECTFILENAME] ,
97 [CREATEDIMENSIONS] ,[UPDATECUBES] ,[OVERWRITETRANSLATIONS] ,[UPDATEDIMENSIONS] ,[UPDATEMEASUREGROUPS] ,[UPDATEDIMENSIONUSAGE] ,[UPDATEHIERARCHIES] ,[UPDATEFRAMEWORKDIMENSIONUSAGE] ,[OPENUPDATEDPROJECT] ,[DATASOURCETYPE] ,
98 [ENABLECURRENCYCONVERSION] ,[UPDATEDATEDIMENSIONS] ,[RECVERSION] ,[RECID])
99 VALUES ('1','0', 'Dynamics Database', 'Provider=SQLNCLI10.1;Data Source=$($SqlServerInstance);Integrated Security=SSPI;Initial Catalog=$($DestTransDB)', '0', '0', 'DynamicsAX', '0', 'C:\Users\K3ADMIN\AppData\Local\Temp\3\DynamicsAX\DynamicsAX.dwproj', '0', '0', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', '1290264436', '5637144576' )
100
101 --K3 Fix for Slow AX Client Startup ???
102 USE [$($DestTransDB)_Model];
103 IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = 'ParentIDX' )
104 BEGIN
105 CREATE NONCLUSTERED INDEX [ParentIDX] ON [$($DestTransDB)_Model].[dbo].[ModelElement] ([ParentId])
106 END
107 GO "
108
109
110 if (!($DestTransDB -eq $SourceTransDB))
111 {
112 Write-Host
113 Write-Host "Rename AX $target Database settings...."
114
115 Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlServerInstance -Query "
116
117 --Rename Restored DB's
118 GO
119 USE [$DestTransDB]
120 ALTER DATABASE [$DestTransDB] MODIFY FILE (NAME=N'$($SourceTransDB)', NEWNAME=N'$DestTransDB')
121 ALTER DATABASE [$DestTransDB] MODIFY FILE (NAME=N'$($SourceTransDB)_log', NEWNAME=N'$($DestTransDB)_log')
122 GO
123 USE [$($DestTransDB)_Model]
124 ALTER DATABASE [$($DestTransDB)_Model] MODIFY FILE (NAME=N'$($SourceTransDB)_Model', NEWNAME=N'$($DestTransDB)_Model')
125 ALTER DATABASE [$($DestTransDB)_Model] MODIFY FILE (NAME=N'$($SourceTransDB)_Model_log', NEWNAME=N'$($DestTransDB)_Model_log')"
126 }
127
128 if (!($Target -eq "DEV"))
129 {
130 Write-Host
131 Write-Host "Set AX $target Database to Simple Recovery Mode...."
132
133 Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlServerInstance -Query "
134
135 --Set DBs Simple Recovery Mode
136 GO
137 USE [$DestTransDB]
138 ALTER DATABASE [$DestTransDB] SET RECOVERY SIMPLE WITH NO_WAIT
139 GO
140 USE [$($DestTransDB)_Model]
141 ALTER DATABASE [$($DestTransDB)_Model] SET RECOVERY SIMPLE WITH NO_WAIT"
142
143 }
144
145 Write-Host
146 Write-Host "Shrink AX $target Databases...."
147
148 Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $SqlServerInstance -Query "
149
150 --Shrink DB's and Files
151 GO
152 USE
153 [$DestTransDB]
154 DBCC SHRINKDATABASE(N'$DestTransDB' )
155 GO
156 USE
157 [$DestTransDB]
158 DBCC SHRINKFILE (N'$DestTransDB' , 0, TRUNCATEONLY)
159 GO
160 USE
161 [$DestTransDB]
162 DBCC SHRINKFILE (N'$($DestTransDB)_log' , 0, TRUNCATEONLY)
163 GO
164 USE
165 [$($DestTransDB)_Model]
166 DBCC SHRINKDATABASE(N'$($DestTransDB)_Model' )
167 GO
168 USE
169 [$($DestTransDB)_Model]
170 DBCC SHRINKFILE (N'$($DestTransDB)_model' , 0, TRUNCATEONLY)
171 GO
172 USE
173 [$($DestTransDB)_Model]
174 DBCC SHRINKFILE (N'$($DestTransDB)_model_log' , 0, TRUNCATEONLY) "
175
176 if ($? -ne "True") {
177 throw "An error occurred whilst recreating the AX Settings $_"
178 }
179 }
180}
181
182function Restore-Database
183{
184
185 param(
186 [Parameter(Mandatory = $true)]
187 [string]$Source,
188 [Parameter(Mandatory = $true)]
189 [string]$Target,
190 [Parameter(Mandatory = $true)]
191 [string]$transDatabaseName,
192 [Parameter(Mandatory = $true)]
193 [string]$modelDatabaseName,
194 [Parameter(Mandatory = $true)]
195 [string]$StransDatabaseName,
196 [Parameter(Mandatory = $true)]
197 [string]$SmodelDatabaseName,
198 [Parameter(Mandatory = $true)]
199 [string]$dbServer,
200 [Parameter(Mandatory = $true)]
201 [string]$SdbServer,
202 [Parameter(Mandatory = $true)]
203 [string]$STransSource,
204 [Parameter(Mandatory = $true)]
205 [string]$SModelSource,
206 [Parameter(Mandatory = $true)]
207 [string]$DestData,
208 [Parameter(Mandatory = $true)]
209 [string]$DestLogs
210
211 )
212
213 Write-Host
214 Write-Host "Restoring $Source Transactional Database to $Target" -Fore Green -Back Red
215
216 Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $dbServer -Query "
217USE [master]
218ALTER DATABASE [$transDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
219RESTORE DATABASE [$transDatabaseName] FROM DISK = N'$STransSource' WITH FILE = 1, MOVE N'$StransDatabaseName' TO N'$($DestData)$($transDatabaseName).mdf', MOVE N'$($StransDatabaseName)_log' TO N'$($DestLogs)$($transDatabaseName)_log.ldf', NOUNLOAD, REPLACE, STATS = 5
220ALTER DATABASE [$transDatabaseName] SET MULTI_USER
221
222GO
223"
224
225 Write-Host
226 Write-Host "Restoring $Source ModelStore Database to $Target" -Fore Green -Back Red
227
228 Invoke-Sqlcmd -QueryTimeout 65535 -ServerInstance $dbServer -Query "
229USE [master]
230ALTER DATABASE [$modelDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
231RESTORE DATABASE [$modelDatabaseName] FROM DISK = N'$SModelSource' WITH FILE = 1, MOVE N'$SmodelDatabaseName' TO N'$($DestData)$($modelDatabaseName).mdf', MOVE N'$($SmodelDatabaseName)_log' TO N'$($DestLogs)$($modelDatabaseName)_log.ldf', NOUNLOAD, REPLACE, STATS = 5
232ALTER DATABASE [$modelDatabaseName] SET MULTI_USER
233
234GO
235"
236
237 if ($? -ne "True") {
238 throw "An error occurred whilst restoring to $Target AX"
239 }
240
241 else
242 { Write-Host
243 Write-Host "Both $Source Databases Restored successfully" -Fore Green -Back Red }
244
245}
246
247################################################################
248# Pre-Config Environment VAR population
249################################################################
250
251if (!($RunHeadless.IsPresent))
252{
253 # Step 1 : Check Admin Rights,
254 if (!(Test-AdministratorPrivileges))
255 {
256 Write-Warning -Message "###############################################"
257 Write-Warning -Message "# Please run this script with Admin Privileges"
258 Write-Warning -Message "###############################################"
259 Pause
260 return
261 }
262 #Menu to allow the user to choose which Environment to work with
263 Write-Host -Fore Green -Back Black
264 Write-Host -Fore Green -Back Black
265 Write-Host -Fore Green -Back Black
266 Write-Host -Fore Green -Back Black ******************************************************
267 Write-Host -Fore Green -Back Black ******************************************************
268 Write-Host -Fore Green -Back Black ********Please Choose the required Source*************
269 Write-Host -Fore Green -Back Black ******************************************************
270 Write-Host -Fore Green -Back Black *******This is the Env that you want to pull *********
271 Write-Host -Fore Green -Back Black ******************************************************
272 Write-Host -Fore Green -Back Black ******** CHDEV ***************************************
273 Write-Host -Fore Green -Back Black ******** DEV *****************************************
274 Write-Host -Fore Green -Back Black ******** UAT *****************************************
275 Write-Host -Fore Green -Back Black ******** GOLD ****************************************
276 Write-Host -Fore Green -Back Black ******** PROD ****************************************
277 Write-Host -Fore Green -Back Black ******************************************************
278 Write-Host -Fore Green -Back Black ******************************************************
279 Write-Host -Fore Green -Back Black
280 Write-Host -Fore Green -Back Black
281 Write-Host -Fore Green -Back Black
282 [ValidateSet("dev","uat","prod","gold","chdev",ignoreCase = $true)][Parameter(Mandatory = $false)] [string]$Source = Read-Host -Prompt 'Choose the Source Environment you want to pull'
283 $Source = $Source.ToUpper()
284 Write-Host -Fore Yellow "THE SOURCE IS $Source"
285 Pause
286 cls
287 Write-Host -Fore Green -Back Black
288 Write-Host -Fore Green -Back Black
289 Write-Host -Fore Green -Back Black
290 Write-Host -Fore Green -Back Black ******************************************************
291 Write-Host -Fore Green -Back Black ******************************************************
292 Write-Host -Fore Green -Back Black ********Please Choose the required Target*************
293 Write-Host -Fore Green -Back Black ******************************************************
294 Write-Host -Fore Green -Back Black *****This is the Env that you want to write to *******
295 Write-Host -Fore Green -Back Black ******************************************************
296 Write-Host -Fore Green -Back Black ******** LOCAL ***************************************
297 Write-Host -Fore Green -Back Black ******** DEV *****************************************
298 Write-Host -Fore Green -Back Black ******** CHDEV ***************************************
299 Write-Host -Fore Green -Back Black ******** GOLD ****************************************
300 Write-Host -Fore Green -Back Black ******************************************************
301 Write-Host -Fore Green -Back Black ******************************************************
302 Write-Host -Fore Green -Back Black
303 Write-Host -Fore Green -Back Black
304 Write-Host -Fore Green -Back Black
305 [ValidateSet("local","dev","chdev","gold",ignoreCase = $true)][Parameter(Mandatory = $false)] [string]$Target = Read-Host -Prompt 'Choose the Target Environment to ReCreate'
306 $target = $Target.ToUpper()
307 Write-Host -Fore Yellow "THE TARGET IS $Target"
308 Pause
309 cls
310 Write-Host -Fore White -Back Red
311 Write-Host -Fore White -Back Red
312 Write-Host -Fore White -Back Red
313 Write-Host -Fore White -Back Red ******************************************************
314 Write-Host -Fore White -Back Red ******************************************************
315 Write-Host -Fore White -Back Red ********** WARNING YOU ARE ABOUT TO WIPE *************
316 Write-Host -Fore White -Back Red "************ THE" -NoNewline
317 Write-Host -Fore Yellow -Back Red " $Target " -NoNewline
318 Write-Host -Fore White -Back Red "ENVIRONMENT *******************"
319 Write-Host -Fore White -Back Red ******************************************************
320 Write-Host -Fore White -Back Red ***********WARNING*****WARNING******WARNING **********
321 Write-Host -Fore White -Back Red ******************************************************
322 Write-Host -Fore White -Back Red ********** -NoNewline
323 Write-Host -Fore Yellow -Back Red " $Target " -NoNewline
324 Write-Host -Fore White -Back Red IS ABOUT TO BE RECREATED *************
325 Write-Host -Fore White -Back Red ********** FROM THE -NoNewline
326 Write-Host -Fore Yellow -Back Red " $Source " -NoNewline
327 Write-Host -Fore White -Back Red ENVIRONMENT ***************
328 Write-Host -Fore White -Back Red ******************************************************
329 Write-Host -Fore White -Back Red ******************************************************
330 Write-Host -Fore White -Back Red
331 Write-Host -Fore White -Back Red
332 Write-Host -Fore White -Back Red
333 Pause
334}
335
336################################################################
337# Configuration
338################################################################
339$Source = $Source.ToUpper()
340$target = $Target.ToUpper()
341
342$environments = @{
343 "LOCAL" = @{
344 "aosServers" = @( "$ENV:Computername")
345 "aosServiceUser" = "CONNECT\AXDEVAOS"
346 "enterprisePortalUrl" = ""
347 "reportingServers" = "$ENV:Computername"
348 "ServerURL" = "ReportServer"
349 "ManagerURL" = "Reports"
350 "RSInstance" = "MSSQLSERVER"
351 "databaseServer" = "$ENV:Computername\AXDEV"
352 "modelDatabaseName" = "MicrosoftDynamicsAX_Model"
353 "transDatabaseName" = "MicrosoftDynamicsAX"
354 "configuration" = "build-client.axc"
355 "serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin"
356 "DestData" = "C:\Program Files\Microsoft SQL Server\MSSQL12.AXDEV\MSSQL\DATA\"
357 "DestLogs" = "C:\Program Files\Microsoft SQL Server\MSSQL12.AXDEV\MSSQL\DATA\"
358 "DestBackup" = "C:\Source\"
359 }
360 "DEV" = @{
361 "aosServers" = @( "AX-DEV-AOS-01")
362 "aosServiceUser" = "CONNECT\AXAOSACC"
363 "enterprisePortalUrl" = "http://ax-uat-ent-01:93/sites/DynamicsAx"
364 "reportingServers" = "AX-DEV-RS-01"
365 "ServerURL" = "ReportServer"
366 "ManagerURL" = "Reports"
367 "RSInstance" = "MSSQLSERVER"
368 "databaseServer" = @( "AX-UAT-SQL-01\SQL_AX_DEV1")
369 "modelDatabaseName" = "AX63_CDS_DEV_model"
370 "transDatabaseName" = "AX63_CDS_DEV"
371 "configuration" = "DEV-client.axc"
372 "serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\AX63_CDS_DEV\bin"
373 "TransSource" = "\\AX-UAT-SQL-01\DEV-Backup$\AX63_CDS_DEV.bak"
374 "ModelSource" = "\\AX-UAT-SQL-01\DEV-Backup$\AX63_CDS_DEV_model.bak"
375 "DestData" = "D:\Microsoft SQL Server\MSSQL12.SQL_AX_DEV1\MSSQL\DATA\"
376 "DestLogs" = "F:\Microsoft SQL Server\MSSQL12.SQL_AX_DEV1\MSSQL\DATA\"
377 "DestBackup" = "E:\DEV-Backup\"
378 }
379 "UAT" = @{
380 "aosServers" = @( "AX-UAT-AOS-01","AX-UAT-BAT-01")
381 "aosServiceUser" = "CONNECT\AXAOSACC"
382 "enterprisePortalUrl" = "http://ax-uat-ent-01:83/sites/DynamicsAx"
383 "reportingServers" = "AX-UAT-RS-01"
384 "ServerURL" = "ReportServer"
385 "ManagerURL" = "Reports"
386 "RSInstance" = "MSSQLSERVER"
387 "databaseServer" = @( "AX-UAT-SQL-01\SQL_AX_UAT1")
388 "modelDatabaseName" = "AX63_CDS_UAT_model"
389 "transDatabaseName" = "AX63_CDS_UAT"
390 "configuration" = "UAT-client.axc"
391 "serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\AX63_CDS_UAT\bin"
392 "TransSource" = "\\AX-UAT-SQL-01\UAT-Backup$\AX63_CDS_UAT.bak"
393 "ModelSource" = "\\AX-UAT-SQL-01\UAT-Backup$\AX63_CDS_UAT_model.bak"
394 "DestData" = "D:\Microsoft SQL Server\MSSQL12.SQL_AX_UAT1\MSSQL\DATA\"
395 "DestLogs" = "F:\Microsoft SQL Server\MSSQL12.SQL_AX_UAT1\MSSQL\DATA\"
396 "DestBackup" = "E:\UAT-Backup\"
397 }
398 "PROD" = @{
399 "aosServers" = @( "AX-AOS-01","AX-AOS-02","AX-AOS-03","AX-AOS-04","AX-AOS-BAT-01")
400 "aosServiceUser" = "CONNECT\AXAOSACC"
401 "enterprisePortalUrl" = "http://ax-ent-01:83/sites/DynamicsAx/"
402 "reportingServers" = "AX-RS-01"
403 "ServerURL" = "ReportServer"
404 "ManagerURL" = "Reports"
405 "RSInstance" = "MSSQLSERVER"
406 "databaseServer" = @( "AX-SQL-00\SQL_AX1")
407 "modelDatabaseName" = "AX63_CDS_PROD_model"
408 "transDatabaseName" = "AX63_CDS_PROD"
409 "configuration" = "production.axc"
410 "serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\AX63_CDS_PROD\bin"
411 "TransSource" = "\\AX-SQL-01\PROD-Backup$\AX63_CDS_PROD.bak"
412 "ModelSource" = "\\AX-SQL-01\PROD-Backup$\AX63_CDS_PROD_model.bak"
413 "DestData" = "D:\MSSQL12.SQL_AX1.Data\"
414 "DestLogs" = "F:\MSSQL12.SQL_AX1.Logs\"
415 "DestBackup" = "E:\PROD-Backup\"
416 }
417 "GOLD" = @{
418 "aosServers" = @( "CH-WV-AX-GOLD-T")
419 "aosServiceUser" = "CONNECT\AXDEVAOS"
420 "enterprisePortalUrl" = "http://ax-uat-ent-01:93/sites/DynamicsAx"
421 "reportingServers" = "CH-WV-AX-GOLD-T"
422 "ServerURL" = "ReportServer"
423 "ManagerURL" = "Reports"
424 "RSInstance" = "MSSQLSERVER"
425 "databaseServer" = @( "CH-WV-AX-GOLD-T\AXDEV")
426 "modelDatabaseName" = "MicrosoftDynamicsAX_Model"
427 "transDatabaseName" = "MicrosoftDynamicsAX"
428 "configuration" = "AXGOLD.axc"
429 "serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin"
430 "TransSource" = "\\CH-WV-AX-GOLD-T\GOLD-Backup$\MicrosoftDynamicsAX.bak"
431 "ModelSource" = "\\CH-WV-AX-GOLD-T\GOLD-Backup$\MicrosoftDynamicsAX_Model.bak"
432 "DestData" = "C:\Program Files\Microsoft SQL Server\MSSQL12.AXDEV\MSSQL\DATA\"
433 "DestLogs" = "C:\Program Files\Microsoft SQL Server\MSSQL12.AXDEV\MSSQL\DATA\"
434 "DestBackup" = "C:\GOLD-Backup\"
435 }
436 "CHDEV" = @{
437 "aosServers" = @( "CH-AXDEV-AOS-01")
438 "aosServiceUser" = "CONNECT\AXDEVAOS"
439 "enterprisePortalUrl" = "http://ax-uat-ent-01:93/sites/DynamicsAx"
440 "reportingServers" = "CH-AXDEV-RS-01"
441 "ServerURL" = "ReportServer_REPORTING"
442 "ManagerURL" = "Reports_REPORTING"
443 "RSInstance" = "REPORTING"
444 "databaseServer" = @( "CH-WV-SQL-01\AXDEV")
445 "modelDatabaseName" = "MicrosoftDynamicsAX_Model"
446 "transDatabaseName" = "MicrosoftDynamicsAX"
447 "configuration" = "AX_CH_DEV.axc"
448 "serverBinDir" = "C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin"
449 "TransSource" = "\\CH-WV-SQL-01\CHDEV$\MicrosoftDynamicsAX.bak"
450 "ModelSource" = "\\CH-WV-SQL-01\CHDEV$\MicrosoftDynamicsAX_Model.bak"
451 "DestData" = "E:\MSSQL12.AXDEV\MSSQL\DATA\"
452 "DestLogs" = "F:\MSSQL12.AXDEV\MSSQL\DATA\"
453 "DestBackup" = "C:\Source\CHDEV\"
454 }
455};
456
457# Destination Configuration options
458$dest = $environments[$Target.ToUpperInvariant()]
459$aosInstances = $dest.aosServers
460[string]$dbServer = $dest.databaseServer
461$aosUser = $dest.aosServiceUser
462$reportingServers = $dest.reportingServers
463$enterpriseUrl = $dest.enterprisePortalUrl
464$modelDatabaseName = $dest.modelDatabaseName
465$transDatabaseName = $dest.transDatabaseName
466$serverBinDir = $dest.serverBinDir
467$DestData = $dest.DestData
468$DestLogs = $dest.DestLogs
469$DestBackup = $dest.DestBackup
470$ServerURL = $dest.ServerURL
471$ManagerURL = $dest.ManagerURL
472$RSInstance = $dest.RSInstance
473
474# Source Configuration options
475$src = $environments[$Source.ToUpperInvariant()]
476$SaosInstances = $src.aosServers
477[string]$SdbServer = $src.databaseServer
478$SaosUser = $src.aosServiceUser
479$SreportingServers = $src.reportingServers
480$SenterpriseUrl = $src.enterprisePortalUrl
481$SmodelDatabaseName = $src.modelDatabaseName
482$StransDatabaseName = $src.transDatabaseName
483$SserverBinDir = $src.serverBinDir
484$STransSource = $src.TransSource
485$SModelSource = $src.ModelSource
486$SDestBackup = $src.DestBackup
487
488# Common Configuration options
489$axServerInstallPath = "C:\Program Files\Microsoft Dynamics AX\60\Server\$transDatabaseName\bin"
490
491##################################################################
492# Importing Required Modules
493##################################################################
494
495Import-Module sqlps –DisableNameChecking
496Import-Module "C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Modules\AXUtilLib.Powershell\AXUtilLib.PowerShell.dll"
497Import-Module "C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Modules\Microsoft.Dynamics.AX.Framework.Management\Microsoft.Dynamics.AX.Framework.Management.dll"
498."C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Microsoft.Dynamics.ManagementUtilities.ps1"
499
500##################################################################
501# Main Application Starts Running Here
502##################################################################
503cls
504Write-Host "*************************************************************************"
505Write-Host " Recreate $Target AX from $Source AX"
506Write-Host "*************************************************************************"
507Write-Host
508Write-Host "*************************************************************************"
509Write-Host "Environment Recreation Settings"
510Write-Host "*************************************************************************"
511Write-Host "TARGET : $Target"
512Write-Host "DB Server : $dbServer"
513Write-Host "Model DB Name : $modelDatabaseName"
514Write-Host "Trans DB Name : $transDatabaseName"
515Write-Host "SOURCE : $Source"
516Write-Host "DB Server : $SdbServer"
517Write-Host "Model DB Name : $SmodelDatabaseName"
518Write-Host "Trans DB Name : $StransDatabaseName"
519Write-Host "Trans Source DB : $STransSource"
520Write-Host "Model Source DB : $SModelSource"
521Write-Host "*************************************************************************"
522try
523{
524 # Step 1 : Check Admin Rights,
525 if (!(Test-AdministratorPrivileges))
526 {
527 Write-Host -ForegroundColor Red "Please run this script with admin privileges"
528 return
529 }
530
531 Push-Location
532
533 # Step 2 : Take new $Source AX Backup
534 Write-Host "Starting $Source AX Transactional Database Backup"
535 Backup-SqlDatabase -CompressionOption On -ServerInstance $SdbServer -Database $StransDatabaseName -BackupFile "$($SDestBackup)$($STransDatabaseName).bak" -Initialize
536 Write-Host "Starting $Source AX ModelStore Database Backup"
537 Backup-SqlDatabase -CompressionOption On -ServerInstance $SdbServer -Database $SModelDatabaseName -BackupFile "$($SDestBackup)$($SModelDatabaseName).bak" -Initialize
538
539 # Step 3 : Stop AOS
540 Write-Host "Stopping AOS Service on Destination!"
541 Stop-Service -Name 'AOS60$01'
542
543 # Step 4 : Backup $Target AX Databases
544 Write-Host "Starting $Target AX Transactional Database Backup"
545 Backup-SqlDatabase -ServerInstance $dbServer -Database $transDatabaseName -BackupFile "$($DestBackup)$($TransDatabaseName).bak" -CompressionOption On -Initialize
546 Write-Host "Starting $Target AX ModelStore Database Backup"
547 Backup-SqlDatabase -ServerInstance $dbServer -Database $ModelDatabaseName -BackupFile "$($DestBackup)$($ModelDatabaseName).bak" -CompressionOption On -Initialize
548
549 # Step 5: Restore(!)
550 Write-Host "Restoring $Source AX Database to $Target AX!!!!"
551 Restore-Database -Source $Source -Target $Target -TransDatabaseName $transDatabaseName -ModelDatabaseName $modelDatabaseName -StransDatabaseName $StransDatabaseName -SModelDatabaseName $SmodelDatabaseName -dbServer $dbServer -SdbServer $SdbServer -SModelSource $SModelSource -STransSource $STransSource -DestData $DestData -DestLogs $DestLogs
552
553 # Step 6: Reconfigure AX Target settings
554 Write-Host "Reconfiguring AX $Target Settings"
555 Reconfigure-AxConfiguration -ComputerName $ENV:Computername -SqlServerInstance $dbServer -DestTransDB $transDatabaseName -SourceTransDB $StransDatabaseName -ReportServerHost $reportingServers -ServerURL $ServerURL -ManagerURL $ManagerURL -RSInstance $RSInstance
556
557 # Step 7 : Blow away XPIL etc...
558 Write-Host "Clearing $Target XPPIL cache"
559 if (Test-Path -Path "$axServerInstallPath\XPPIL") {
560 Remove-Item -Path "$axServerInstallPath\XPPIL" -Recurse
561 }
562
563 # Step 8 : Restart AOS on Destination
564 Write-Host "Starting AOS Service on Destination!"
565 Start-Service -Name 'AOS60$01'
566
567 # Step 8.1 : Couple of minute timeout to allow the service to fully start and the next steps complete successfully
568 Write-Host "Waiting 2 Minutes for Service to Fully Start up"
569 Start-Sleep -seconds 120
570
571
572 # Step 9 : Check Business Connector Install and Install if not present.
573 if (!(Test-Path "C:\Program Files\Microsoft Dynamics AX\60\BusinessConnector\Bin\Microsoft.Dynamics.BusinessConnectorNet.dll"))
574 { Write-Host "Installing MS Dynamics AX 2012 R3 Business Connector - Please Wait....."
575 Start-Process "\\CH-WV-FS-01\AXDev$\Source\AX_2012_R3_Installer\setup.exe" ParmFile="\\CH-WV-FS-01\AXDev$\Source\Scripts\SQLImageComplete-1\AXBusinessConnector.txt" -NoNewWindow -Wait
576 }
577
578 # Step 10 : If LOCAL or GOLD or CHDEV Desktop Devs are Admin
579 if (($Target -eq "LOCAL" -or $Target -eq "GOLD"-or $Target -eq "CHDEV") -and (!($Source -eq "GOLD" -or $Source -eq "CHDEV")))
580 { Write-Host "Recreating Desktop DEV Sys Admins!"
581 New-AXUser -AccountType WindowsGroup -AXUserId DDEVADM -UserName "AX DesktopDEV - SysAdmins" -UserDomain CONNECT -Company CDS
582 Add-AXSecurityRoleMember -AOTName "-SYSADMIN-" -AXUserId DDEVADM
583 }
584
585 # Step 11 : If DEV - Needs DEV Admin Group
586 if ($Target -eq "DEV")
587 { Write-Host "Enabling DEV RDS Access"
588 New-AXUser -AccountType WindowsGroup -AXUserId ADAXDEV -UserName "AX RDS DEV Access" -UserDomain CONNECT -Company CDS
589 Add-AXSecurityRoleMember -AOTName "SystemUser" -AXUserId ADAXDEV
590 Add-AXSecurityRoleMember -AOTName "HCMEmployee" -AXUserId ADAXDEV
591 Write-Host "Recreating DEV Sys Admins!"
592 New-AXUser -AccountType WindowsGroup -AXUserId DEVADM -UserName "AX DEV - SysAdmins" -UserDomain CONNECT -Company CDS
593 Add-AXSecurityRoleMember -AOTName "-SYSADMIN-" -AXUserId DEVADM
594 }
595
596 if (!($RunHeadless.IsPresent))
597 { Pause }
598}
599catch
600{
601 $exception = $_.Exception | Format-List -Force | Out-String
602 Write-Host $exception
603 if (!($RunHeadless.IsPresent))
604 { Pause }
605}