Allways available DB

SQL Server Administration
1

Hi,
‌Our DataMart (SQL2014 Enterprise) will completely deleted during etl-load from DWH several times a day (=>drop database, create table... and load from DWH). During this time (about 15 minutes) there is no database available for the consumer. Which solution is suitable for this - fast, uncomplicated and easy to maintain, Connectionstrings, same Server/Instance. (Backup/Restore ... Log Shipping ... a.s.o)
Thanks
Regards
Nicole :slight_smile:
‌

2

please read up on principles of ETL. Why are you dropping the whole database, tables and reload from scratch?

  1. how much data (number of tables, number of rows per tables or size of whole database) is in the DataMart
  2. how often does the consumer want fresh data? once every 15 minutes, 1 hour, once a day?
  3. Use MERGE or some other mechanism that UPDATES data that has changed in source, DELETE data that no longer exist in SOURCE and INSERT new data from source.
3 
update tgt
   set tgt.column1 = src.column1,
         tgt.column2 = src.column2
  from source src
  join target tgt on src.keyId = tgt.keyId

insert into target
select column1 , column1
  from source src
where not exists (select 1 from target tgt where tgt.keyId = src.keyId )

etc

4

Create a new database and the same logins/users, load data there, rename drop the original, rename the new one to the original name. Or create all the tables with different names, load the data, drop the original tables and rename the new ones to the original names. Or create staging tables with the same structure, partition the original tables swap in the staging tables as a new partition and drop the original partition.