Should Developers manage database read status

A programmer asked me if he can include the a function to change the database status out of READ_ONLY , add some data and then put it back into READ_ONLY. These are the basic commands ALTER DATABASE database-name SET READ_ONLY ALTER DATABASE database-name SET READ_WRITE There are some considerations for deciding if a Developer should be able to include as part of an ETL process , the capacity to change the READ STATE of a SQL database 1) Requires ALTER permission on the database. This is an elevated privilege - and may break the organisations sql server security policy 2)... Read more →

Day 2 Operations - Are you ready?

Question: I was in a meeting yesterday and heard a new term - "Day 2 Operations". In the context of the conversation Iit made sense, but I'd never heard it. Is this a formal concept? What are the specifics? Answer: In general terms - Day 2 operations refers to features of a product or platform which are in an operational state. Thinking about Day 2 operations as part of a lifecycle - we'd be discussing product usage in the advanced stages of the lifecycle, Day 0 -customer requirements -Architectural Design Day 1 -Build -Deployment -Best practises configuration Day 2 -Operations... Read more →

Most important SQL Server wait types

Before I discuss the most important wait type list , I want to say that really there is no definitive list of most important sql wait types. Understanding what is normal and the context of the wait type is essential when discussing wait types. For example , you may see the sql wait type WRITELOG as high on the list of waits on a SQL Server. This may or may not be important depending on the "normal" behaviour. Benchmarking your systems is a great way of understanding the patterns your systems. Based on monitoring and analysing various SQL Server performance... Read more →

SELECT 1 FROM TABLE

Question: I'm working on an Enterprise application upgrade and have hit some problems. As part of the debugging process I'm inspecting the sql queries executing. The program is currently running SELECT 1 FROM TABLE. (replace TABLE with actual table name). What is the purpose of SELECT 1 FROM TABLE ? Answer: The SELECT 1 FROM TABLE technique is often used in certain products. The first question is what does this sql statement actually do? When you execute SELECT 1 FROM TABLE , there will be a Scan , because the purpose of SELECT 1 FROM TABLE is to return a... Read more →

When to use SQL Server 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 →