Tuesday, 10 January 2017

Interview questions and answers

---------------------------------------------------------------------------------------------------------------------------
Interview question : How DIFFERENTIAL Backups works internally
---------------------------------------------------------------------------------------------------------------------------
Answer :
A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential

Internally when you run a differential database backup it will backup all extents which has changes since the time last full backup was run. SQL Server stored data in 8KB page and an extent comprises of 8 of such 8 KB pages equaling to 64KB of data. Whenever any data changes within a 8KB Page a flag is turned on to let SQL Server know that if at all a differential backup is initiated then it should include the data from this extent within the backup. Once a FULL backup is completed the flags are turned off. Therefore, every time you take a differential backup it will backup all the extends which has changed after the last successful FULL backup.

Overview of Differential Backups

A differential backup captures the state of any extents (collections of eight physically contiguous pages) that have changed between when the differential base was created and the when differential backup is created. This means that the size of a given differential backup depends on the amount of data that has changed since the base. Generally, the older a base is, the larger a new differential backup will be. In a series of differential backups, a frequently updated extent is likely to contain different data in each differential backup.

The following illustration shows how a differential backup works. The figure shows 24 data extents, 6 of which have changed. The differential backup contains only these 6 data extents. The differential backup operation relies on a bitmap page that contains a bit for every extent. For each extent updated since the base, the bit is set to 1 in the bitmap.

Differential bitmap identifies changed extents

For more reference :https://msdn.microsoft.com/en-IN/library/ms175526.aspx

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Interview question :What are different possible Mirroring Stats?

-------------------------------------------------------------------------------------------------------------------------
Answer :

          SYNCHRONIZING
          SYNCHRONIZED
          SUSPENDED
          PENDING FAILOVER

DISCONNECTED.

Interview question: What are the few SQL Server DMV are used by DBA

--------------------------------------------------------------------------------------------------------------------------

Answer :

Execution Related
sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_exec_cached_plans
sys.dm_exec_query_plans
sys.dm_exec_sql_text
sys.dm_exec_query_stats
Index Related
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats
SQL Server Operating System
sys.dm_os_performance_counters
sys.dm_os_schedulers
sys.dm_os_nodes
sys.dm_os_waiting_tasks
sys.dm_os_wait_stats
I/O Related
sys.dm_io_virtual_file_stats

Interview question: What is Fragmentation ? How can you detect? If Fragmentation is too high what steps DBA can taken?

---------------------------------------------------------------------------------------------------------------------------

Answer :

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

You can remedy index fragmentation by reorganizing or rebuilding an index.

Fragmentation can be easily detected by running the system function sys.dm_db_index_physical_stats which returns the size and the fragmentation information for the data and indexes of tables or views in SQL Server. It can be run only against a specific index in the table or view, all indexes in the specific table or view, or vs. all indexes in all databases:

The results returned after running the procedures include following information:

avg_fragmentation_in_percent – average percent of incorrect pages in the index
fragment_count – number of fragments in index
avg_fragment_size_in_pages – average number of pages in one fragment in an index

avg_fragmentation_in_percent value	Corrective statement
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

Index Rebuild : This process drops the existing Index and Recreates the index.

USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REBUILD
 GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REORGANIZE.

GO

For more reference :https://msdn.microsoft.com/en-us/library/ms189858.aspx

**By using this query we can identify the fragmentation in SQL SERVER 2008 and above.

SELECT
DB_NAME(idxst.database_id) AS [database_name],
OBJECT_NAME(idxst.object_id, idxst.database_id) AS [object_name],
QUOTENAME(idxif.name) [index_name],
CASE
  WHEN avg_fragmentation_in_percent < 10 THEN 'LOW'
  WHEN avg_fragmentation_in_percent < 30 THEN 'MEDIUM'
  WHEN avg_fragmentation_in_percent < 50 THEN 'HIGH'
  ELSE 'EXTREME'
END as fragmentation_indicator,
idxst.*
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , 'LIMITED') AS idxst -- not using DETAILED or SAMPLED
INNER JOIN sys.indexes idxif ON idxst.object_id = idxif.object_id AND idxst.index_id = idxif.index_id
ORDER BY [object_name], [index_name]
GO

------------------------------------------------------------------------------------------------------------------------------------------------------------------

What is Fill Factor ?

-------------------------------------------------------------------------------------------------------------------------

Answer :

Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made of Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left in the page, when the fill factor is 100.

If the page is completely filled and new data is inserted in the table which belongs to completely filled page, the “page split” event happens to accommodate new data. When new data arrives, SQL Server has to accommodate the new data, and if it belongs to the page which is completely filled, SQL Server splits the page in two pages dividing the data in half. This means a completely filled page is now two half-filled pages. Once this page split process is over, the new data is inserted at its logical place. This page split process is expensive in terms of the resources. As there is a lock on the page for a brief period as well, more storage space is used to accommodate small amounts of data. The argument usually is that it does not matter as storage is cheaper now a day.

Fill factor is usually measured at the server level as well as table level. Below we have the scripts for the same.

Here is the script to measure the Fill Factor at the server level:



SELECT *

FROM sys.configurations

WHERE name ='fill factor (%)'

And, here is the script to measure the Fill Factor at the table/index level:



USE YourDatabaseName;

SELECT OBJECT_NAME(OBJECT_ID) Name, type_desc, fill_factor

FROM sys.indexes

I think this is good logical stop for this blog post today. Thank you!!

Monday, 9 January 2017

SQL DBA ACTIVITY-How to move your SQL Server master Database to another drive

Activity: How to move your SQL Server master Database to another drive?
---------------------------------------------------------------------------------------------------------------------------
Workaround : when you want to move your master database from the default location to some other drive. Now this activity cannot be done with the normal ALTER DATABASE statement with MODIFY FILE option. And you need a special handling for this case of master DB.

Now leave SSMS, and open SSCM i.e. SQL Server Configuration Manager. Here select “SQL Server Service”, and Right Click on the instance of SQL Server, and choose Properties. Now select the Startup Parameters tab.

Here you will see 3 line items:

1. -d is the path of the master data file.
2. -e is the path of the SQL error log file.
3. -l is the path of the master log file.
So, you need to update the 1st and 3rd ones. As I want to move my files to E:\SystemDatabases\Master\ location, so you just need to replace the existing path with following:
1. master data File:
-dC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf
-dE:\SystemDatabases\Master\master.mdf
2. master Log File:
-lC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-lE:\SystemDatabases\Master\mastlog.ldf

–> Now Stop the SQL Server services, by going to: RUN –> services.msc
–> Manually Copy the master.mdf & mastlog.ldf files to the new location
–> Start the SQL Server services.

–> To confirm the new location, just execute following query and check the path:

USE master

GO

SELECT * FROM sys.database_files

SQL DBA issues and solutions

ISSUE: How to Unlock a SQL Login Without Resetting the Password
--------------------------------------------------------------------------------------------------------------------------
SOLUTION :

            SQL Server uses the same password policies as that of the operating system. Password policies include various parameters with regard to password length, when a password gets locked out, and how long until the password expires.

           To see these parameters by checking the local security policy on your server. To do this, go to Start >> Control Panel >> Administrative Tools >> Local Security Policy if you're using the Classic View for Control Panel. Otherwise, go to Start >> Control Panel>> Performance and Maintenance >> Administrative Tools >> Local Security Policy.

          Once inside the Local Security Policy console, expand Account Policies and you should see two subfolders, Password Policy and Account Lockout Policy.

Click on Account Lockout policy>

The Account Lockout Policy folder has the following settings:

Account Lockout Duration - how many minutes before a locked account is unlocked again
Account Lockout Threshold - how many failed login attempts can occur before the account is locked out
Reset Account Lockout Counter After - how long before the failed login attempts are reset to zero (assuming no successful logins have occurred since, since that automatically resets the failed logic count to zero).

If a SQL Server login is configured to use password policy enforcement and the organization uses account lockout after a certain number of failed logins, you can end up locking out a SQL Server login.

If the account gets locked out, unlocking the account is easy to do if you want to reset the password. If you don't however, SQL Server won't let you directly. For instance, simply unchecking the box beside Login is locked out and clicking OK won't work.

Instead, you'll get the following error:

Temporarily take the login out of password policy enforcement by unchecking it, as shown.

This will disable the unlock checkbox.

Click OK to confirm the change and the SQL Server login will unlock. This will permit connections via that login again. If there is a need for the password enforcement to be turned back on, you can do so and it won't re-lock the login. However, in either case be sure you know what caused the lockout in the first place. The reason for having account lockout is to prevent a security breach due to someone brute forcing the password for an account. Disabling password policy enforcement basically means an attacker can try to guess the password.

Activity : How to Changing SQL Server Service Account or Password

--------------------------------------------------------------------------------------------------------------------------

Workaround :

                    As per Microsoft documentation, we should always use SQL Server configuration manager to do any modification for SQL Service. Account change and password change, both should be done only via configuration manager.

                    For service account change we need to restart SQL server service. But if we are only changing the password then there is no need to restart the SQL Service.

                   Keep in mind a bug in SQL Server where if we change the password in clusters on the passive node, SQL services would stop.https://support.microsoft.com/en-us/kb/972387 (SQL Server Failover Cluster Instance is taken offline when you change the password for the service account on the passive node).

we should not use services.msc? Why?

There are valid reasons for that. As per books online – In addition to changing the account name, SQL Server Configuration Manager performs additional configuration, such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings.

The right way…

Go to Start –> All programs –> Microsoft SQL Server <version>
Go to Configuration Tools
Click on “SQL Server Configuration Manager”

4.Within the SQL Server Configuration Manager, go to the “SQL Server Services” node

In the right-hand side pane, you will see all the SQL Server services listed
Simply right-click on the required SQL Server Service and go to “Properties”

3. Next, change the domain service password and click OK

4.Once the password has been changed – there is no need for the SQL Server service to be restarted

If ever you need a service restart:

Let’s say you need a service restart because of a configuration change. Instead of heading over to the Services.msc console, one should be using one of the following options:

Using SQL Server Configuration Manager

Right click on the SQL Server Service
Click on “Restart”

Using SQL Server Management Studio

In the Object Explorer pane, connect to the server in question
Right-click the server name and click “Restart”

3 .Click on “Yes” on the following Message Box that comes up (beware, this message box might appear in the background)

For more reference on how to setup Windows Service accounts for Microsoft SQL Server, please refer: http://msdn.microsoft.com/en-us/library/ms143504.aspx

Until we meet next time.....,

Saturday, 7 January 2017

SQL DBA ACTIVITYS

Hello DBAs,

Hope you are doing good!!

Today am going to post few SQL DBA activity's with solutions.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Activity: How to move your SQL Server tempDB database from its default location to an another drive.
----------------------------------------------------------------------------------------------------------------------
Workaround : Reason could be anything like Disk maintenance, Failure recovery, etc. This can be done by using the normal ALTER DATABASE statement, we will see how.

–> Script: Let’s first check the location of TEMP DB:

–> Now execute the ALTER DATABASE statement with MODIFY FILE option:

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE (
NAME = tempdev, 
FILENAME = 'E:\SystemDatabases\tempDB\tempdb.mdf'
);
GO
ALTER DATABASE tempdb
MODIFY FILE (
NAME = templog, 
FILENAME = 'E:\SystemDatabases\tempDB\templog.ldf'
);
GO

The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.

–> Now Restart the SQL Server services from SSMS Object Explorer or by going to RUN –> services.msc.
As soon as the SQL Server services are started, the SQL engine re-creates the tempDB database files. So there is no need to physically move them to the new location.

–> To confirm the new location, just execute following query and check the path:

---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------

Activity : How can we change Static Port to Dynamic Port ?
---------------------------------------------------------------------------------------------------------------------------
Before going to start activity , we need to know bit clarity about Ports?

If you configure an instance of SQL Server to use a static port, and you restart the instance of SQL Server, the instance of SQL Server listens only on the specified static port. The SQL Server clients must send all the requests only to the static port where the instance of SQL Server is listening.

By default, the default instance of SQL Server listens for requests from SQL Server clients on static port 1433. Therefore, the client network libraries assume that either port 1433 or the global default port that is defined for that client computer is used to connect to the default instance of SQL Server.

The default instance of SQL Server does not support dynamic port allocation. However, the named instances of SQL Server support allocation of both static and dynamic ports. By default, a named instance of SQL Server listens on a dynamic port.

Only named instances of SQL Server can use the dynamic port allocation process. In the dynamic port allocation process, when you start the instance of SQL Server for the first time, the port is set to zero (0). Therefore, SQL Server requests a free port number from the operating system. As soon as a port number is allocated to SQL Server, SQL Server starts listening on the allocated port.

Workaround :

DETERMINE THE TCP/IP PORT NUMBER OF THE INSTANCE OF SQL SERVER.
SQL SERVER 2005

1. Open SQL Server Configuration Manager, and then expand SQL Server 2005 Network
Configuration.
2. Click Protocols for InstanceName, and then double-click TCP/IP in the right panel.
3. On the Protocol tab, notice the value of the Listen All item.
4. Click the IP Addresses tab:
• If the value of Listen All is yes, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item under IPAll.
• If the value of Listen All is no, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item for a specific IP address.

>>Owing to security issues it is often not recommended to use the default port number 1433 for communicating with the SQL server. This article helps set up the SQL server to use a non-standard port number.

1.Run the SQL Server Configuration Manager.
2.Select the SQL Server Network Configuration.

3.Select from the list the instance you want to configure to listen to on a specific port.

4.To change the port assignment right-click on the TCP/IP protocol and select Properties.

5.Click on the IP Addresses tab.

Note: Both IP5 and IP6 are disabled and the TCP Dynamic Ports setting is set to “0”, which means that the database engine is listening on dynamic ports. This instance currently uses port number 1433.
Specify the port number you want to use instead of 1433 by entering the preferred port number. In this case the new port number is going to be 8181. Also, turn off the dynamical port number setting by removing the “0” mark in the TCP Dynamic Ports field.

7.In order to finish the adjustment, select SQL Server Services, click on SQL Server and restart it.

8.Once cross check if Dynamic port is being used or not using T-SQL :

SELECT NAME
    ,protocol_desc
    ,type_desc
    ,state_desc
    ,is_admin_endpoint
    ,port
    ,is_dynamic_port
    ,ip_address
FROM sys.tcp_endpoints

Steps for Database Refresh

1. Before doing a database refresh from one environment to the other we need to reach out to all the users of the database and keep them informed so that the refresh does not affect their work and any data changes they are working on.

2. After obtaining enough permissions and none of the users are connected to the database we need to place the database in single user mode using the below command.

ALTER DATABASE ProdDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

3. We need to take a full backup of the database before refreshing, so that any data is not lost  if required again.

         BACKUP DATABASE ProdDB
         TO DISK = 'E:\PASUMARTHY\MSSQL\BACKUPS\ProdDB_FULL_BKP_JAN082017_12.40AM.BAK'

4. Restore the database with an appropriate backup from the other environment.

         RESTORE DATABASE DevDB
         FROM DISK = 'E:\Destination\MSSQL\BACKUPS\ProdDB_FULL_BKP_JAN082017_12.40AM.BAK'
         WITH REPLACE,STATS = 10----------------- percentage complete after each 10% segment.
         Move 'DevDB' to 'E:\Destination\MSSQL\Data\DevDB_Data.mdf',
         Move 'DevDB_Log' to 'O:\Destination\MSSQL\Log\DevDB_Log.ldf'
If required ,we can use below query for find how % to complete the restoration

select * from sys.dm_exec_requests

5. Once the database restore has been completed successfully we need to check for the orphan users if any so as to avoid login issues for any users of the database by using the below command.

USE DevDB

EXEC sp_change_users_login 'report'

6. Then we need to fix the orphan users using the below command so that all the users are mapped to an appropriate login and do not have any issues w.r.t logging into the server.

USE DevDB

EXEC sp_change_users_login 'Auto_Fix', 'LoginName'

7. Need to ensure that all the users of the database are mapped to appropriate logins and then place the database in multi user mode using the below command.

        ALTER DATABASE ProdDB
        SET MULTI_USER;

8.      Check the version compatibility level and the disk space also.

9. Keep all the users informed that the database is ready for use and check if any users still facing any issues with logging into the database.

THANKS FOR READING MYBLOG.... WILL POST MORE UPDATES....KEEP VISITING

Sunday, 1 January 2017

HAPPY NEW YEAR

Dear Friends A Very Warm Welcome New Year...

Thursday, 29 December 2016

SQL DBA : Real time interview questions few more...

Hi DBA Aspirants,

Hope you guys are doing well!!.

Let`s begin the new year with fresh thoughts ,I hope first week in the month of JAN-2017 the corporate hiring process (freezing) will be resume. So its good time to switch company for better career growth and more Salary .

Now am continue to post few more real time interview questions . Which I have faced early in my career .I hope my blog will be help you guys for who are looking for change another company and I believe this blog will help for only experts not learners . Because so far i have posted only interview questions not for solutions. Because of expert people to know answers but don't know thing is recent interviewer are focusing which areas .

My opinion is to share my real time experience and it is not copy pasted things. Thanks for understand.

I hope and surely , my up coming posts will be share useful scripts . Thanks for the time.

Have a quick look::

1)what is fragmentation? How can you handle fragmentation is too high?
2)what is the functionality of algebrizer in SQL SERVER?
3)Dirty read?
4)types of pages and usages?
5)msdb special permissions?
6)what is the size of TDS?
7)what are the wait types in sql server?
8)What is the silent installation?
9)what are the regular Dynamic Management Views used by DBA?
10)Importance of UPDATEUSAGE ?
11)Default MDF and LDF file size?
12)how to fix orphaned users in sql server?
13)what is the functionality of dB backup operator?
14)what are the pre-prerequisites for before the migration processes ?
15)Importance of perfmon?

Thank you for reading my blog and kindly celebrate new year safely by planning with lot of Joyeeee..
Once Again Thank You!!

Saturday, 17 December 2016

Real time interview questions :: Sample

SQL DBA : Have a quick look for Real time interview questions :: Sample

1)How can we Move tempDB to another drive ?Is it possible, If yes how?
2)how the Differential backup works internally?
3)What is the use of FILL FACTOR settings and impact on performance of the BOX?
4)How page split impact on server performance ?Brief
5)CPU utilization is high (100%) how you trouble shoot in this context.
6)TEMPDB Full , how can you handle?
7)What is the difference between SQL Server clustering methods: - Active/Active - Active/Passive
8)What is the alive & Looks alive
9)What is Quorum ?Why Quorum is necessary ?
10) Unable to start SQL Server, what is the reason, how to find issue?
11)Log file is corrupted (there is no backups, how u will get database?
12)How to recover a Database in suspect mode?
13)How to find long running queries?
14)How to transfer Logins?
15)SQL Server Agent Service is not working?
16)While taking full backup can we take transaction log backup?
17)How do database checkpoint occur?
18)Difference between Checkpoint and Lazy writer?
19)What is SQL service broker?
20)Define acid property in SQL Server?

Thank you for reading my blog, I will post in my next blog for more usefull real time questions....,Once Again Thank You!!