Blog Archives

SQL Tip: Changing Database names along with file names (logical and physical)

Sometimes you need to rename a databases. Although it seems pretty easy to rename it on the surface. However for long term maintainability it is crucial that the underlying logical files and physical files also reflect the changes. Without the

Tagged with:
Posted in security, sql, SQL Server, tsql

SQL Tip: Read + Write (or any) role on databases, all existing and new ones

Trying to avoid the double hop issue which configuring linked servers, I opted for local users. After setting them up I asked the developer who requested this as to which databases he wanted to be read across the servers /

Tagged with:
Posted in SQL Server, tsql, Uncategorized

SQL Tip: How to find SQL Server installation date and time

Getting right down to business… heres the script SELECT create_date FROM sys.server_principals WHERE sid = 0x010100000000000512000000 And for Eval versions — Evaluation Version Expire Date SELECTcreate_date ASInstallationDate, DATEADD(DD, 180, create_date) AS’Expiry Date’ FROMsys.server_principals WHEREsid = 0x010100000000000512000000 GO Reference: Pinal Dave (http://blog.sqlauthority.com)

Tagged with:
Posted in sql, SQL Server, tsql, Uncategorized

SQL Server 2016 SP1 – The game changing release… seriously

SQL Server 2016 SP1, In short with this release Microsoft has enabled many features in Standard, Web and even Express editions which were only previously available in Enterprise.

Tagged with:
Posted in Uncategorized

SQL 2005 – 2016, Migrated CLR objects / Assemblies causing access error messages

You may receive an error message when you try to run an existing CLR object or create an assembly that has the external_access or unsafe permission set on a database that is attached or restored from a different server

Tagged with:
Posted in SQL Server, Uncategorized

SQL Server 2016 new features & common performance issue of Tempdb Contention

Tempdb the “shared resource” or the “scratch pad” or even the “waste bin” as described by many names in SQL Server has of course a lot many important things to do but by very nature of any shared resource there

Tagged with: , ,
Posted in log, sql, SQL Server, Uncategorized

SQL Server Restoring backups from UNC Network paths and Recovery Time Objectives

Might sound very basic but I still come across colleagues who are confused on this topic. SQL Server management studio when restoring backups does no let you browse to network drives. That is why if quick workarounds are not known people

Tagged with: , ,
Posted in backup, sql, SQL Server

Usage data for SSRS reports to decide on reports to consider for re-engineering & retiring

You have a SSRS instance on either live or production and you want to find out which reports and most / least used. Luckily SQL Server maintains by default SSRS usage info in audit logs it maintains. Although you can

Tagged with: , , ,
Posted in sql, SQL Server, ssrs, tsql

Encrypt SQL Server connections

SQL Server connections can and should be encrypted where possible but must when passing through public circuits. The overview of the process of setting up SQL Server connection encryption is as follows. Get a certificate issued by the CA in

Tagged with: , , , , ,
Posted in security, sql, SQL Server

SQL Server Index and Statistics maintenance: Importance and a good solution with explanation (Ola hallengren’s IndexOptimize)

INTRODUCTION Index and Statistics need to be maintained regularly for optimum performance of a SQL Server database engine. The SQL Server core engine within the Query Optimizer part of it, uses Indexes and statistics to create a query plan to

Tagged with: , ,
Posted in sql, SQL Server, tsql