Monday, September 22, 2014

Fastest way to row count all tables in a Database

--Full Database
SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

-----------------------------------------------------------
-- For Individual Schema**
SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
      
    
      AND QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))='[YOURCLAIMNAMEHERE]'
     
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

SQL Server agent Job Owner are set to users those no longer with the organization

The jobs may start failing when their AD accounts are removed. If the users have left it is better to change the job owner to a different account.
To ensure such issues wont happen, it is better to create a generic AD account with only necessary permissions and use it exclusively for this purpose.

List down all the jobs which are tied to the users and share it with all the respective project managers and technical architects and get a approval .
Decide on a common user account and create new one .

How to get elapsed time i from start of current day

How to get elapsed time in Seconds, Minutes, Hours from start of current day?


SELECT  DATEDIFF(SECOND, 0, Cast(GETDATE() as time))  -- In Seconds

SELECT  DATEDIFF(MINUTE, 0, Cast(GETDATE() as time))  -- In Minutes

SELECT  DATEDIFF(HOUR, 0, Cast(GETDATE() as time))      -- In Hours

Checking Email log in SQL Server

--SENT MESSAGES LOG
SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_sentitems]
ORDER BY [send_request_date] DESC

--FAILED MESSAGES LOG
SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_faileditems]
ORDER BY [send_request_date] DESC

--ALL MESSAGES – REGARDLESS OF STATUS
SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_allitems]
ORDER BY [send_request_date] DESC

Thursday, September 18, 2014

SQL Server Endpoint







Configuring endpoint security





A SQL Server endpoint is a door opened from or to SQL Server. Everything that can go from your SQL Server to the network, or from the network to SQL Server, goes through an endpoint. Endpoints can be system or user-defined. The system endpoints allow the usual T-SQL connections to SQL Server to send queries.
The endpoints are defined for a specific protocol, which were either HTTP or TCP in the
previous versions. In SQL Server 2012, the HTTP endpoints previously used for the native
web services feature are removed. Now you only have TCP endpoints.
You can create a user-defined endpoint for three purposes: TCP requests, service broker, or
database mirroring. Here we will talk about the first one, which is useful to set up a dedicated
and secured connection to SQL Server for administrative purposes or distant access.


How to do it...


To configure endpoint security, follow these steps:
1. In a SQL query window, type the following T-SQL command:
CREATE ENDPOINT myTSQLEndpoint
STATE = started
AS TCP (
LISTENER_PORT = 8080,
LISTENER_IP = (127.0.0.1)
)
FOR TSQL ();
2. You will receive a message saying that it will cause the revocation of any Public
connect permissions on the TSQL default TCP endpoint. This effectively means that
all logins will lose their permission to connect to SQL Server through the default
T-SQL endpoint. If you still want to allow connection permissions to the default TCP
endpoint, issue the following command:
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];
3. You can view the state of the endpoints with this command:
SELECT * FROM sys.tcp_endpoints;
Chapter 1
45
4. You can start or stop the endpoints with the ALTER ENDPOINT command.
For example, you can stop the default TCP endpoint as follows:
ALTER ENDPOINT [TSQL Default TCP]
STATE = STOPPED;
The state we just creat@ed will remain even after a service restart.




How it works...





When SQL Server is installed, a system endpoint is created for each network protocol used
in SQL Server. The permission to access these endpoints is given to the Public server role.
Every login declared in SQL Server is a member of this role, and permissions on the Public
server role can be changed, unlike other fixed server roles. You can grant, revoke, or deny
permissions to connect to an endpoint to all the logins through the Public role, or to specific
logins by revoking CONNECT permissions to the Public role, and by granting specific privileges
as follows:
REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [a_specific_login];
If you want to allow connections to SQL Server from only a specific client IP address, you can
stop the default endpoint, or deny access to it, and create a user-defined T-SQL endpoint, with
a client IP address and a TCP port.
Stopping default endpoints has the same effect as disabling them in SQL Server
Configuration Manager.




There's more...



In SQL Server 2012, you can create user-defined server roles. We will detail this functionality
later. This interests us for now, because a server role could be used to grant CONNECT
permissions on an endpoint to a group of logins.
The following code creates a user-defined server role, adds a login as a member, and grants
the CONNECT privilege on the default TCP endpoint to the role:
USE [master];
CREATE SERVER ROLE [TCPRole];
ALTER SERVER ROLE [TCPRole] ADD MEMBER [my_login];
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [TCPRole];





Reference: Microsoft SQL Server 2012 Security
Cookbook

AES algorith is stronger algorthm than 3DES/Triple-DES

AES is an algorithm adopted as a Federal U.S. government standard in 2002 and approved
by the NSA. It is a stronger algorithm than Triple-DES. When you choose the algorithm,
you need to balance between security and performances. AES_128 is gradually becoming
more vulnerable as new attacks are discovered but it is still considered reasonably safe. If your database contains classified
information, you should go for a bigger key, which is harder to crack. But the bigger the
key, the higher the performance impact will be. This being said, the performance impact
of TDE is relatively low.


Reference: Microsoft SQL Server 2012 Security Cookbook

List of services started automatically by Windows that you could consider stopping

# DHCP Client: On most servers, IP addresses are fixed and DHCP is not needed.

#DNS Client: This caches DNS names locally.

#Network Location Awareness: This collects and stores the configuration information
for the network and notifies the programs when this information is modified. You can't
disable it unless you use the Windows 2008 Advanced Firewall.

#Print Spooler: This loads the files to the memory for printing later. It can be disabled
if you don't print from the server.

#Windows Error Reporting Service: This allows errors to be reported when programs
stop working or responding, and allows existing solutions to be delivered. It can safely
be disabled.

#Windows Firewall: You can disable this service, if you are using a network firewall.

#Shell Hardware Detection: This activates AutoPlay for removable devices.
On Windows Server 2008 R2, this service stops when nobody is logged in,

to minimize risks. You can disable it.

Reference: Microsoft SQL Server 2012 Security
Cookbook