Sitecore: DB Migration Sitecore 10.2

Sitecore documentation provides the corresponding guide, find it here Upgrade to 10.2, check it out and now we can review some notes to take in account to make this go go smoothly. Also, I will give some references in terms of size and time for small an large dbs. 

Practice the upgrade on a test environment as similar as possible to the real one. There are several differences between a local on-premise dbs. setup comparing with the Azure services available. 

Make sure you have the right SQL server version. Sitecore 10.2 needs MS SQL 2017 or later.

On dbs. backups, get  PITR, otherwise you will have errors prompting on your db restoration since the dbs you are getting the backup from is being written every time then a usual backup will be corrupted. 

        "The bacpac file is not transactional, so new rows written to your target database while the bacpac is being             generated will end up corrupting the index. The database either must have no other users connected making             writes, or you can copy the database and make a bacpac from the copy."  

Stop all content tests in case there is any. Check this here Cancelling content tests

Now you can start running the scripts. 

Analytics Dbs 

• Reporting (Do it at the end of all upgrade process)
• Rebuild the reporting database. Find the guide here
• Get a reporting db dacpac file and deploy a clean database because the rebuild will delete all data and populate the database again. 
• Add the connection string to the secondary reporting db at the config file:                   <addname="reporting.secondary"connectionString="user id=_sql_server_user_;password=_user_password;Data Source=_sqlserver_;Database= Sitecore_Reporting_Secondary"/>
• Go to site/sitecore/admin/RebuildReportingDB.aspx and start the rebuild process
• Swap the databases and delete or comment the secondary connection string otherwise data will be write to both dbs.
• Reporting user should be member of  db_datareader and db_datawritter roles. 
• Collection SMM:
• Run the SXP_collection_smm.sql script 
• Collection shards:
• Run the upgrade script for every shard:
• If AE is On, run the SXP_collection_AE_Part1.sql script
• If AE is Off, run the SXP_collection_Part1.sql script.
• Marketing Automation: 
• Run sxp_marketingautomation.sql.
• Scripts contain 2 indexes dropping and creation and this will take 1-5 hours depending on the amount of data. It took 5 hours for a 6 million registers dbs
• Complete upgrade script will take 7-21 hours.         
• Finally, compare the schemas between vanilla 10.2  version and upgraded version are the same. You can use visual studio tool. Go to tools>SQL Server> New Schema Comparison. The unique difference should be users and some permissions
• Note: About schemas, in shards db there are two more differences  xdb_collection.GetContactsByIdentifierTvp procedure does not have a facetkey as in vanilla version. Reviewing the upgrade script seems like this procedure will not be used again since the one created is the xdb_collection.GetContactsByIdentifiersTvp_V_10_1_0 procedure. 
• Db restoring timing 
• Marketing automation db: 3hours.
• Shard dbs: around 30-60 min each one
• SMM db: < 30 min 
• According with official guide there is a second part for the shard collections upgrade meant to be used in case you need to split the shards databases, apart from the two we have by default, then script SXP_collection_Part2.sql is not needed.

Last notes 

Store procedure "sp_configure" value at azure sql database is 1 by default.  In case you face some error related to this.

Once a database is restored, change the compatibility level to 150  for SQL Server 2019  

EXM upgrade was not difficult, just make sure the bacpac file is a PITR and run the script in the order specified by the guide. 

 

Hope this help you. Please, fill free to leave a comment, question or suggestion below.