| *Bulletin ID* |
A2E_230 |
| *Last Review* |
06/13/2013 |
| *Revision* |
1.0 |
| *Previous IDs* |
None |
| *Obsoletes* |
None |
Moving the Add2Exchange Database to either an Existing or a NEW Remote SQL Instance:
- If you already have an Add2Exchange installation, you can detach the databases, attach to the new server and upgrade to the new version. During the upgrade you are prompted to pick the Database Server and Database Instance. These values are used by Add2exchange and strored in these keys: HKEY_LOCAL_MACHINE\SOFTWARE\OpenDoor Software®\Add2Exchange\DBServer and DBInstance. DBServer and DBinstance gets pointed to the correct SQL server, which is local by default and whose value is A2ESQLSERVER.
- If you are attaching to a remote SQL Server it can be done in one of two ways: You can create a new instance, which we prefer, or you can attach to an existing instance. The trouble with atttaching to an existing instance, is we want to control the entire database properties and characteristics and attaching the tables to an existing instance does not allow this kind of control, Anything done to our instance would be done to the entire instance, such as allowing for maximum or minimum memory usages.
To attach to an existing SQL instance – not preferred, but fairly easy
Normally during the upgrade we prompt for the DBServer and Instance. The installation will only allow for the local installation of the SQL Server for Add2Exchange or for the attachment of the database to an existing instance of a remote server. To create a new Add2Exchange A2ESQLServer instance, follow the procedures below and give permissions to do so before opening up the Console. Enable permissions for Add2Exchange account to remotely install the Database to the existing instance: and then open the Add2Exchange Console.
Moving or making new database to an Existing A2E Database to a remote SQL Server using an existing instance
If you already have an Add2Exchange installation, you can move the database to an existing instance or to a new instance, whichever method you chose. The after procedures are the same.
Upgrade to latest version from Downloads if you have valid software maintenance. Finish install and open Add2Exchange Console.
If it doesn’t open, fix that problem first. If it does, then continue by closing the Add2Exchange Console and do not start the Add2Exchange Service
If Started, Stop the Add2Exchange Service.
Detach Database using A2EDiags
Physically or logically Move the Add2Exchange.mdf and Add2Exchange.log files to the new SQL Server
Open SQL Management Studio
Attach to now local instance
Give permissions to the "remote" SQL server including master instance - Give login Security - dbcreator and owner permissions to master instance to Add2Exchange Service Account and open new SQL Server location with an existing instance
Back on A2E Server, Uninstall a2e
Open registry
Navigate to localmachine/Software/OpenDoorSoftware/Add2exchange or localmachine/Wow6432node/Software/OpenDoorSoftware/Add2Exchange
Export and save older localsqlreg.reg file in our "zlibrary"
Remove dbserver value, not key
Remove dbinstance value, not key
Install existing version of Add2Exchange
When prompted for SQL server name
Type in sql server name or pick if agent is on
Accept
Then it will list instances
Pick instance
Finish install
Open Add2Exchange Console
If it Opens Successfully, then you did it right. Congratulations! Start Add2Exchange Service and review Add2Exchange Event log.
NOTE: In order for Add2Exchange to test the status of the remote SQL Server, you will need to grant the service account permissions to detect services on the remote SQL computer
This is how: http://social.technet.microsoft.com/Forums/windowsserver/en-US/5c53eb1e-d3d8-4ac7-89ff-b7429ba78a38/how-to-manage-services-remotely
Solution 1.
Please put the user account in the built-in administrators group on the target server. This is a simple solution to resolve the issue. Sometimes this doesn’t work if you have a policy defined. Go to Solution 2.
Solution 2.
Set a new Group policy to grant the user with proper permission to access and view the service.
Please perform the following steps on the domain controller.
1. Click Start, point to Administrative Tools, and then click Active Directory Users and Computers.
2. Find and locate the organizational unit which contains the user, Right-click on it and click Properties
3. Click the Group Policy tab, and then click New, or find existing policy. Type a name for the new Group Policy object (for example, use the name of the organizational unit for which it is implemented), and then press ENTER.
4. Click the new Group Policy object in the Group Policy Objects Links list (if it is not already selected), and then click Edit.
5. Expand Computer Configuration -> Windows Settings -> Security Settings -> System Services
6. In the right pane, double-click the target service to that you want to apply permissions. The security policy setting for that specific service is displayed.
7. Click to select the "Define this policy setting" checkbox.
8. Click "Edit Security"
9. Add the user account and grant it with "Start, stop and pause" and "Read" permissions to the user account that you want to access the service remotely, and then click OK.
10. Under "Select service startup mode", click "Automatic" startup mode option, and then click OK.
11. Close the Group Policy Object Editor, click OK, and then close the Active Directory Users and Computers tool.
12. Please run "gpupdate /force" on both the DC and the client to make the GPO settings come into effect.
13. Reset the problematic client and then check if the issue can be resolved.
More on Rights: http://msdn.microsoft.com/en-us/library/windows/desktop/ms685981(v=vs.85).aspx
Even more on Rights: http://serverfault.com/questions/55961/is-it-possible-to-use-group-policy-to-grant-the-permission-to-manage-windows-ser
If you are creating a new instance during the installation, open the Add2Exchange Console and pick the appropriate SQL machine and SQLInstance when prompted or after editing the registry.
Method: To attach to a new A2ESQLServer instance on the remote server
We will be installing an instance on the remote SQL server. The configuration file creates A2E SQL Instance which is then used by the program to setup the database.
From the Replication Server: copy the appropriate SQL installation files from here to the Remote SQL Server:
If you are running Full Enterprise SQL, you would add the instance as you normally do with the configuration file specified.
C:\Program Files\OpenDoor Software®\Add2Exchange\Console\Database\Setup\sqlEXPR x32.exe
Or C:\Program Files\OpenDoor Software®\Add2Exchange\Console\Database\Setup\sqlEXPR x64.exe
And the configuration file
C:\Program Files\OpenDoor Software®\Add2Exchange\Console\Database\Setup\configurationFile.ini
Log on to your remote SQL Server as administrator
Give the right for the Add2Exchange Service Account to be part of the local Administrators group to the SQL Machine
Log on as the Add2Exchange Service Account
If you are logged in as the service account when you install it, the account automatically has appropriate rights to the instance.
Start/Run
If using Enterprise SQL, run your your full SQL install with the configurationfile.ini
If using Express follow these instructions:
- Microsoft SQL Server 2008 Express with Tools x64
“Microsoft SQL Server 2008 Express with Tools x64.exe" /configurationfile=configurationfile.ini
Microsoft SQL Server 2008 Express with Tools x86
“Microsoft SQL Server 2008 Express with Tools x86.exe” /configurationfile=configurationfile.ini
"sqlEXPR x64.exe" /configurationfile=configurationfile.ini
The configurationfile.ini has this as contents:
;SQLSERVER2008 Configuration File
[SQLSERVER2008]
; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will reflect the instance ID of the SQL Server instance.
INSTANCEID="A2ESQLSERVER"
; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
ACTION="Install"
; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, and Tools. The SQL feature will install the database engine, replication, and full-text. The Tools feature will install Management Tools, Books online, Business Intelligence Development Studio, and other shared components.
; With SQL Management Studio FEATURES=SQLENGINE,REPLICATION,SNAC_SDK,SSMS
FEATURES=SQLENGINE,REPLICATION,SNAC_SDK
; Displays the command line parameters usage
HELP="False"
; Specifies that the detailed Setup log should be piped to the console.
INDICATEPROGRESS="True"
; Setup will not display any user interface.
QUIET="True"
; Setup will display progress only without any user interaction.
QUIETSIMPLE="False"
; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.
X86="False"
; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.
ERRORREPORTING="False"
; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.
SQMREPORTING="False"
; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).
INSTANCENAME="A2ESQLSERVER"
; Auto-start service after installation.
AGTSVCSTARTUPTYPE="Manual"
; Startup type for Integration Services.
ISSVCSTARTUPTYPE="Automatic"
; Account for Integration Services: Domain\User or system account.
ISSVCACCOUNT="NT AUTHORITY\NetworkService"
; Controls the service startup type setting after the service has been created.
ASSVCSTARTUPTYPE="Automatic"
; The collation to be used by Analysis Services.
ASCOLLATION="Latin1_General_CI_AS"
; The location for the Analysis Services data files.
ASDATADIR="Data"
; The location for the Analysis Services log files.
ASLOGDIR="Log"
; The location for the Analysis Services backup files.
ASBACKUPDIR="Backup"
; The location for the Analysis Services temporary files.
ASTEMPDIR="Temp"
; The location for the Analysis Services configuration files.
ASCONFIGDIR="Config"
; Specifies whether or not the MSOLAP provider is allowed to run in process.
ASPROVIDERMSOLAP="1"
; Startup type for the SQL Server service.
SQLSVCSTARTUPTYPE="Automatic"
; Level to enable FILESTREAM feature at (0, 1, 2 or 3).
FILESTREAMLEVEL="0"
; Set to "1" to enable RANU for SQL Server Express.
ENABLERANU="True"
; Specifies a Windows collation or an SQL collation to use for the Database Engine.
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
; Account for SQL Server service: Domain\User or system account.
SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"
; Provision current user as a Database Engine system administrator for SQL Server 2008 Express.
ADDCURRENTUSERASSQLADMIN="True"
; Specify 0 to disable or 1 to enable the TCP/IP protocol.
TCPENABLED="1"
; Specify 0 to disable or 1 to enable the Named Pipes protocol.
NPENABLED="0"
; Startup type for Browser Service.
BROWSERSVCSTARTUPTYPE="Automatic"
; Specifies how the startup mode of the report server NT service. When
; Manual - Service startup is manual mode (default).
; Automatic - Service startup is automatic mode.
; Disabled - Service is disabled
RSSVCSTARTUPTYPE="Automatic"
; Specifies which mode report server is installed in.
; Default value: “FilesOnly”
RSINSTALLMODE="FilesOnlyMode"
If you are moving an existing installation over here to this new remote SQL server instance
See section above: To enable Add2Exchange to talk to the remote SQL Server
Make the changes above to the DBServer and DBinstance and attach the DB from the old Server here.
Detach the older DB from the older replication server
Move the files over to the new SQL Server as directed above
Then log in to the remote SQL server, browse for the new instance and attaché with SQL Management Tools
Attach the A2E.mdb files to this new instance