Migrate SQL Server Reporting Services database from SSRS 2012 to SSRS 2016

Question: I need to migrate and upgrade an SSRS 2012 database to an SSRS 2016 . Could you supply me with a general framework for a migration path . Answer:This is a basic framework and you may need to build in extra some extra steps to prepare and validate your particular upgrade and migration path from SQL Server 2012 to SQL Server 2016. Back up ReportServer database on source SSRS 2012 Back up ReportServerTempDB on source SSRS 2012 Backup encryption keys on source SSRS 2012 Stop SSRS 2016 service on target server Restore ReportServer database Note: You should see informational... Read more →

Change Data Capture

There’s a number of things to consider in deciding on Change Data Capture (CDC) and deciding on the ideal use cases for CDC is very much dependant on the specific requirements. So it is important to understand in detail the project requirements. This should be weighed up against other solutions such as Triggers Some initial thoughts for discussion: For Auditing – CDC is an appropriate tool , as it can present the complete history of the data. Deciding on whether you’d rather use triggers will depend on reviewing the data in the change table and assessing whether it’s appropriate for... Read more →

Should I use SQL Change Data Capture for data audit ?

Question: There is lots of information around the SQL Server Change Data Capture (CDC) feature, and I’m trying to decide if CDC is suitable for the purposes of a project which requires data auditing. We currently have a sql server triggers based solution in place for capturing data changes on certain tables. Could you outline some the issues to consider when deciding if CDC is an appropriate tool. Answer: Deciding on when to adopt CDC is dependant on the specific requirements. The first step is to understand in detail the project requirements. As you mentioned in your question, there are... Read more →

How to get row count for every table in database

Question: I need some sql code to record the row count of every table in a database. I only need to run this code once , but it’s on a database with hundreds of tables Answer: There are numerous ways of getting the row count for every table in a database. If I require a quick method , just to simply record every table row count , I’ll use some code used in this example. This code utilises the sp_msforeachtable stored procedure . As the input parameter, I include an INSERT INTO .. construct. The sp_msforeachtable stored procedure iterates through... Read more →

Display all files in a directory with master.sys.xp_dirtree

Question: I want to list all files in a folder but not use Powershell. I’ve read SQL Server - Powershell Get-ChildItem with examples , which outlines how to list all files in a folder with the Powershell Get-ChildItem cmdlet . But due to various security policies , we can’t use this methodology. As part of the solution , I want to place these files into a temporary table, which other sql code can use this list to complete some multiple BULK INSERT commands Is there an extended procedure , that will support this type of result? Answer: You can use... Read more →