AddressBase Premium gives you up to date, accurate information about addresses, properties, and land areas. The AddressBase Premium dataset contains Local Authority, Ordnance Survey and Royal Mail addresses, each uniquely referenced by the Unique Property Reference Number (UPRN).
For hosted customers please raise a new case online and reference the title of this article. Please provide the name or URL of the relevant Mosaic instances.
📌 Note: If you're using Oracle, please use this guide.
After completing an AddressBase update, follow the Configuring Ward Codes and Authorities guide.
Prerequisites
SQL Server releases are provided as a standard compressed SQL Server backup .bak file and accompanying SQL files. The backup file is around 5GB in size and will decompress to provide a 30GB database, named addresses. This database must be restored to the same SQL Server instance as your Mosaic databases is stored on. This database may be used by multiple Mosaic databases on that SQL Server instance.
The database backup file comes in two versions, each with a different collation:
Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS. You should choose the collation that matches the collation of your Mosaic database, which will be Latin1_General_CI_AS in most cases.
The addresses.bak file will need to be uploaded to the SQL Server.
Instructions for MS SQL Server
Step 1: Note down the users of the database addresses
Open the SQL Server Management Studio Object Explorer and expand the database addresses.
Next, expand Security, followed by Users.
Take a screenshot using the Snipping Tool or manually note down the Users. You will need to restore these users later in step 3.
Step 2: Use addresess.bak file to restore the database addresses
Right-click on the database addresses in SQL Server Management Studio Object Explorer.
Select Tasks, Restore, and then Database.... from the pop-up menu.
In the General pane, select the Source Device and then click the ... button.
Click the Add button, navigate to the location of the addresses.bak file, and then click the OK button.
Select the Options pane.
Tick the box labeled Overwrite the existing database (WITH REPLACE).
Tick the box labeled Close existing connections to destination database.
Click OK to begin the restoration of the database.
Step 3: Assign users to the database addresses
Expand the Security, Logins folder in SQL Server Management Studio Object Explorer.
Double-click the fw login to open its properties window. If there are many logins, right-click on Logins folder and use the filter option to find the login.
Select the pane User Mapping.
Under the heading Users mapped to this login Select the tick box to the left of the database addresses.
Under the heading Database role membership for: addresses tick db_datareader.
Repeat the above for all the users noted from step 1.
Step 4: Reinstate Dummy/Custom addresses
When the latest version of the Addressebase is installed any dummy/custom addresses e.g. XXX XXX postcodes will be lost and will need re-creating. It is advisable to have an SQL script prepared which can be run after each Epoch update to ensure consistency.
Step 5: Update Statistics
USE [addresses];
DECLARE @SQLStmt NVARCHAR(4000);
DECLARE StatsUpdate_cur CURSOR LOCAL STATIC
FOR
SELECT
'UPDATE STATISTICS ['+SCHEMA_NAME(so.schema_id)+'].['+so.name+'] ['+stat.name+'] WITH SAMPLE 10 PERCENT ,PERSIST_SAMPLE_PERCENT = ON;'
FROM sys.stats as stat
CROSS APPLY sys.dm_db_stats_properties (stat.object_id, stat.stats_id) AS sp
JOIN sys.objects as so on
stat.object_id=so.object_id
JOIN sys.schemas as sc on
so.schema_id=sc.schema_id
WHERE so.is_ms_shipped = 0
AND (rows_sampled IS NULL OR CAST(((sp.rows_sampled*1.0)/(sp.rows*1.0))*100.0 AS INT) < 10);
OPEN StatsUpdate_cur
FETCH NEXT FROM StatsUpdate_cur INTO @SQLstmt
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR('%s',0,0,@SQLstmt) WITH NOWAIT;
EXEC (@SQLstmt);
FETCH NEXT FROM StatsUpdate_cur INTO @SQLstmt
END
CLOSE StatsUpdate_cur;
DEALLOCATE StatsUpdate_cur;