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

Powershell

• 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.


Thanks,
Tushar Kanti
 

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 
https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

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

Database Refresh

Hi,
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

>run
[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.






Tushar

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.


Tushar

T-Replication 101

Well there have been a lot of articles for starting with transactional-replication. But there have been rarely an article for fixing the common issues one runs into. That's the motivation for this article. Actually I had also run into these issues a couple of times but used to drop the whole replication and bring it up again. But then one fine day I decided to find out the basics and try to help others on this.

After configuring the distribution, publication and subscription when you start the replication monitor you may encounter the below mentioned error.

Error authenticating proxy tushar-hub\lifercks, system error: Logon failure: unknown user name or bad password.). The step failed.

Well the reason may be you have put a windows account with some wrong password. Well the immediate fix can serve as you go to the agent job and in the step('Run agent') where the agent starts; use the 'Run As' in the property and change to run under the sql server service account. This should fix the issue. I am presuming here that your sql server agent service account is running under a domain account with all security checks.

http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/42229a2c-a9ef-4082-806c-3381e9f6d510

The next most common error will be the replication is failing.
Error :
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)

The issue is some application/user updated/deleted/inserted some row directly in the subscriber. Then when there is a change in publisher and the distributor tries to push those changes into subscriber the error shows up as there is inconsistancy between subscriber and publisher.

To fix this there are a couple of steps we have to follow.
1. To find the sequence number and then the articles involved in the mismatch.
2. Find the data miss match

Now to find the lsn number we will use the query mentioned below.

select * from distribution..MSrepl_errors with(nolock) where time> '20110921' order by time desc

here '20110921' is the time stamp from when we suspect the error has occured.

Now when we have the sequence number we fire the next query to identify the objects which are in suspect.

exec distribution..sp_browsereplcmds @xact_seqno_start='0x00023C6800000BA3000C00000000', @xact_seqno_end='0x00023C6800000BA3000C00000000'

here 0x00023C6800000BA3000C00000000 is the lsn number.

Now since we have the objects we can use the table diff utility to find the actual data issues we have



to be continued.

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

Tushar