INSERT BULK in Profiler trace

In a Profiler trace you can see INSERT BULK statements , which are a mechanism for interface between SQL Server and a client bulk copy API.For example : .Net SqlBulkCopy & ODBC Bulk Copy methods. The Profiler trace whill display the INSERT BULK statement but the FROM part , meaning you cannot view the values. The INSERT BULK statement specifies the target tables \ columns including other meta data information - NULL management , triggers etc The tricky bit from a troubleshooting per, spective is you cannot view the values in the INSERT BULK statement. The values are transfered in... Read more →

How to create a Linked Server for SQL Server to PostgreSQL

Question: I need to create a SQL Server Linked Server connection from a SQL Server to a PostgreSQL database on Linux. The purpose is to check through a basic test from INSERT and SELECT statements for data insert and retrieval . I've checked through a few sites and there are different approaches - do you have a standardized method which you utilize for this purpose.? Answer: Increasingly I'm getting more requests within my own projects to create connectivity between different database server systems. It's not uncommon to link SQL Server with all other systems such as DB2 LUW, Oracle, MariaDB... Read more →

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 →