I would like to move my SmartConnect 21 database to a new SQL server, what is the process for this?
You can follow these steps to move the SmartConnect 21 database from one SQL server to another.
- Make a SQL backup of your SmartConnect 21 database on your current SQL server.
- In Microsoft SQL Server Management Studio, right click on the database and pick Tasks – Backup. Do a “Full” backup of the database and path the location of the backup file to somewhere you can locate after the backup completes. Then click the OK button to start the backup
- After making a backup of the database, take the SQL .bak file that was created and move it to the new SQL server.
- On the new SQL server, right click on the “Databases” folder and select “Restore Database”. It will then ask you for the location of the backup file. Select the correct .bak file and click OK.
- After the backup finishes, you will have a SmartConnect database exactly like your original server, and with exactly the same name as the old server.
- Next you need to “remove” the SmartConnect user that has access to the database. The reason we have to remove it is becuase that user has a SID (security identifier) from the original SQL server, and the SID won’t match the SID of the SmartConnect user on the new server.
- In SQL management studio find the SmartConnect database and expand the database.
- Go into the “Security – Users” subfolder inside the database.
- Find the SmartConnect user, right click on them and pick “Delete”
- After you remove the user from the database, we are ready to reinstall. Run a new SmartConnect 21 installation on the new server.
- After the product installs, launch the SmartConnect Configuration tool to create a new instance.
- Enter in your Account ID and validate. After it validates, click on “Create New Instance”
- Enter in the name of the new SQLServer\Instance in the MSSQL Server field. In my case it’s “eone\eonesql”
- Enter the “sa” user in the MSSQL User field. You can use another user that has admin like permissions to the SmartConnect database.
- Enter the “sa” password in the MSSQL Password field.
- Click on the “Test Connection” button
- After clicking on the “Test Connection” button the “SmartConnect Database” section should populate.
- For the SmartConnect database, type in the name of the database you restored. Mine is called “SmartConnect”
- Type in the SmartConnect User name. By default it’s “SmartConnect” but you can use a different user if you want.
- Type in the SmartConnect SQL password. Since SmartConnect hasn’t been installed on this server before, you can type anything you want for the password. Just write it down because you will need it when you configure the additional workstations.
- After you tab out of the “Confirm Password” field, the “SmartConnect User” section of the configuration window will enable. The available options for the “SmartConnect User” window depends on if you have an perpetual license or a SAAS (monthly subscription) license.
- If you have a “perpetual” license then the window will look like the following screenshot. It’s going to force you to create a user account. You MUST type in an account that does not currently exist. If you don’t have a specific user in mind, you can just type anything in that window. In my screenshot I just created a user named “test@test.com”
- If you have an “SAAS” license then the window will look like the following screenshot. The option to create a user is completely disabled, and the user account that is displayed is the initial installer of SmartConnect 21. In this case, you do not have to fill out of the bottom of the window, and you can just hit “Save” at the top of the window. In my screenshot the user defaulted to the initial installation users. Just accept the default and then Click “Save”. The screen will look like this before you hit “Save”.
- After clicking “Save” on the new instance, make sure it’s shown in the list as “Active”. If it shows as “Available” right click on it and pick “Set Active Instance”.
- After closing the SmartConnect configuration, and relaunching SmartConnect, you will now be able to see your existing maps from your previous installation. You can login as the new user you created, or you can use your existing user logins.
