Install Microsoft SQL Server and create a database - database configuration/design tasks

If you are tasked with installing and configuring the database, here is a list of steps to be taken:

  1. Install the Microsoft™ SQL Server on your computer.
    Important: Make sure that you select the correct collation when installing SQL Server: The default collation will apply to the tempdb database and will also be used for temporary tables, instead of inheriting the collation of the current database. If the default server collation does not match the collation of the current database, you will experience character set conflicts between permanent tables and temporary tables (SQL Server error message 468).
  2. Create a SQL Server database entity with the SQL Server Management Studio.
    In the database properties:
    1. Choose the right code page / collation to get a case-sensitive database; this cannot be changed later.
      Remember collation defines the character set for CHAR/VARCHAR columns, while NCHAR/NVARCHAR columns are always storing UNICODE (UCS-2 or UTF-16) characters. Informix® collation order is a codeset base for CHAR/VARCHAR/TEXT columns. If you want to get the same sort order with SQL Server, you will need to use a binary collation such as Latin1_General_BIN.
    2. Make sure the "ANSI NULL Default" option is true if you want to have the same default NULL constraint as in Informix (a column created without a NULL constraint will allow null values, users must specify NOT NULL to deny nulls).
    3. Make sure the "Quoted Identifiers Enabled" option is false to use database object names without quotes as in Informix.
  3. Create and declare a database user dedicated to your application: the application administrator.
  4. If you plan to use SERIAL emulation based on triggers using a registration table, create the SERIALREG table and create the serial triggers for all tables using a SERIAL.
    See SERIAL and BIGSERIAL data types.
  5. Create the application tables.
    Convert Informix data types to SQL Server data types. See Data type conversion table: Informix to SQL Server. In order to make application tables visible to all users, make sure that the tables are created with the 'dbo' owner.