SQL Server :: When was a Database taken Offline

Who changed SQL Server database state to OFFLINE or When was my database last taken Offline?

Here is a T-SQL script which tells when and who took the database offline or online recently.
This script utilizes the default trace and if the trace is reset after the database went offline or online then you have change the trace file path and name in the script.

DECLARE  @DBNAME nvarchar(100)

  ,@FileName nvarchar(max)

  ,@spid int

  ,@LogDate Datetime

  ,@Status nvarchar(10)

  

SET @DBNAME = 'AdventureWorks2008R2' -- Change DB Name

SET @Status = 'OFFLINE' --[OFFLINE or ONLINE]

SELECT @FileName=[path] FROM sys.traces WHERE is_default=1

DECLARE @ErrorLogTable table (Logdate datetime, ProcessInfo nvarchar(10), [Text] nvarchar(max))

INSERT INTO @ErrorLogTable

EXEC xp_readerrorlog 0,1, @Status, @DBNAME, NULL, NULL, 'desc'

SELECT TOP 1 @spid=cast(SUBSTRING(ProcessInfo,5,5) AS int)

   ,@LogDate=cast(Logdate AS nvarchar)

FROM @ErrorLogTable

SELECT DatabaseID, DatabaseName, HostName, ApplicationName, LoginName, StartTime

FROM sys.fn_trace_gettable( @FileName, DEFAULT )

WHERE spid=@spid and DatabaseName=@DBNAME and CAST(StartTime AS nvarchar)=@LogDate

If you didn't get the result by running above code it means, trace is reset after the database went offline. 

In this case go run following code and get the path.

select path FROM sys.traces WHERE is_default=1

Change file name and run following code. Last row will have the timestamp. 

SELECT DatabaseID, DatabaseName, HostName, ApplicationName, LoginName, StartTime

FROM sys.fn_trace_gettable( 'H:\sqlsysdb\MSSQL10\MSSQL\Log\log_228.trc', DEFAULT )

WHERE DatabaseName='TestDB'

Second option is to check SQL Server error log and you'll get something similar for your database. 

Hope it will be helpful.

SQL Server :: Database Design Best Practices

Database Design Best Practices :-

Although there are several factors that needs to be considered while designing databases, here are some tips for designing your relational data warehouse database: 

• Keep the data files and log files on separate drives with separate spindles. 
• Make use of the fastest drives possible for data and log files.
• Create data files for as many processors on the machine and distribute the files equally on the different available drives.
• As transactional backups are normally not taken, set the Recovery Model of the database to SIMPLE.  If it is required to do transactional backups, then switch to BULK LOGGED recovery model before bulk data load operations and switch back to FULL recovery model after the data load.
• Design views to pull data from base tables of relational data warehouse and specify query hints or filter conditions in them.
• To avoid more locks or lock escalations, specify the TABLOCK query hint while querying or ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF when creating tables or indexes or pull data from a read only database. 
• Sometimes to aggregate fact data at the source before pulling the data, one can improve performance by creating  indexed (materialized) views for this and instead of doing aggregations every time, pull the data from the indexed view. 
• Make sure that the resources are available to SQL Server for serving the data pull requests; one can use RESOURCE GOVERNOR to control the amount of resources available to OLTP and OLAP operations.  

Hope it will be helpful.

Thanks, 

SQL Server : SQL Server 2016 New Features

SQL Server 2016 New Features

1:-  Always Encrypted

Starting off, we have always encrypted. This feature enables a client side encryption of table data. This is similar to column encryption, but unlike column encryption, the data type does not need to be varbinary. 

This feature requires a driver on the client applications to communicate with the database. Encryption occurs at the client side, so the data is not plain text during transmission. 

Another difference from column encryption (as a SQL only technology) is that the encryption key may either be deterministic or randomized. The benefit of deterministic is that it allows for the indexing of column data so that performance of queries that need to filter on the column are more efficient. 

2:-  Row Level Security

Row Level Security allows tables to be configures where some users may work with only a subset of rows in the table. This function requires setup on the DB side as well as coding requirements on the developer side. Working with this feature requires setting up inline table functions and security policies on tables that govern the filtering/blocking at the row level. 

Some of the issues that may arise from this feature is inconsistent application functionality, since with the security policies set up, it’s possible to create an instance where a user may update/insert a record, but then not be able to view it. 

3:- Dynamic Data Masking

This, as the name suggests, allows for data to be stored normally, but only certain users may see the information unmasked. Masking is performed as part of a table’s DDL; assigning masking at a column level. 

There are new permissions to allow granular rights of unmasking to certain user groups. However, dbo always have unmasking rights. All queries accessing masked columns are automatically masked/unmasked depending of the permission of the user making the query. 

Masking does not alter the underlying data, therefore, adding masking to columns is a table metadata change. 

4:-  Availability Groups

A few more improvements were made in Availability Groups. Automatic failover may now be performed over a set of 3 nodes, instead of 2. As well log transport to synchronize between the primary and secondary replica(s) was streamlined. In addition, build in load balancing of the replicas is possible. (2014 and earlier, all traffic was directed to one node unless it was unavailable, or 3rd party products needed to be used).

5 :-  TempDB Enchancements

On installation tempdb will have created the recommended number of files (1 per logical processor up to 8). This can still be modified on installation to something besides the default. As well, trace flags 1117 and 1118 have been eliminated as their functionality has been built into tempdb. (1117 was uniform data file growth, 1118 was full extent allocation)

6 :-  Query Store

One of the best new features, from a DBA’s perspective. Query Store is a per database implemented item. It is meant to greatly increase the ease of finding performance issues and troubleshooting. 

The query store has two components, a plan store that persists the execution plans, and a run-time stats store, that persists the stats surrounding query execution (CPU, I/O, memory etc). 

7:- R integration

R is a programming language widely used by data scientists for advanced analytics. SQL Server R Services is a result of a Microsoft acquisition in 2015. For SQL 2016, R services have been integrated into the SQL Server platform. R code may be executed directory in a sql database. This adds to te workload of the server, but allows to greater security and performance because data movement is minimized. 

8:- SSRS 

There are a number of improvement in 2016 (compared with almost no change in 2014). 

Mobile reports are now supported with the integration of another Microsoft acquisition. Mobile reports and standard reports may be viewed through the same web portal interface of the report server. 

As well, enhancements have been created to make administering ownership and subscriptions easier. 

9 :-  Polybase

Polybase is s transparent access layer that facilitates connectivity between SQL and Hadoop data sources. It’s purpose is to merge big data into SQL platforms. This integrations means you can execute T-SQL queries against this platform without knowing Map/Reduce, Hive or any other Hadoop related tools. 

Hope it will be helpful. I'll try to write more on these features in detail in coming days. 

Thanks

SSRS : Query execution failed for dataset (rsErrorExecutingCommand)

SSRS : Query execution failed for dataset (rsErrorExecutingCommand) 

An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for dataset 'dataset1'. (rsErrorExecutingCommand)

For more information about this error navigate to the report server on the local server machine, or enable remote errors 

To find out the exact error:

1. Navigate to E:\Program files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\LogFiles\ReportServerService__12_22_2016_00_04_44.log

2. Located the following error

 Info: 

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'dataset1'. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'get_report_data', database 'Test123', schema 'dbo'.

Resolution:

Granted the execute permisison to account which was used to create data source.

grant execute on [get_report_data] to [ReportID]

Retry accessing the report. You should not see the error anymore and report should be running fine.

Thanks

For SQL Server DBAs :: Frequently used System Administrator command line shortcuts to popular MMCs

For SQL Server DBAs :: Frequently used System Administrator command line shortcuts to popular MMCs



Simply get to a run command (Start>Run) or a  command prompt (Start>Run>CMD [enter])


Local Security Settings Manager : secpol.msc
Local Users and Groups Manager : lusrmgr.msc
Services Management : services.msc
Shared Folders : fsmgmt.msc
Teminal Services RDP : MSTSC
Teminal Services RDP to Console : mstsc /v:[server] /console
Windows Mangement Instumentation : wmimgmt.msc
Disk Manager : diskmgmt.msc
Event Viewer : eventvwr.msc
Computer Management : compmgmt.msc

SQL Server: How to use NamedPipe Protocol to establish connection

Error:-

 (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

To resolve this issue , we can create alias on client server using TCP/IP protocol.

But story was different here, There was a situation when an application was unable/couldn't establish connection using TCP/IP protocol. 

As a solution , I was bound to use NamedPipe protocol.

Steps to use NamedPipe protocol:-

1. Enable the NamedPipe protocol on Database server

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select NamedPipe

Right Click on NamedPipe>> Click on Enable

2. For clustered instance named S12345\SQL1 , here is is pipe name

\\.\pipe\$$\S12345\MSSQL$SQL1\sql\query

S12345 -- It is virtual SQL Server name 
SQL1 -- Instance name

3. You must restart SQL Server Services for all the changes to take effect.

4. Enable the NamedPipe protocol on Client server

5. Create Alias on Client server/Client Machine

Create 32 bit alias with following mentioned parameters

a. Alias name : S12345\SQL1
b. Pipe Name : \\S12345.ca.com\pipe\$$\S12345\MSSQL$SQL1\sql\query
c. Protocal : Named Pipe
d. Server : S12345.ca.com (FQDN)

Now, you are ready to use alias in in connection string. 

To validate whether named pipe is being used or not:-

First, you can establish connection from SSMS on client server/your laptop

Second, you can run below query and see the value in net_transport column

Select * from sys.dm_exec_connections order by 1

net_transport -- It must be Namedpipe

Hope it will help you.

Warm Regards,

Chhavinath Mishra
Sr. Specialist Database Administrator

SQL Server : To drain all the connections for a particular database

/***********************************************************************
Kill all the spid's attached to a particular database. Very useful when 
continually restoring backups for testing.

***********************************************************************/
Error Msg:

ALTER DATABASE failed because a lock could not be placed on database 'employee'. 

ALTER DATABASE statement failed.
Cannot drop database "employee" because it is currently in use.

Solution: 

To drain all the connections , use bellow SQL to kill all the connections.

use master
go
Sp_killall 'employee'

to verify, use following mentioned code

select * from sys.sysprocesses where dbid = <dbid for your database>

Note: Create this (sp_KillAll Stored Procedure) in the master database, and call it from anywhere; but you can't kill your own process!

*********************Script to create sp_KillAll Stored Procedure *****************

CREATE PROCEDURE sp_KillAll
@DbName VARCHAR(100)
AS


IF db_id(@DbName) = NULL 
BEGIN
    PRINT 'DataBase dose not Exist'
END
ELSE
BEGIN
    DECLARE @spId VARCHAR(30)
        
    DECLARE TmpCursor CURSOR FOR
    SELECT  'Kill ' + CONVERT(VARCHAR, spid)  AS spId
      FROM master..SysProcesses
      WHERE db_Name(dbID) = @DbName
      AND spId <> @@SpId
      AND dbID <> 0
      
    OPEN TmpCursor
        
    FETCH NEXT FROM TmpCursor INTO @spId 
        
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC (@spId)
     
        FETCH NEXT FROM TmpCursor INTO @spId 
    END

    CLOSE TmpCursor
    DEALLOCATE TmpCursor
END 
GO 

Warm Regards,
Chhavinath Mishra 
Sr. Specialist Database Administrator