Tuesday, February 25, 2014

Contained Databases is GooooD

Hi Guys,
Let's check out the contained database feature today. The contained database user's can be based on both sql and windows based. They need not be mapped to any login and the best part which I liked is that the application user will have absolutely no idea about the server it's running on.

Creating a Contained Database

To start with the Contained database you will have to first configure the Contained database option.

exec spconfigure 'contained database authentication',1

Now we will be ready to create a partial contained database (No Clue why they call it partially contained). So let's fire the create database command.

Create database Contained
containment =partial

This will create a database for us which will be contained. Now lets go ahead and create an object (i.e a table) in this database and put in some values.

USE [COntained]gocreate table conTab(name char(100))
insert into contab values ('tushar'), ('kusum'),('swati'), ('shashank')
go Now lets go ahead and create a windows user for this database.
USE [COntained]goCREATE USER [eapac\etuskanadm] WITH DEFAULT_SCHEMA=[dbo]goexec sp_addrolemember 'db_owner','eapac\etuskanadm'
 Now this user will be comtained in the database itself as it is not mapped to any login and this user will have no server level information. The below image shows how the connection to the Server/Database will look like. Here while connecting using ssms you have to addtionally go to the options in the ssms and there type the database name.

You can also connect through command line you have to use the -d paramter in the connection to connect to the database.

sqlcmd -E -Slocalhost -dCOntained

Tushar Kanti


Thursday, February 20, 2014

Working with command line

Hi Guys,
There has been times when I have heard people saying MS SQL Server as a right click database.
Well first answer to them is we have a GUI provided by the base OS and the RDMS client tools are intelligent enough to exploit them. Too bad they dont have the GUI to handle tasks efficiently.
Comming back to command line we definitely can do everything any RDMS can through command line.
 I started working on command line like 2 years ago and have now experienced that working on command line not only makes your work blazing fast but also you get to know the internal commands much more than when you use a GUI. The idea of working on command line was not new to me but then it was the challenge that I faced while working on the heavy GUI which is pretty cool as far as functionality is related but then it makes the normal administration work a little slow specifically when you are geographically placed  in a distant location from you actual sever. So command line comes very handy in situations when the connectivity is really slow but then you should be ready and equipped to work on the command line. So best is start preparing scripts that will help you in a crunch scenario and then its really feels geeky while you are working on the command line. So its kinda catchy there.

All I am saying if I am saying anything at all is that you should prepare your scripts and start using them on a day to day basis before you faced with any crunch situation.

Well to start off you can install SQL Server on a Windows Core to get the real feel of working on command line.

To start with you can start/stop the SQL Server related services from cmd or powershell.
CommandLine (CMD)
  • Net start mssqlserver (Net start mssql$InstanceName for named instance)
  • Net stop  mssqlserver (Net stop mssql$InstanceName for named instance)
  • Sc
  • Start-Service MSSQLSERVER  (Start-Service 'MSSQL$instancename' )
  • Stop-Service MSSQLSERVER (Stop-Service 'MSSQL$instancename' )

to be continued...

Tushar Kanti

SSMS Keyboard shortcut not working!!!

hi Guys,
Was facing the keyboard shortcut issue not working since logn so decided to run through the options in the SSMS today and identified how to fix that. Sharing the same with you/.

Tools-> Options -> Environment -> Keyboard -> Reset -> ok

This was the SSMS for SQL Server 2012 but I hope most of the versions will be the same from SQL Server 2005.

Tushar Kanti

Thursday, February 6, 2014

Harpoon for SQL Server DBA's

Hi Guys,
It's been sometime now that I have spent in this RDBMS world and I know there is still plethora of things still waiting for me in here. But today I thought of compiling my tools in One Place so that it's one stop destination for DBA's working on Production Issues.

I  will try to put all the counters and values or the queries required for looking into Production Issues. I will also try to document the significance of a parameter. I will alter/customize the content depending upon the feedback I have from the guests.

I will start with places I have the most issues and try to fight my fears before the D Day so that I don't have a cold feet.

Classic Question : What do I do if my Query/Report is responding slow and taking more time than ever?
Answer: There is never a right/wrong answer here. The only thing important is your approach and the answer will come eventually. The doctors first tries to identify the symptoms and then does some test and based on his experience he judges what is wrong with the patient. If the doctor has not faced the disease earlier then he will not be in a situation to prescribe any medication nor will he be able to tell what's wrong with the patient.
So the Answer will be two fold what are the parameters you will try to judge the situation on and the analysis you will do based on the data you have.
Now first the parameters : There are a lot of parameters you can look into starting from PLE ,Page Fault,Disk Write/Sec, Disk Queue length, Processor Queue Length, etc.. The list if almost endless

Processor Queue Length:
How will it help? This is a parameter which will give the awareness that whether the CPU in the system is adequate or you need more CPU to be added.
Value : The Processor Queue Length should be 0.
Significance : The Processor Queue Length as the name suggests is a parameter which tells us if there are any processes in queue waiting for the Processing. So if the Value is 0 then there are no processes waiting to be catered. You need not worry even the CPU utilization is very high if the Processor queue length is 0. What a I mean to say is there is no performance hit yet. Definitely a thing to work upon why is the CPU going high.

Disk Queue Length:

ok.. first things first.. I apologize for not working on this blog for long long time now. But then today I thought I will ink some of the important things I need to revise before the interview calls. :) 

Let Start with Mirroring: 
I know it's an old school technology and we should switch to always on like asap but then hold on to your horses causes the questions are not stopping to come very soon. 

So 1st question which is the table which will show you the redo queue for the mirroring. 

SELECT  db_name(database_id) as DatabaseName       ,[retention_period]      ,[time_behind]
      ,[enable_time_behind]      ,[send_queue]      ,[enable_send_queue]      ,[redo_queue]      ,[enable_redo_queue]      ,[average_delay]      ,[enable_average_delay]
  FROM [msdb].[dbo].[dbm_monitor_alerts]  order by [database_id]

So basically the msdb.dbo.dbm_monitor_alerts table will have the key to your performance configuration of the mirroing setup.

SELECT  db_name(database_id) as DatabaseName      ,[role]      ,[status]      ,[witness_status]
      ,[log_flush_rate]      ,[send_queue_size]      ,[send_rate]      ,[redo_queue_size]      ,[redo_rate]
      ,[transaction_delay]      ,[transactions_per_sec]      ,[time]      ,[end_of_log_lsn]      ,[failover_lsn]
      ,[local_time]  FROM [msdb].[dbo].[dbm_monitor_data]  order by [database_id], [local_time] desc

Now msdb.dbo.dbm_monitor_data will have the answer to your redo queue size 

OR use sp_dbmmonitorresults database_name       , rows_to_return        , update_status 
which will give you all the information you can seek for :)

There will another questions related to setup as well and I would like to address the most interesting one as of now but I promise to update this more soon. 
What is the process of failover for asyncronous mode in the mirror server 
use master; 
alter database set partner  force_service_allow_data_loss

ok.. lets now look at replication questions we might encounter 

Exec sys.sp_replmonitorrefreshjob @iterations = 1;
we need to run this proc to update all the data in the distribution database monitoring tables.

exec sp_replmonitorsubscriptionpendingcmds   @publisher ='Enter publisher server name',
 @publisher_db = 'Enter publisher database name', @publication ='Enter name of publication',
 @subscriber ='Enter subscriber server name', @subscriber_db ='Enter subscriber database name',
 @subscription_type ='0' --0 for push and 1 for pull 

The MSrepl_transactions table contains one row for each replicated transaction.
The MSrepl_commands table contains rows of replicated commands
The MSrepl_errors table contains rows with extended Distribution Agent and Merge Agent failure information. 

Okay all for Indexes on this page 

Al Time Favourite !!! Will post on the lines of Paul Randal sometime sson. May be getting Lucky Tonite !! 

Wednesday, October 19, 2011

Database Refresh

Off late I have been doing a lot of db refresh on adhoc basis but I tend to do some mistakes each time. So I thought of tabulating the steps in one central point. I hope some of you can also benefit from it. Below mentioned are logical steps and some scripts to make things easier.

Steps for refresh:-
>take backup from prod
>zip the bakcup
>move the zip to UAT server
>unzip the backupfile
>take backup of the database on UAT and keep it in a separate folder just in case of roll back

[restore filelistonly from device ] and [sp_helpdb ] and check that both have matching files.
>script out the roles access rights on UAT database
>script out the user access rights on UAT database
>kill all user connections on the database on UAT
>restore the database on UAT
>delete the users exect dbo,sa,guest and other
>run the user access script
>use sp_change_users_login 'report' to generate the orphaned logins
>run sp_changedbowner 'sa'
>change the recovery plan of the UAT back to its original as the Prod may have a different recovery plan.
>send mail to use rto verify the database access for a couple of logins

That should be basic steps. I will soon update the script to get the access list for the users in a database.

Script for users and user access :

set nocount on

-- users created for logins and with their defualt schema
select 'CREATE USER ['+p.name +'] '+
case when l.name is not null then ' FOR LOGIN ['+l.name+'] ' else ' WITHOUT LOGIN ' end +
case when p.default_schema_name is not null then ' WITH DEFAULT_SCHEMA = '+p.default_schema_name +' 'else ' ' end + CHAR(13)+CHAR(10)+ 'GO '
from sys.syslogins l
right outer join sys.database_principals p on p.SID=l.SID
where (p.type='S' or p.type ='U')

--roles related to users in database
select 'Exec sp_addrolemember ' + ''''+prole.name+''''+','+'''' +puser.name +'''' + CHAR(13)+CHAR(10)+' GO'
from sys.database_role_members r
join sys.database_principals prole on r.role_principal_id= prole.principal_id
join sys.database_principals puser on r.member_principal_id= puser.principal_id
order by 1

set nocount off


Hope this helps.I am still missing the grants in the script. Will work on that today and add it to the script.


Tuesday, September 20, 2011

Linked Server

Now there was this issue from one the developers when they are trying to connect to a database through the linked server it errors out. I tried to login and fire the same query it ran so I asked him to check again with the proper credential. He was using his windows login which was part of some windows domain group which I was unable to resolve. So I asked him to use the login the application will actually use. He tried that but there was no lock. Now I looked into the configuration of the linked server it was set to use the login's current security content. User was trying to run a proc which was calling a table in the linked server. So while he tried to execute this he forgot to check if the login actually also existed in the linked server as well. I identified that the login in the linked server was missing. We changed the security content to use a paricular login for the linked server to fix this.


Thursday, August 25, 2011

Lets Start with Kid's Stuff..

How to delete a login which has users mapped to different databases?
Well you can drop a login to which database users are mapped but this will create orphaned users.

Well to begin with we have to know the name of the login name from syslogins table in case you are not sure abpout the login name.

query : select name from syslogins where name like '%%'

Now we got the login name to begin with and then we can get all the user databases the login has access to with help of sp_helplogins
query: Exec sp_helplogins 'loginName'

Now we need to delete the user from the different databases.
We will use the sp_dropuser to delete the user from the databases.
query : EXEC revokedbaccess ''
remeber this name is the username in the database corresponding to the login.

Now after deleting all users from all the databases. We need to delete the login as well. You can do that from the GUI or use the script.

script: Exec sp_droplogin ''

References: MSDN