{"id":24404,"date":"2024-02-15T14:53:34","date_gmt":"2024-02-15T22:53:34","guid":{"rendered":"https:\/\/docs.extraview.com\/v25\/book\/sql-server-database-configuration-1\/"},"modified":"2025-02-17T12:59:30","modified_gmt":"2025-02-17T20:59:30","slug":"sql-server-database-configuration-1","status":"publish","type":"page","link":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/","title":{"rendered":"SQL Server  Database Configuration"},"content":{"rendered":"<h3>Importing an ExtraView Backup Database into MSSQL<\/h3>\n<p>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\u2019s 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.<\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/SQL_server_properties.png\" \/><\/p>\n<p>In the SQL Server Enterprise Manager, right click on the name of the database server, and choose Properties. Under the <b>Security<\/b> tab, make sure to set <b>SQL Server and Windows<\/b> rather than <b>Windows only<\/b>. Open the SQL Server Management Studio. Right-click on <b>Databases &#8211;&gt; Restore Database\u2026<\/b> You will see the \u201cRestore database\u201d dialog. Enter selections as below. Make sure to edit the physical file name and path to reflect your MSSQL installation.<\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/restore_database.png\" \/><\/p>\n<p>You will get the <b>Restore Database<\/b> dialog. Enter selections as below.<\/p>\n<p><img decoding=\"async\" style=\"width: 90%;\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-1.png\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-2.png\" \/><\/p>\n<p>Browse to the location where you saved the .bak file and select it.<\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-3.png\" \/><\/p>\n<p>Once you have selected the file to restore, mark the checkbox next to the selected database.<\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-4.png\" \/><\/p>\n<p>Make sure to edit the physical file name to reflect your MSSQL installation.<\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-5.png\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-6.png\" \/><\/p>\n<p>Now, create the ExtraView login in the database by clicking the <b>New Login<\/b> icon on the toolbar.<\/p>\n<p><img decoding=\"async\" src=\"extraview-media\/images\/ig\/mssql\/sql-server-7.png\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-8.png\" \/><\/p>\n<p>Create a new database user named extraview, which has the default database of extraview.<\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-9.png\" \/><\/p>\n<p>There is nothing to select on <b>Server Roles<\/b>.<\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-10.png\" \/><\/p>\n<p>For the User Mapping:<\/p>\n<ul>\n<li>Click on checkbox beside <b>extraview<\/b><\/li>\n<li>Click on button in <b>default schema<\/b> cell and select [dbo]<\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-11.png\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-12.png\" \/><\/p>\n<p>Next, give db_owner permission to the extraview user<\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-13.png\" \/><\/p>\n<p>There is nothing to select on <b>Securables<\/b> tab Note the default values on the <b>Status<\/b> tab<\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-14.png\" \/><\/p>\n<p>The objects are all owned by <b>golden51<\/b> or similar name &#8211; this used to be the <b>object owner<\/b> \u2013 when importing into SQL Server 2005, this step makes this the <b>schema owner<\/b>. Next, connect to the database as the extraview user:<\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-20.png\" \/><\/p>\n<p>Enter a new query in the ExtraView database \u2013 <span class=\"fixedWidthFont\">select * from area<\/span><\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ig\/mssql\/sql-server-21.png\" \/><\/p>\n<h3>SQL Server Configuration Option<\/h3>\n<p>The SQL Server database has an \u201coddity\u201d 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:<\/p>\n<p><span class=\"fixedWidthFont\">&#8216;Thomas&#8217; + &lt;null&gt;<\/span><\/p>\n<p>The result will be <span class=\"fixedWidthFont\">&lt;null&gt;<\/span>, rather than &#8216;Thomas&#8217;. 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 \u2018Thomas\u2019 rather than &lt;null&gt;. To accomplish this, the database administrator should enter the command:<\/p>\n<p><span class=\"fixedWidthFont\">alter database &lt;db-name&gt; set CONCAT_NULL_YIELDS_NULL off; <\/span><\/p>\n<h3>Database Collation Information<\/h3>\n<p>By default the SQL Server database\u00a0that ExtraView\u00a0Corporation\u00a0delivers is a .bak file, and is set to a collation of <span style=\"font-family: courier new,courier,monospace;\">SQL_Latin1_General_CP1_CI_AS<\/span><\/p>\n<p>You should follow these guidelines:<\/p>\n<ul>\n<li>You\u00a0can import this database into a SQL Server database that has a different server-level collation<\/li>\n<li>You must not\u00a0modify the database level collation &#8211; it must remain <span style=\"font-family: courier new,courier,monospace;\">SQL_Latin1_General_CP1_CI_AS <\/span>when you restore the database<\/li>\n<li>In general, ExtraView requires a CI case INsensitive collation<\/li>\n<li>You\u00a0should\u00a0not alter the database level collation after importing the .bak file<\/li>\n<\/ul>\n<p>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 <span style=\"font-family: courier new,courier,monospace;\">SQL_Latin1_General_CP1_CI_AS <\/span>(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 <span style=\"font-family: courier new,courier,monospace;\">SQL_Latin1_General_CP1_CI_AS <\/span>collation.<\/p>\n<p>Note that ExtraView will not run under a case sensitive collation.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":24373,"menu_order":8,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"_lmt_disableupdate":"no","_lmt_disable":"","_kad_blocks_custom_css":"","_kad_blocks_head_custom_js":"","_kad_blocks_body_custom_js":"","_kad_blocks_footer_custom_js":"","_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"footnotes":""},"class_list":["post-24404","page","type-page","status-publish","hentry"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL Server Database Configuration - Product Documentation<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Database Configuration - Product Documentation\" \/>\n<meta property=\"og:description\" content=\"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...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/\" \/>\n<meta property=\"og:site_name\" content=\"Product Documentation\" \/>\n<meta property=\"article:modified_time\" content=\"2025-02-17T20:59:30+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/\",\"url\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/\",\"name\":\"SQL Server Database Configuration - Product Documentation\",\"isPartOf\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/#website\"},\"datePublished\":\"2024-02-15T22:53:34+00:00\",\"dateModified\":\"2025-02-17T20:59:30+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/docs.extraview.com\/v25\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ExtraView 25\",\"item\":\"https:\/\/docs.extraview.com\/v25\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Installation &#038; Upgrade Guide\",\"item\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"Software Installation &#038; Upgrades\",\"item\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/\"},{\"@type\":\"ListItem\",\"position\":5,\"name\":\"SQL Server Database Configuration\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/#website\",\"url\":\"https:\/\/docs.extraview.com\/v25\/\",\"name\":\"ExtraView Product Documentation\",\"description\":\"ExtraView Documentation\",\"publisher\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/docs.extraview.com\/v25\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/#organization\",\"name\":\"ExtraView Corporation\",\"url\":\"https:\/\/docs.extraview.com\/v25\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png\",\"contentUrl\":\"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png\",\"width\":512,\"height\":512,\"caption\":\"ExtraView Corporation\"},\"image\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Database Configuration - Product Documentation","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Database Configuration - Product Documentation","og_description":"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...","og_url":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/","og_site_name":"Product Documentation","article_modified_time":"2025-02-17T20:59:30+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/","url":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/","name":"SQL Server Database Configuration - Product Documentation","isPartOf":{"@id":"https:\/\/docs.extraview.com\/v25\/#website"},"datePublished":"2024-02-15T22:53:34+00:00","dateModified":"2025-02-17T20:59:30+00:00","breadcrumb":{"@id":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/sql-server-database-configuration-1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/docs.extraview.com\/v25\/"},{"@type":"ListItem","position":2,"name":"ExtraView 25","item":"https:\/\/docs.extraview.com\/v25\/"},{"@type":"ListItem","position":3,"name":"Installation &#038; Upgrade Guide","item":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/"},{"@type":"ListItem","position":4,"name":"Software Installation &#038; Upgrades","item":"https:\/\/docs.extraview.com\/v25\/extraview-25\/installation-upgrade-guide-1\/software-installation-upgrades-1\/"},{"@type":"ListItem","position":5,"name":"SQL Server Database Configuration"}]},{"@type":"WebSite","@id":"https:\/\/docs.extraview.com\/v25\/#website","url":"https:\/\/docs.extraview.com\/v25\/","name":"ExtraView Product Documentation","description":"ExtraView Documentation","publisher":{"@id":"https:\/\/docs.extraview.com\/v25\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/docs.extraview.com\/v25\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/docs.extraview.com\/v25\/#organization","name":"ExtraView Corporation","url":"https:\/\/docs.extraview.com\/v25\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/","url":"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png","contentUrl":"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png","width":512,"height":512,"caption":"ExtraView Corporation"},"image":{"@id":"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/"}}]}},"taxonomy_info":[],"featured_image_src_large":false,"author_info":{"display_name":"carl.koppel","author_link":"https:\/\/docs.extraview.com\/v25\/author\/carl-koppel\/"},"comment_info":0,"_links":{"self":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages\/24404","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/comments?post=24404"}],"version-history":[{"count":0,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages\/24404\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages\/24373"}],"wp:attachment":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/media?parent=24404"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}