Setting up our NAV 2013 R2 Developement with PowerShell

I just finished my install on databases and service to our developement server.  When creating several databases and several instances I wanted to use PowerShell.

I based the setup on the demo database backup and used PowerShell ISE as an Administrator to do the setup.

First step is to set up parameters.

#Change Id to match the solution type
$Id = ‘GL’
#Change ClientPort to available port
$ClientPort = 9213

Then I run the script.

[code]#Change Id to match the solution type
$Id = ‘GL’
#Change ClientPort to available port
$ClientPort = 9213
#Database Server Name
$servername = ‘localhost’
#Location of Demo Database Backup File
$backupFile = ‘T:\Demo Database NAV (7-1).bak’
#New locations for Database Files
$DataOrigName = ‘Demo Database NAV (7-1)_Data’
$DataDestName = ‘M:\SQLdata\mdf\NAV71_’ + $Id + ‘.mdf’
$LogOrigName = ‘Demo Database NAV (7-1)_Log’
$LogDestName = ‘L:\SQLdata\ldf\NAV71_’ + $Id + ‘.ldf’
#New Database Name
$DatabaseName = ‘NAV71_’ + $Id
#New Service Instance Name
$ServiceInstanceName = ‘NAV71_’ + $Id
#New Company Name
$NewCompanyName = ‘CRONUS ‘ + $Id + ‘ 2013 R2’
#Other Ports to use
$SoapPort = $ClientPort + 1
$ODataPort = $SoapPort + 1
$MgtPort = $ODataPort + 1
#Database and Table with the Developement License
$CopyLicenseFromDB = ‘NAV71_AdvaniaStarter’
$tblName = ‘$ndo$dbproperty’

#Load the DLLs if not using SQLPS
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SmoExtended’) | out-null

#load server, backup, and restore objects
$server = new-object("Microsoft.SqlServer.Management.Smo.Server") $servername
$dbRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")

#settings for the restore
$dbRestore.Action = "Database"
$dbRestore.NoRecovery = $false;
$dbRestore.ReplaceDatabase = $false;
$dbRestorePercentCompleteNotification = 5;
$dbRestore.Devices.AddDevice($backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

#get the db name
$dbRestoreDetails = $dbRestore.ReadBackupHeader($server)

#print database name
"Database Name from Backup File : " + $dbRestoreDetails.Rows[0]["DatabaseName"]

#give a new database name
$dbRestore.Database = $DatabaseName

#specify new data and log files (mdf and ldf)
$dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")

#set file names; use the default database directory
$dbRestoreFile.LogicalFileName = $DataOrigName
$dbRestoreFile.PhysicalFileName = $DataDestName
$dbRestoreLog.LogicalFileName = $LogOrigName
$dbRestoreLog.PhysicalFileName = $LogDestName
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)

#execute the restore!
$dbRestore.SqlRestore($server)
write-host "Restore of " $DatabaseName "Complete"

$database = $server.Databases[$DatabaseName]
# grant access to database
$name = ‘NT AUTHORITY\NETWORK SERVICE’
$user = new-object (‘Microsoft.SqlServer.Management.Smo.User’) $database, $name
$user.Login = ‘NT AUTHORITY\NETWORK SERVICE’
$user.DefaultSchema = ‘dbo’
$user.Create()

# add role membership
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$servername;Integrated Security=SSPI;Initial Catalog=$DatabaseName");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N’db_owner’, @membername = N’NT AUTHORITY\NETWORK SERVICE’"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()

# Copy License
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$servername;Integrated Security=SSPI;Initial Catalog=master");
$cn.Open()
$q = "UPDATE [$DatabaseName].[dbo].[$tblName] SET [license] = (SELECT [license] FROM [$CopyLicenseFromDB].[dbo].[$tblName])"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()

New-NAVServerInstance -ServerInstance $ServiceInstanceName -ClientServicesCredentialType Windows -ClientServicesPort $ClientPort -DatabaseName $DatabaseName -DatabaseServer Localhost -ManagementServicesPort $MgtPort -SOAPServicesPort $SoapPort -ODataServicesPort $ODataPort
Set-NAVServerInstance -ServerInstance $ServiceInstanceName -Start
Rename-NAVCompany -ServerInstance $ServiceInstanceName -CompanyName ‘CRONUS Ísland hf.’ -NewCompanyName $NewCompanyName
Get-NAVCompany -ServerInstance $ServiceInstanceName

# give developers SUPER access to the database
$grpNames = (‘sec_Dynamics_NAV_Dev1’, ‘sec_Dynamics_NAV_Dev2′)
foreach ($grp in $grpNames)
{
Get-ADGroupMember -Identity $grp -Server "172.16.1.2" | foreach {
New-NAVServerUser -ServerInstance $ServiceInstanceName -Sid $($_.SID) -FullName $($_.name) -LicenseType Full -State Enabled
New-NAVServerUserPermissionSet -ServerInstance $ServiceInstanceName -Sid $($_.SID) -PermissionSetId "SUPER"
}

# add role membership
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$servername;Integrated Security=SSPI;Initial Catalog=$DatabaseName");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N’db_owner’, @membername = N’" + "SKYRR\" + $grp + "’"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()
}

[/code]

First step is to initialize all variables.

  • Set variable for the location of the Demo Database Backup File
  • Set variables for new location of Database Files
  • Set variable for new Database Name
  • Set variable for new Service Instance Name
  • Set variable for new Company Name

Next step is to restore the database.

  • Setup SMO environment
  • Setup the restore
  • Get current Database Name
  • Set new name for the Database
  • Set new locations for the Database Files
  • Execute Restore

The user running the NAV Server Instance will need to have access to the new database.

  • Create new user for  ‘NT AUTHORITY\NETWORK SERVICE’
  • Adds role ‘do_owner’ to user ‘NT AUTHORITY\NETWORK SERVICE’

Before starting the NAV Server Instance I will copy the developement license to the new database

  • Setup SQL connection to the master database
  • Prepare SQL statement to copy the license from another database
  • Execute SQL statement

New NAV Server Instance is created and started.  The existing company is renamed to the new company.

Use Active Directory to find all users in the developement group and add them as NAV users and give them SUPER permission.  The groups are also given ‘db_owner’ permission for the database to be able to run Developement Environment.

If the Active Directory PowerShell commands are not installed just activate that feature or look here for further details.

AddADPowerShell

Developers Speed Bump in NAV 2013 R2

NAV 2013 R2 is now multi tenant.  That means changes for the developer but not for the user.  This means that the application can be in one database and the company data in another.  One application can contain multiple companies in multiple databases.  This means that some of the administration tasks in the Developement Environment (old classic client) have been removed.

speedbump_street

The whole File->Company section is moved into the Windows Client.  In the Windows Client the user can now create, delete and copy companies.  The Copy Company function is new and easy to use.  The permissions for the database table Company determines what the user is allowed to do, meaning that if a user has the permission to delete a record in the table Company that user will be able to delete the whole company and all its data.

Take a closer look at what this means on Mark’s new website.

Since the data can now reside in multiple databases and the Developement Environment only connects to the application database the Backup and Restore feature has been removed.  No more FBK files.  You will now use SQL or PowerShell to do backup and restore.  Take a look at Waldo’s website for details.  Microsoft is planning to supply a new and easy way for Backup and Restore as Mark has blogged about.

Both Mark and Kamil have supplied a scripted way to copy data from one company to another using SQL commands.  I published a NAV Report that created SQL commands to copy data.  This was prior to NAV 2013 R2 so I decided to update that report and republish here.  Some of the update was inspired by Kamil’s work – thanks Kamil.

CopyCompanyScript

First create an empty company and start this report.  The destination is based on the company and database running the Report.  Features are;

  • The user can lookup databases and companies in the request page
  • Uses Table Information to loop through tables.  If selected, only the company tables will be added to the script.  This is useful if using multi tenant
  • It is possible to create a script for a limited number of tables by filtering on the Table No. field
  • Checks fields for AutoIncrement property and adds required script lines
  • Checks if a table exists in the source database before adding it to the script
  • Checks if a field exists in the source table before adding it to the script
  • Uses “Convert identifiers” from both source and destination database to create script lines

I also use this to copy data from a live database to a developement database and between versions of NAV.  I hope this will help you over some of the speed bumps to save time.

Create Copy Script

Seattle testdrive

Came here to Seattle last Thursday on a holiday with my family. Nice practise for the MVP Global Summit next month.

It will be exciting to visit Microsoft headquarters for the first time and see what is on the horizon. Will see some of you there…

Unexpected downtime

The web site is up again after an unexpected downtime.  I lost the electricity in my home town for a short time and at the same time I was driving south to Reykjavík for work.  I needed my wife to turn on the web server computer in her launch break and that is the first time she has to do that.  This is the first downtime I have had on my web site this year.

But, you know what ?  Being a Microsoft MVP means I get benefits on Windows Azure and can move my web site to a place that will never fail.  I already moved my @dynamics.is mail to outlook.com.

 

 

Creating a desktop shortcut for Dynamics NAV 2013 (R2)

If you are a user with multiple companies in your NAV database, or even with multiple databases you might be used to switching between companies or servers.

I  have been asked to create desktop shortcuts for the users.  What if the user is acting as a sales person in one company and as an accountant in another.  Then there is this confirmation dialog that always requires us to approve the server connection.  This is a process that takes time.

So, why don’t we let the user do this by him self ?

I wanted to so I created the code to make this happen.  First I needed to add a function to Codeunit 9500, Debugger Management.

[code] PROCEDURE GetConfigDetails@50000(VAR ServerComputerName@50002 : Text;VAR ServerInstance@50001 : Text;VAR ServerPort@50000 : Text;VAR ConfigFileName@50003 : Text);
BEGIN
IF ISNULL(Config) THEN
Config := Config.Instance;
ServerComputerName := Config.GetStringSetting(‘Server’);
ServerInstance := Config.GetStringSetting(‘ServerInstance’);
ServerPort := Config.GetStringSetting(‘ClientServicesPort’);
IF Config.Configuration.HasFile THEN
ConfigFileName := Config.Configuration.FilePath;
END;

[/code]

and then a small Codeunit to do the magic.  And guess what, no confirmation dialogs.

[code]OBJECT Codeunit 50001 Create Desktop Shortcut
{
OBJECT-PROPERTIES
{
Date=10.10.13;
Time=15:23:43;
Modified=Yes;
Version List=Dynamics.is;
}
PROPERTIES
{
OnRun=VAR
SpecialFolder@50006 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Environment+SpecialFolder" RUNONCLIENT;
Environment@50007 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Environment" RUNONCLIENT;
httpUtility@50001 : DotNet "’System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.Web.HttpUtility";
UrlString@50005 : Text;
ServerComputerName@50004 : Text;
ServerInstance@50003 : Text;
ServerPort@50002 : Text;
ConfigFileName@50000 : Text;
Arguments@50008 : Text;
LinkName@50009 : Text;
BEGIN
DebugMgt.GetConfigDetails(ServerComputerName,ServerInstance,ServerPort,ConfigFileName);
UrlString := STRSUBSTNO(‘DynamicsNAV://%1:%2/%3/%4/’,ServerComputerName,ServerPort,ServerInstance,httpUtility.UrlPathEncode(COMPANYNAME));

CREATE(WshShell,TRUE,TRUE);
LinkName := Environment.GetFolderPath(SpecialFolder.DesktopDirectory) + ‘\’ + COMPANYNAME;
IF ConfigFileName <> ” THEN
Arguments := STRSUBSTNO(‘ -settings:"%1" %2’,ConfigFileName,UrlString)
ELSE
Arguments := STRSUBSTNO(‘ -protocolhandler "%1"’,UrlString);

ActiveSession.SETRANGE("Session ID",SESSIONID);
ActiveSession.FINDFIRST;
Personalization.GET(ActiveSession."User SID");
IF Personalization."Language ID" <> 0 THEN
Arguments := STRSUBSTNO(‘ -language:%1 ‘,Personalization."Language ID") + Arguments;
IF Personalization."Profile ID" <> ” THEN BEGIN
Profile.GET(Personalization."Profile ID");
Arguments := STRSUBSTNO(‘ -profile:"%1" ‘,Personalization."Profile ID") + Arguments;
LinkName := LinkName + ‘ ‘ + Profile.Description;
END;

WshShortcut := WshShell.CreateShortcut(LinkName + ‘.lnk’);
WshShortcut.TargetPath := GetClientFileName;
WshShortcut.Arguments := Arguments;
WshShortcut.Description := COMPANYNAME;
WshShortcut.WorkingDirectory := Path.GetDirectoryName(GetClientFileName);
WshShortcut.Save;
CLEAR(WshShortcut);
CLEAR(WshShell);
END;

}
CODE
{
VAR
WshShortcut@50005 : Automation "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{A548B8E4-51D5-4661-8824-DAA1D893DFB2}:’Windows Script Host Object Model’.WshShortcut";
WshShell@50000 : Automation "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{72C24DD5-D70A-438B-8A42-98424B88AFB8}:’Windows Script Host Object Model’.WshShell";
Path@50001 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.Path" RUNONCLIENT;
Personalization@50003 : Record 2000000073;
ActiveSession@50004 : Record 2000000110;
Profile@50006 : Record 2000000072;
DebugMgt@50002 : Codeunit 9500;

PROCEDURE GetClientFileName@50018() ClientFileName : Text;
VAR
Assembly@50006 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Reflection.Assembly" RUNONCLIENT;
BEGIN
Assembly := Assembly.GetCallingAssembly;
ClientFileName := Path.GetDirectoryName(Assembly.Location) + ‘\Microsoft.Dynamics.Nav.Client.exe’;
END;

BEGIN
END.
}
}

[/code]

By running this Codeunit a shortcut will be created on the user desktop. The shortcut name will be the company name and the profile description. I guess it is not to hard to code the making of shortcuts for the developement environment but I leave that to you.

Create Desktop Shortcut

NAV 2013 R2 is available for download in 20 localized versions

Last Monday Microsoft announced the release of a new major version of Dynamics NAV.

On the product download page I am happy to see that my IS localized version is available.  It is nice to see that and a big change from the last release where the IS version was not available until May 1st this year, seven months after the global release date.

NAV2013R2_IS

A PDF document is supplied with highlighted new features and download links for all versions.

Microsoft Dynamics NAV 2013 R2 download links
AU-Australia Download
AT-Austria Download
BE-Belgium Download
CA-Canada Download
DK-Denmark Download
FI-Finland Download
FR-France Download
DE-Germany Download
IS – Iceland Download
IN-India Download
IT-Italy Download
MX-Mexico Download
NL-Netherlands Download
NZ-New Zealand Download
NO-Norway Download
ES-Spain Download
SE-Sweden Download
CH-Switzerland Download
GB-Great Britain Download
US-United States Download
W1 Download

MVP Award

mvp

Dear
Gunnar Gestsson,

Congratulations! We are pleased to present you with the 2013 Microsoft® MVP
Award! This award is given to exceptional technical community leaders who
actively share their high quality, real world expertise with others. We
appreciate your outstanding contributions in Dynamics NAV technical communities
during the past year.

At Microsoft, we believe that technical communities enhance people’s lives and the
industry’s success because independent experts, like you, help others extract
greater value from products and technologies through the free and objective
exchange of knowledge. As a Microsoft MVP, you are part of a highly select
group of experts that represent technology’s best and brightest who share a
deep commitment to community and a willingness to help others.

On behalf of everyone at Microsoft, thank you for your contributions to
technical communities.