Static and Dynamic port in sql server

Posted on February 5, 2010 by uday arumilli

Static and Dynamic port in sql server


STATIC PORT ALLOCATION

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.

However, if an instance of SQL Server is configured to listen on a static port, and another program that is running on the computer is already using the specified static port when SQL Server is started, SQL Server does not listen on the specified static port.

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.

DYNAMIC PORT ALLOCATION

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.

The allocated port number is written to the Windows registry. Every time that you start that named instance of SQL Server, it uses that allocated port number. However, in the unlikely case that another program that is already running on the computer is using that previously allocated (but not static) port number when you start SQL Server, SQL Server chooses another port.

When an instance of SQL Server uses dynamic port allocation, the connection string that is built at the SQL Server client does not specify the destination TCP/IP port unless the user or the programmer explicitly specifies the port. Therefore, the SQL Server client library queries the server on UDP port 1434 to collect the information about the destination instance of SQL Server. When SQL Server returns the information, the SQL Server client library sends the data to the appropriate instance of SQL Server.

If UDP port 1434 is disabled, the SQL Server client cannot dynamically determine the port of the named instance of SQL Server. Therefore, the SQL Server client may be unable to connect to the named instance of SQL Server. In this situation, the SQL Server client must specify the dynamically allocated port where the named instance of SQL Server 2000, SQL Server 2005, or SQL Server 2008 is listening.

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.

TROUBLESHOOTING

If the SQL Server clients cannot access an instance of SQL Server after you have configured, the following causes may exist:

A firewall may be blocking the specified TCP/IP port.

If the port that the SQL Server instance is currently listening on is blocked by your firewall, the connections will fail.

Another program may already be using the specified TCP/IP port.

If another program is already using the specified TCP/IP port, the port is not available to the instance of SQL Server and SQL Server clients may be unable to connect to the instance of SQL Server.

This problem is specific to an instance of SQL Server that is configured to use a static TCP/IP port. This problem does not occur for an instance of SQL Server that is configured to use dynamic port allocation. In dynamic port allocation, if another program is already using the specified TCP/IP port when you start the instance of SQL Server, the instance of SQL Server selects a new port.

Posted in SQL Server DBA | Tagged , , , , , , | 2 Comments

Migrate Log shipping from SQL 2005 to 2008

Posted on January 15, 2010 by uday arumilli

Migrate Log shipping from SQL 2005 to 2008

This post helps us to Migrate Log shipping from SQL 2005 to 2008.

LET’S upgrade the Log shipping from SQL SERVER 2005 TO SQL SERVER 2008

We have 3 different cases as follows:

1. One Primary, One Secondary

2. One Primary, One Secondary and a Monitor

3. One Primary and More Secondary Servers

Case1:

à Upgrade the Secondary

à Upgrade the Primary

Ø Set database in offline and Upgrade

Ø Failover to the secondary and Upgrade

Ø Switch back the Log shipping (Optional)

Case2:

à Upgrade the Monitor

à Upgrade the Secondary

à Upgrade the Primary

Ø Set database in offline and Upgrade

Ø Failover to the secondary and Upgrade

Ø Switch back the Log shipping (Optional)

Case3:

à Upgrade all the secondary servers

à Upgrade the Primary

Ø Set database in offline and Upgrade

Ø Failover to one of the secondary server and Upgrade. Here remaining secondary servers

are in offline till the primary server is being enabled log shipping.

Ø Switch back the Log shipping

Protect Your Data Before the Upgrade

To protect your data

1. Perform a full database backup on every primary database

2. Run the DBCC CHECKDB command on every primary database.

Upgrading the Monitor Server Instance

The monitor server instance, if any, can be upgraded at any time.

While the monitor server is being upgraded, the log shipping configuration continues to work, but its status is not recorded in the tables on the monitor. Any alerts that have been configured will not be triggered while the monitor server is being upgraded. After the upgrade, you can update the information in the monitor tables by executing the sp_refresh_log_shipping_monitor (Transact-SQL) system stored procedure.

The Upgrade Process for Configurations with a Single Secondary Server

The upgrade process described in this section assumes a configuration consisting of the primary server and only one secondary server. This configuration is represented in the following illustration, which shows a primary server instance, A, and a single secondary server instance, B.

Upgrading the Secondary server instance

The upgrade process involves upgrading the secondary server instances of a SQL Server 2005 log shipping configuration to SQL Server 2008 before upgrading the primary server instance. Always upgrade the secondary server instance first. If the primary server were upgraded before a secondary server, log shipping would fail because a backup created on a newer version of SQL Server cannot be restored on an older version of SQL Server.

Log shipping continues throughout the upgrade process because the upgraded secondary servers continue to restore the log backups from the SQL Server 2005 primary server. While the secondary server instance is being upgraded, the log shipping copy and restore jobs do not run, so unrestored transaction log backups will accumulate. The amount of accumulation depends on the frequency of scheduled backup on the primary server. Also, if a separate monitor server has been configured; alerts might be raised indicating restores have not been performed for longer than the configured interval.

Once the secondary server has been upgraded, the log shipping agents jobs resume and continue to copy and restore log backups from the primary server instance, server A. The amount of time required for the secondary server to bring the secondary database up to date varies, depending on the time taken to upgrade the secondary server and the frequency of the backups on the primary server.

Note:
During the server upgrade, the secondary database is not upgraded to a SQL Server 2008 database. It will get upgraded only if it is brought online.

Upgrading the Primary server instance

When planning an upgrade, a significant consideration is the amount of time that your database will be unavailable. The simplest upgrade scenario involves the database being unavailable while you upgrade the primary server (scenario 1, below).

At the cost of a more complicated upgrade process, you can maximize your database availability by failing over the SQL Server 2005 primary server to a SQL Server 2008 secondary server before upgrading the original primary server (scenario 2, below). There are two variants of the failover scenario. You can switch back to the original primary server and keep the original log shipping configuration. Alternatively, you can remove the original log shipping configuration before upgrading the original primary server and later create a new configuration using the new primary server. This topic describes each of these scenarios.

Scenario 1: Upgrade Primary Server Instance Without Failover

This is the simpler scenario, but it causes more downtime than using failover. The primary server instance is simply upgraded and the database is unavailable during this upgrade.
Once the server is upgraded, the database is automatically brought back online, which causes it to be upgraded. After the database is upgraded, the log shipping jobs resume.

Scenario 2: Upgrade Primary Server Instance with Failover
 This scenario maximizes availability and minimizes downtime. It utilizes a controlled failover to the secondary server instance, which keeps the database available while the original primary server instance is upgraded. Downtime is limited to the relatively short time required to fail over, rather than the time required to upgrade the primary server instance.
Upgrading the primary server instance with failover involves three general procedures:

1. Performing a controlled failover to the secondary server,
2. Upgrading the original primary server instance to SQL Server 2008,
3. Setting up log shipping on a SQL Server 2008 primary server instance.

Important:
If you plan to have the secondary server instance as the new primary server instance, you need to remove the log shipping configuration. Log shipping will need to be reconfigured from the new primary to the new secondary, after the original primary server instance has been upgraded. For more information, see Removing Log Shipping.

Procedure 1. Perform a controlled failover to the secondary server

Controlled failover to the secondary server:
1. Manually perform a tail-log backup of the transaction log on the primary database specifying WITH NORECOVERY. This log backup captures any log records that have not been backed up yet and takes the database offline. Note that while the database is offline, the log shipping backup job will fail. The following example creates a tail log backup of the AdventureWorks database on the primary server. The backup file is named Failover_AW_20080315.trn:2.

BACKUP LOG AdventureWorks

TO DISK=N’\\FileServer\LogShipping\AdventureWorks\Failover_AW_20080315.trn’

WITH NORECOVERY; GO

We recommend that you use a distinct file naming convention to differentiate the manually-created backup file from the backup files created by the log shipping backup job.

3. On the secondary server:
a. Ensure that all backups taken automatically by the log shipping backup jobs have been applied. To check which backup jobs have been applied, use the sp_help_log_shipping_monitor (Transact-SQL) system stored procedure on the monitor server or on the primary and secondary servers. The same file should be listed in the last_backup_file, last_copied_file, and last_restored_file columns. If any of the backup files have not been copied and restored, manually invoke the agent copy and restore jobs for the log shipping configuration. For more information, see How to: Start a Job (SQL Server Management Studio) or sp_start_job (Transact-SQL).

b. Copy your the final log backup file that you created in step 1 from the file share to the local location that is used by log shipping on the secondary server.

c. Restore the final log backup specifying WITH RECOVERY to bring the database online. As part of being brought online, the database will upgraded to SQL Server 2008. The following example restores the tail log backup of the AdventureWorks database on the secondary database. The example uses the WITH RECOVERY option, which brings the database online: d.

RESTORE LOG AdventureWorks

FROM DISK = N’c:\logshipping\Failover_AW_20080315.trn’

WITH RECOVERY;GO

d. Fail over the database by redirecting clients from the original primary server (server A) to the online secondary server (server B).

e. Take care that the transaction log of the secondary database does not fill while the database is online. To prevent the transaction log from filling, you might need to back it up. If so, we recommend that you back it up to a shared location, a backup share, to make the backups available for restoring on the other server instance.

Procedure 2. Upgrade the Original Primary Server Instance to SQL Server 2008

After you upgrade the original primary server instance to SQL Server 2008, the database will still be offline and in the SQL Server 2005 format.

Procedure 3. Set Up Log Shipping on SQL Server 2008

The rest of the upgrade process depends on whether log shipping is still configured, as follows:
If you have kept the SQL Server 2005 log shipping configuration, switch back to the original primary server instance. For more information, see “To switch back to the original primary server instance,” later in this section.

If you removed the log shipping configuration before failing over, create a new log shipping configuration in which the original secondary server instance is the new primary server instance. For more information, see “To keep the old secondary server instance as the new primary server instance,” later in this section.

To switch back to the original primary server instance

1. On the interim primary server (server B), back up the tail of the log using WITH NORECOVERY to create a tail-log backup and take the database offline. The tail log backup is named Switchback_AW_20080315.trn.For example:2. BACKUP LOG AdventureWorks 3. TO DISK = N’\\FileServer\LogShipping\AdventureWorks\Switchback_AW_20080315.trn’4. WITH NORECOVERY;GO

5. If any transaction log backups were taken on the interim primary database, other than the tail backup that you created in step 1, restore those log backups using WITH NORECOVERY to the offline database on the original primary server (server A). The database is upgraded to SQL Server 2008 format when the first log backup is restored.

6. Restore the tail-log backup, Switchback_AW_20080315.trn, on the original primary database (on server A) using WITH RECOVERY to bring the database online.

7. Fail over back to the original primary database (on server A) by redirecting clients to the online secondary server from the original primary server.
After the database comes online, the original log shipping configuration will resume.
To keep the old secondary server instance as the new primary server instance
Establish a new log shipping configuration using the old secondary server instance, B, as the primary server and the old primary server instance, A, as the new secondary server, as follows:

Important:

The old log shipping configuration should have been removed from the original primary server at the start of the process before taking the manual transaction log backup that took the database offline.
1. To avoid performing a complete backup and restore of the database on the new secondary server (server A), apply the log backups from the new primary database to the new secondary database. In the example configuration, this involves restoring the log backups taken on server B to the database on server A.
2. Back up the log from the new primary database (on server B).
3. Restore the log backups to the new secondary server instance (server A) using WITH NORECOVERY. The first restore operation updates the database to SQL Server 2008.
4. Configure log shipping with the former secondary server (server B) as the primary server instance.

Important:
If you use SQL Server Management Studio, specify that the secondary database is already initialized.

5. To configure log shipping
o How to: Enable Log Shipping (SQL Server Management Studio)
o How to: Enable Log Shipping (Transact-SQL)

6. Fail over the database by redirecting clients from the original primary server (server A) to the online secondary server (server B).

Important:

When you failover to a new primary database, you should ensure that its metadata is consistent with the metadata of the original primary database. For more information, see Managing Metadata When Making a Database Available on Another Server Instance.

Considerations for Upgrading Multiple Secondary server instances

This configuration is represented in the following illustration, which shows a primary server instance, A, and two secondary server instances, B and C.
Always upgrade all the secondary server instances before you upgrade the primary server.

Upgrading With Failover and Switching Back to the Original Primary Server

When upgrading the primary instance with failover the process is more complex when there are multiple secondary server instances. In the following procedure, after all the secondary servers are upgraded, the primary server is failed over to one of the upgraded secondary databases. The original primary server is upgraded, and log shipping is failed over back to it.

1. Upgrade all the secondary server instances (server B and server C).
2. Obtain the tail of the transaction log of the primary database (on server A), and take the database offline, by backing up the transaction log using WITH NORECOVERY.
3. On the secondary server to which you plan to fail over (server B), bring the secondary database online, by restoring the log backup using WITH RECOVERY.
4. On every other secondary server (server C), leave the secondary database offline by restoring the log backup using WITH NORECOVERY.

Note:

The log shipping copy and restore jobs will run on the secondary servers, but the jobs will do nothing because new log-backup files will not be placed on the backup share.

5. Fail over the database by redirecting clients from the original primary server (server A) to the online secondary server (server B). The online database becomes an interim primary server, keeping the database available while the original primary server is offline (server A).

6. Upgrade the original primary server (server A).

7. On the database to which you failed over—the interim primary database (on server B), manually back up the transaction log using WITH NORECOVERY. This takes the database offline.

8. Restore all transaction log backups that you created on the interim primary database (on server B) to every other secondary database (on server C) using WITH NORECOVERY. This allows log shipping to continue from the original primary database after its upgrade, without requiring a full database restore on each secondary database.

9. Restore the transaction log from the interim primary server (server B) to the original primary database (on server A) using WITH RECOVERY.

Redeploying Log Shipping

If you do not want to migrate your log shipping configuration using one of the procedures shown above, you can redeploy log shipping from scratch by reinitializing your secondary database with a full backup and restore of the primary database. This may be a desirable option if you have a small database or if high availability is not crucial during the upgrade procedure.
For information about enabling log shipping using SQL Server Management Studio, see How to: Enable Log Shipping (SQL Server Management Studio).

Posted in High Availability, SQL Server DBA | Tagged , , , , | Leave a comment

Dynamic Management Views in Sql Server

Posted on January 1, 2010 by uday arumilli

Dynamic Management Views in Sql Server

Introduction:
The dynamic management views (DMVs) in SQL Server 2005 are designed to give you a window into what’s going on inside SQL Server. They can provide information on what’s currently happening inside the server as well as the objects it’s storing. They are designed to be used instead of system tables and the various functions provided in SQL Server 2000. This article provides an introduction to DMVs and covers a few of the basic views and functions.
The Dynamic Management Views are actually composed of both views and table-valued functions. Some apply to the entire server and are stored in the master database. Others are specific to each database. All are stored in the sys schema. They all start with dm_ in the name. They have been broken up into twelve categories:

1. Common Language Runtime Related Dynamic Management Views
2. I/O Related Dynamic Management Views and Functions
3. Database Mirroring Related Dynamic Management Views
4. Query Notifications Related Dynamic Management Views
5. Database Related Dynamic Management Views
6. Replication Related Dynamic Management Views
7. Execution Related Dynamic Management Views and Functions
8. Service Broker Related Dynamic Management Views
9. Full-Text Search Related Dynamic Management Views
10. SQL Operating System Related Dynamic Management Views
11. Index Related Dynamic Management Views and Functions
12. Transaction Related Dynamic Management Views and Functions

This article will focus on a few of the more common views.

Sessions
We’ll start by looking at a view that will give us information on each session. Selecting from sys.dm_exec_sessions is similar to running sp_who2 or selecting from sysprocesses. It lists one row per session. Remember that when you reference any of the dynamic management views or functions you’ll need to qualify with the sys schema.

SELECT
session_id,
login_name,
last_request_end_time,
cpu_time
FROM
sys.dm_exec_sessions
WHERE
session_id >= 51
GO

session_id login_name last_request_end_time cpu_time
———- ——————– ———————– ———–
51 L30\billgraziano 2005-10-30 17:11:26.487 170
52 bg 2005-10-30 17:03:33.667 190
53 L30\billgraziano 2005-10-30 16:43:26.160 30
55 bg 2005-10-30 17:03:34.740 90

The view also returns an extra 25 columns or so but this is a good place to start. The session_id is basically the SPID that we’re used to seeing. In SQL Server 2000 selecting @@SPID returned the “server process identifier” or SPID. In SQL Server 2005 selecting @@SPID returns the “session ID of the current user process”. The view also returns session-specific information such as the ANSI NULL settings, reads, writes and other set-able session objects.

Connections
For those sessions that come from outside SQL Server (session_id >= 51) we can also look at the connection information. We’ll query sys.dm_exec_connections for this information. This view returns one row for each connection.

SELECT
connection_id, session_id,
client_net_address,
auth_scheme
FROM
sys.dm_exec_connections
GO

connection_id session_id client_net_address auth_scheme
———————————— ———– ——————– ———–
71AE7560-9366-486C-ACBF-D5405E89B6F5 51 local machine NTLM
CAB9E608-0312-42D8-A19C-AD1D954AA427 52 192.168.8.20 SQL
7369B4B6-7199-4527-A882-215023D352EC 53 local machine NTLM
192FFCE9-588C-49DA-9BE1-07134291BC86 55 192.168.8.20 SQL

There are about fifteen other columns but we’re going to focus on these for now. Notice that the IP address of the client is listed as well as the authentication scheme. One of the things I’ve always wanted from SQL Server is an easy way to see the IP address for each connection.

Requests
In order to what each connection is actually doing we’re going to use the sys.dm_exec_requests view. This lists each request that is executing within SQL Server.
SELECT
session_id,
status,
command,
sql_handle,
database_id
FROM
sys.dm_exec_requests
WHERE
session_id >= 51
GO

session_id status command sql_handle database_id
———- ———- ——— ————————————————– ———–
54 running SELECT 0x02000000DF1170132662EE95912DA70270B3EE0F74BCD15C 1
56 suspended WAITFOR 0x02000000C72622210D647D6515783CD4D7140FEB7EE478B5 1

In this case there are two running queries. The first (#54) is my select from sys.dm_exec_requests. The second is another query running from a different connection. We can see that it’s current running a WAITFOR command. This view has a number of other interesting columns including the start time, plan_handle (hash map of the cached query plan), wait type information, transaction information, reads, writes and connection specific settings. Let’s see what we can find out about the other query that’s running.

SQL Text
sys.dm_exec_sql_text is a dynamic management function that returns the text of a SQL statement given a SQL handle. Fortunately we just happen to have a SQL handle from our query of sys.dm_exec_requests. To see the SQL text that’s currently executing in session #54 we can use this query:

SELECT
st.text
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st
WHERE
r.session_id = 56
GO

text
———————————————-
SELECT
*
FROM
TAMSDev.dbo.AuditLog

WAITFOR DELAY ’00:00:10′

Notice that we used a CROSS APPLY to execute the function for each row returned by the view.

Security
In order to query these views a user needs specific permissions granted. To view the server-wide DMVs the user must be granted the VIEW SERVER STATE on the server. After running the following query as administrator

GRANT VIEW SERVER STATE to bg

I was able to query the DMVs when logged in as a regular user. To query database specific DMVs a user must be granted the VIEW DATABASE STATE permission in each specific database. If you want to deny a user permission to query certain DMVs you can use the DENY command and reference those specific views. And remember it’s always better to grant permission to roles instead of individual users.

Best way to find the Latest Executed Query from a Connection:

First we should find the session_id using sys.dm_exec_connections dmv. Then use the below query:

select
st.text
from
sys.dm_exec_connections C
cross apply
sys.dm_exec_sql_text(most_recent_sql_handle) st
where
C.session_id=61

Summary

Those are some of the basic dynamic management views. In a future article I’ll cover a few more of the management views that provide additional information about the server and what’s in it.

Posted in SQL Development, SQL Server DBA | Tagged , , , , , , , | Leave a comment