Importing an ExtraView Backup Database into MSSQL
This step assumes that SQL Server is already installed and operational. It is recommended that an MSSQL DBA carry out this installation according to the instructions provided by Microsoft. One small point, is that you should ask the DBA to check that the character set and collation were set correctly. It is recommended that you use the Latin1 General, Case Insensitive, Accent Sensitive character options. Your contact at ExtraView will have provided you with a database backup file, either containing a system that is configured for your company’s business processes, or a standard, empty ExtraView system. You will have been provided you with a file with a name like xxx.bak from ExtraView, containing the backup of the database you are going to import into your installation.
In the SQL Server Enterprise Manager, right click on the name of the database server, and choose Properties. Under the Security tab, make sure to set SQL Server and Windows rather than Windows only. Open the SQL Server Management Studio. Right-click on Databases –> Restore Database… You will see the “Restore database” dialog. Enter selections as below. Make sure to edit the physical file name and path to reflect your MSSQL installation.
You will get the Restore Database dialog. Enter selections as below.
Browse to the location where you saved the .bak file and select it.
Once you have selected the file to restore, mark the checkbox next to the selected database.
Make sure to edit the physical file name to reflect your MSSQL installation.
Now, create the ExtraView login in the database by clicking the New Login icon on the toolbar.
Create a new database user named extraview, which has the default database of extraview.
There is nothing to select on Server Roles.
For the User Mapping:
- Click on checkbox beside extraview
- Click on button in default schema cell and select [dbo]
Next, give db_owner permission to the extraview user
There is nothing to select on Securables tab Note the default values on the Status tab
The objects are all owned by golden51 or similar name – this used to be the object owner – when importing into SQL Server 2005, this step makes this the schema owner. Next, connect to the database as the extraview user:
Enter a new query in the ExtraView database – select * from area
SQL Server Configuration Option
The SQL Server database has an “oddity” in the way it handles string concatenation. This affects how the ExtraView user who writes expressions for calculated fields will observe the results. By default, within SQL Server the concatenation of a null string with any other string yields a null string as a result. For example, if the user writes an expression where the values equate to the following:
‘Thomas’ + <null>
The result will be <null>, rather than ‘Thomas’. The result may not be what the user expects. There is a SQL Server option that alters this behavior so that the user will get the result ‘Thomas’ rather than <null>. To accomplish this, the database administrator should enter the command:
alter database <db-name> set CONCAT_NULL_YIELDS_NULL off;
Database Collation Information
By default the SQL Server database that ExtraView Corporation delivers is a .bak file, and is set to a collation of SQL_Latin1_General_CP1_CI_AS
You should follow these guidelines:
- You can import this database into a SQL Server database that has a different server-level collation
- You must not modify the database level collation – it must remain SQL_Latin1_General_CP1_CI_AS when you restore the database
- In general, ExtraView requires a CI case INsensitive collation
- You should not alter the database level collation after importing the .bak file
The collation is set at the column level as well as the database and server level. If the database level collation does not match the column level collation, then in an upgrade, when the upgrade patches are run, and new tables and columns are created, they will be created with the default database level collation. This means that if the collation is not the same as SQL_Latin1_General_CP1_CI_AS (i.e. the collation setting for the columns as delivered with ExtraView), then there will be different collation types for different objects in the database. It is important that you retain the SQL_Latin1_General_CP1_CI_AS collation.
Note that ExtraView will not run under a case sensitive collation.