SSRS Migration from SQL Server 2008 R2 to SQL Server 2017

As you are aware SQL Server 2008 R2 Microsoft support coming close to a shutdown, now people are starting to move to higher versions. 

This client wants to move its 800+ SSRS from SQL Server 2008 R2. Followings are learnings from the exercise.

1. SQL Server 2017 SSRS does not come as a single bundle of SQL Server. This means that you need to install SSRS as a separate installation. 

2. Though there are several ways of SSRS upgrade, ReportsServer database backup and restore was chosen as the method. In this, it is important to note that ReportServerTemp database also needs to be restored along with the ReportServer. SSRS service should be stopped before the restoring and with the restart of SSRS, SSRS services are upgraded automatically. Also, subscriptions schedules will be created as well. 

Do you think, you have completed your work. NOPE. still, a few touches are needed.

3. Authentications will not be transferred as a security measure. Therefore, you need to configure all of your data sources. If you have not managed your data sources carefully like this client, you will be coping and pasting connection strings, user names and password for one million times. 

4. If you have set up subscriptions for reports then there are few more things to be done. In standard subscriptions with excel, in the SQL Server 2008 R2 it will be configured as Excel 2003. This will be moved to that new version as it is, but will not work. It has to be changed to Excel. 

5. If you think that it is a tedious task in standard subscription, hold on for a second. There is more to come. In the case of Data-Driven Subscription, all the Excel file types are changed to MHTL Archive. Well, something little difficult to understand but that is how it is 

SQL Server Migration Assistant (SSMA) v8.1

SQL Server Migration Assistant (SSMA) for Oracle, MySQL, SAP ASE (formerly SAP Sybase ASE), DB2, and Access allows users to convert a database schema to a Microsoft SQL Server schema, upload the schema, and then migrate data to the target SQL Server (see below for supported versions).

• SSMA for Access
• SSMA for DB2
• SSMA for MySQL
• SSMA for Oracle
• SSMA for SAP ASE

Recover from a Data Disaster – Point in Time Recovery Method

This is a fantastic feature in databases but you will not realize how valuable this until you come across this situation. Before describing these features, let us first look at two cases. Incidentally, these two incidents occurred in one organization and moreover for one database but at different times.

Incident #1:  A developer was connected to both production and development instances of databases from one SQL Server Management Studio (SSMS) instance. If you have work with SSMS, you would understand how risky this is. If not, this incident tells you how risky it is. The DBA thinking that she is working on the developer instance has deleted data in a customer table in Production.

Incident #2: In the same organization, they decided to increase the column length to 50 from 15. This column is a critical column to the business. They increased the size of the column using the design view from a table. Guess what, before typing the 0, after removing the 1 the user has saved the designed, which had resulted in truncation of the table column to 5 from 15 with a data loss.

Action: In both cases, we were to help. The first question was “What is the recovery model?” if it is Simple, then nothing that we could do as Simple recovery model does not keep the transaction log history and nothing can be recovered. In these scenarios, the recovery model is Full means that we can recover data to a point of time. In this mechanism, we can recover to a given time. So, we got the full back up of the database and restored to a different database instance with the recovery option on. Then we got a log backup and restored on top of the previously restored database with specifying a time which is just before the disaster recovery. After the database is restored fully, customer table and the other tables were transferred to the production database.

Lesson Learnt:
·         Never connect to multiple databases instances from SSMS. Especially, with production and other database instances.
·         Never use a designer, to modify database schema. Always use scripting for schema design changes.
·         Always, use the Full recovery model for production.

Reference:

https://www.sqlshack.com/point-in-time-recovery-with-sql-server/


PN: Customer identity is hidden and will not be disclosed as per the policy. However, tried to expose technical approaches and tasks.