pasumarthysqldba.blogspot.com: 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

pasumarthysqldba.blogspot.com

Saturday, 7 January 2017

SQL DBA ACTIVITYS

No comments:

Post a Comment