HOW to Find Unused databases

Step1:
---Create a table to keep track of connectios:
USE AuditDB;
GO
CREATE TABLE [dbo].[TrackDBConnections]
    (
      ServerName VARCHAR(50) NOT NULL ,
      DatabaseName VARCHAR(30) NOT NULL ,
      NumberOfConnections INT NOT NULL ,
      LoginTime DATETIME NOT NULL ,
      LastBatch DATETIME NOT NULL ,
      DateTimeRecordInsertd DATETIME NULL ,
      Status VARCHAR(100) NULL ,
      HostName VARCHAR(100) NULL ,
      ProgramName VARCHAR(1000) NULL ,
      NTusername VARCHAR(100) NULL ,
      Loginame VARCHAR(100) NULL
    )
ON  [PRIMARY];
GO





Step2:--Main Step
---create a SP to insert the Data into the [TrackDBConnections] Table:
USE AuditDB
GO
CREATE PROCEDURE usp_TrackDBConnections
AS
BEGIN

 /*    The main purpose of the SP is to keep track of Connections which get connect to Databases
                and decide on which database is being used or not being used by any applications and then make OFFLINE for fewdays, backup and then drop..
 */


 SET NOCOUNT ON;
INSERT INTO [TrackDBConnections](ServerName, DatabaseName,LoginTime,LastBatch,DateTimeRecordInsertd,NumberOfConnections,Status,HostName,ProgramName,NTusername,Loginame)
 
  SELECT  @@ServerName AS SERVER ,
        name ,
        login_time ,
        last_batch ,
        GETDATE() AS DATE ,
        COUNT(status) AS number_of_connections ,
        status ,
        hostname ,
        program_name ,
        nt_username ,
        loginame
FROM    sys.databases d
        LEFT JOIN sysprocesses sp ON d.database_id = sp.dbid
WHERE   database_id NOT BETWEEN 0 AND 4
        AND loginame IS NOT NULL
GROUP BY name,status,login_time ,
        last_batch,hostname ,
        program_name ,
        nt_username ,
        loginame;

END


--Step3:-Create a Job to schedule to run every 10 Minutes
       --Let it run for Few days/1 week and see the results with below query,Detailed SQL Server Connection Information



--Step4: CHECK results
----count
SELECT  DatabaseName ,
COUNT(status) AS number_of_connections,
ProgramName
FROM    TrackDBConnections
 GROUP BY DatabaseName,ProgramName,Status
 GO

--Details
 SELECT *  from TrackDBConnections
GO

 
   

How licensing works with AlwaysOn Availability groups

I would like to share some knowledge on how licensing works with AlwaysOn Availability groups.
It took 1 week to figure it out, Having calls with Microsoft and help from forums


Min Requirements for having AlwaysOn Availability groups.:

• Two Windows Servers 2012 R2 Standard Edition
• Ensure that each computer is a Node in a Windows Server Fail over Clustering (WSFC)
• All two Windows Servers should reside on one Cluster and One Domain
• Each SQL Server Instance involved in AlwaysOn Availability Groups must be running Enterprise Edition of SQL Server 2012 or later (2014)
• Must use same network links for communications between WSFC members and availability Replicas
• All Databases should be in Full recovery model, only User databases can participate in AlwaysOn Availability Groups 
• Recommended to keep all configurations/Settings/Drives identical between two nodes

Advantages of using AlwaysOn Availability Groups:

• AlwaysOn Availability Groups build on Top of Windows Server Fail over Clustering (WSFC)
• Automatics Failover(Synchronous mode), No witness server needed for automatic failover, uses Windows Server Failover Clustering to do Automatic Failover
• Can have multiple copies of secondary, but here at FrontPoint we are not going to use secondary
• Backups and DBCC Check DB operations cane be done on secondary( but need to pay for License)
• Applications connect to Virtual Server Name any given time, and we do not need to change any in connection strings even after Failover happens 
• Secondary Replica can be same or different location 
• More than one secondary and cane be readable and run synchronous mode 
• Multiple database failover -AlwaysOn Availability Groups ensures all databases are connected when failover happen to secondary Replica
• No shared Storage is required in AlwaysOn Availability Groups 

AlwaysOn Availability Groups Support Fail over In any of the following  Events

• Operating System goes down
• SQL Server Instance, Database goes offline/Shutdown
• Disk Failures(hardware failures) in which databases cannot be accessed 
• Network goes down or someone unplug Network cables
• Manual failover whenever needed in emergency situations
• Upgrade/Patch Failed/Restarting SQL services 

Manage Jobs and Replication:

• Jobs should be created on both primary and secondary Replicas with some logic.
• Jobs run on both servers all times, but which ever is primary actually execute the job and secondary will not pass validation Skip the job to execute the actual code
• Replication Jobs will be Created on Both server and have to create publications on both servers and configure Distribution database to use remote distributor

IF ( SELECT ars.role_desc

 FROM   sys.dm_hadr_availability_replica_states ars

           INNER JOIN sys.availability_groups ag ON ars.group_id = ag.group_id

         WHERE  ag.name = 'YourAvailabilityGroupName'

         AND ars.is_local = 1

   ) = 'PRIMARY‘

BEGIN

---this server is the primary replica, Put all Job code here 

 END;

ELSE

BEGIN

PRINT 'no';    -- this server is not the primary replica, (optional) do something here

    END;

Finally, How licensing works with AlwaysOn Availability groups ?

 

I am going to explain how it works when we have like below environment

SQL Server 2014 Always ON Availability Groups between 2 nodes(servers) and using SQL server Enterprise edition on top of WSFC, Node1 is primary replica(read/Write)  and Node2 is secondary replica(NON READABLE)  and databases are Restoring mode only and will not use or read/write  data until disaster (failover)happens.

As per documentation , SQL server  Services, Agent  services should run on both servers at anytime but only Primary Replica's  jobs executes at anytime using Some checks like i provided above t SQL script, so we should have JOBS running at both servers, we will have to put a logic inside the jobs , if this is primary execute else come out of the job..--this code or logic make sure the jobs run on PRIMARY REPLICA (now, or after failover)

so we need only 1 SQL SERVER ENTERPRISE Licence to setup ENVIRONMENT like above.....

Note:

1.You probably shouldn't take my word for it, since I don't work for Microsoft, but I'm pretty sure that isn't a problem, You need to have the server ready to take over for any failure. Having your jobs already configured and scheduled is an important part of that.

2.Passive fail-over rights permit a customer to run passive instances of SQL Server in a separate operating system environment (OSE) without requiring the passive server to be licensed for SQL Server. A passive SQL Server instance is one that is not serving SQL Server data to clients or running active SQL Server workloads.e over for any failure. Having your jobs already configured and scheduled is an important part of that.

Some Useful Information:

For each server licensed with SQL Server 2014 and covered by active SA, customers can run up to the same number of passive failover instances in a separate, on-premises OSE to support failover events.

A passive SQL Server instance is one that is not serving SQL Server data to clients or running active SQL Server workloads. The passive failover instances can run on a separate server. These may only be used to synchronize with the primary server and otherwise maintain the passive database instance in a warm standby state in order to minimize downtime due to hardware or software failure.

The secondary server used for failover support does not need to be separately licensed for SQL Server as long as it is truly passive, and the primary SQL Server is covered with active SA. If it is serving data, such as reports to clients running active SQL Server workloads, or performing any “work”, such as additional backups being made from secondary servers, then it must be licensed for SQL Server. 

http://download.microsoft.com/download/B/4/E/B4E604D9-9D38-4BBA-A927-56E4C872E41C/SQL_Server_2014_Licensing_Guide.pdf

So long as the agent jobs only actually perform "work" on the primary, and the other conditions are met, it would be passive. 

Thanks

Naresh DBA

Difference between NORecovery vs STANDBY

 
RECOVERY is the normal and usual status of the database where users can connect and access the database (given that they have the proper permissions set up).

NORECOVERY allows the Database Administrator to restore additional backup files such as Differential or Transactional backups. While the database is in this state then users are not able to connect or access this database.

STANDBY is pretty much the same as NORECOVERY status however it allows users to connect or access database in a READONLY access. So the users are able to run only SELECT command against the database. This is used in Log Shipping quite often for reporting purposes. The only drawback is that while there are users in the database running queries SQL Server or a DBA is not able to restore additional backup files. Therefore if you have many users accessing the database all the time then the replication could fall behind.

SQL Server Find What Jobs Are Running a Procedure

SELECT j.name
  FROM msdb.dbo.sysjobs AS j
  WHERE EXISTS
  (
    SELECT 1 FROM msdb.dbo.sysjobsteps AS s
      WHERE s.job_id = j.job_id
      AND s.command LIKE '%SPNAME%'
  );

or to get more details:
SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sJSTP].[step_uid] AS [StepID]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[subsystem]
        WHEN 'ActiveScripting' THEN 'ActiveX Script'
        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
        WHEN 'PowerShell' THEN 'PowerShell'
        WHEN 'Distribution' THEN 'Replication Distributor'
        WHEN 'Merge' THEN 'Replication Merge'
        WHEN 'QueueReader' THEN 'Replication Queue Reader'
        WHEN 'Snapshot' THEN 'Replication Snapshot'
        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
        ELSE sJSTP.subsystem
      END AS [StepType]
    , [sPROX].[name] AS [RunAs]
    , [sJSTP].[database_name] AS [Database]
    , [sJSTP].[command] AS [ExecutableCommand]
    , CASE [sJSTP].[on_success_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: '
                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
                    + ' '
                    + [sOSSTP].[step_name]
      END AS [OnSuccessAction]
    , [sJSTP].[retry_attempts] AS [RetryAttempts]
    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
    , CASE [sJSTP].[on_fail_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: '
                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
                    + ' '
                    + [sOFSTP].[step_name]
      END AS [OnFailureAction]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [sJSTP].[job_id] = [sOSSTP].[job_id]
        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [sJSTP].[job_id] = [sOFSTP].[job_id]
        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
WHERE [sJSTP].[command] LIKE '%SPNAME%'
ORDER BY [JobName], [StepNo]

why Distribution on SQL Express Edition is not possible

SQL server Distribution which act as intermediate and ruin jobs cannot be on SQL Express edition as SQL Express edition cannot have SQL Agent.

decrypt Stored procedure

Create SP with encryption:
CREATE PROCEDURE Test
WITH ENCRYPTIONAS
SELECT TOP 10 CityFROM Person.Address
GO


Now we cannot view the Definition of SP 



To view the definition of the view:I have downloaded below one and it worked excellent...

https://www.devart.com/dbforge/sql/sqldecryptor/download.html



Thanks to devart

Find if sql server uses SSL Certificate

You can use below scripts to find out if any sql server is using  SSL certificate 

SELECT session_id, encrypt_option,*

FROM sys.dm_exec_connections

select * from sys.dm_exec_connections where encrypt_option = 'TRUE'

Both above scripts let u find out if SSL certificate is used or not.

If yes, the get the session_ID and check which application is being used.

sp_wHO2'ACTIVE'

DBCC INPUTBUFFER(51)

Tuning areas where we need to Look

Tuning areas where we need to Look

PAGEIOLATCH—DISK

CXPACKET—Big Queries

SOS_Scheduled_yield—Lots of functions may be missing Indexes

LATCH_EX—Parallelism or missing Indexes 

Get SQL Info in detailed

 SELECT
 @@SERVERNAME AS ServerName,
 'SQL Server Year '
        + CASE SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')),
                         1, 2)
            WHEN '8.' THEN '2000'
            WHEN '9.' THEN '2005'
            WHEN '10' THEN '2008'
            WHEN '11' THEN '2012'
          END AS SQLServerReleaseName ,
        SERVERPROPERTY('ProductVersion') AS ProductVersion ,
        SERVERPROPERTY('ProductLevel') AS ProductLevel ,
        SERVERPROPERTY('Edition') AS Edition;
GO

remove special characters from string in sql server

DECLARE @str VARCHAR(400)

    DECLARE @expres  VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!]%'

      SET @str = '(remove) ~special~ *characters. from string in sql!'

      WHILE PATINDEX( @expres, @str ) > 0

          SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres,@str ), 1 ),''),'-',' ')

      SELECT @str

find out expired credit cards with MM/YY format

I have a table which stores expiry dates of credit cards

ExpiryDate Varchar(10) --datatype

The dates look like this:

'08/10'
'09/11'
'08/16'
'10/17'

how can i find out if a card's date is expired?

ANS: 

SELECT * from Table where isactive=1 
 AND LEFT(ExpiryDate
 
, 2) >= MONTH(GETDATE())
        AND RIGHT(ExpiryDate
 
, 2) >= RIGHT(YEAR(GETDATE()), 2)