VMWare : How to configure static IP address for ESXi Hosts

Quick one today:

For one of our lab servers, we needed reconfigure an ESXi host from dynamic (default) to static IP address. Following are the steps, that worked for us. Not sure how, screenshots could be take from a ESXi console, so only text instructions for this post.

Go to the physical ESXi machine and perform the following operations.

• At the ESXi console screen press F2 to go to Customize System/View Logs
• Login with your credentials (root/password)
• Using up/down arrows, select “Configure Management Network” and hit enter
• Select “Network Adapters” and hit enter. Make sure the status says “Connected“.
• Go to IP configuration and change from dynamic to static IP and hit enter
• Using up/down arrows and enter key:
  • Enter a valid static IP address in your network.
  • Subnet mask
  • Default Gateway
  • Hit enter for <OK>
• Select DNS configuration and hit enter to change its settings.
  • Enter Primary DNS Server
  • Alternate DNS Server
  • Enter valid HostName in your network
  • Hit Enter for <OK> (accepting the settings)
• Press Esc to main screen.
• Hit “Y” to accept/apply the changes and restart the host for changes to take effect.

Helpful hints: For Subnet mask, DNS, Default Gateway, etc, I ran ipconfig /all on a different physical machine next to this ESXi host (in the same network) and used the same IP addresses.

After restart, the ESXi host shows the new static IP address with Hostname.

Thanks for this post for teaching me.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : How to configure Sql Server instance to listen in static port

Sometimes, in our environment, we have multiple instances of Sql Server running on the same machine. In default configuration, when named instances come up, they listen on some port number. This port number changes every time they restart.Check out the listening port in two ways:

• Sql Server Error Logs
• SCM >> TCP/IP properties window

See below.

Dynamic Configuration : Listening Port

Sql Browser to rescue. When you connect to a named instance without specifying port number in the connection string, the connection is still successful because SQL Browser running on the machine takes that incoming request and re-directs it to the named instance through appropriate port number.

Solution

If you want to make sure the named instances are listening on static port, that could be done through Sql Server Configuration Manager. Sql Server Configuration Manager >> Sql Server Network Configuration >> Protocols for <instance> >> Right Click on TCP/IP >>

Sql Instance Listening Port Properties

 

In properties window go to “IP Addresses” tab, and scroll to the bottom (like the image below), and enter a port number in the “TCP Port” section. Make sure the “TCP Dynamic Ports” is empty.

Sql Instanc Set Static Port

Now, when you restart the instance, Sql Server will come back up while listening to this static port. See Sql Server Error Logs and TCP/IP properties window below.

Sql Instance Listening on Static Port

To connect to this named instance listening on static port, we mention port in the connection string like the image below:

Connect To Static Port Named Instance

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server: How to fix orphan users (and map them to logins)

Quick one today:

When I restore databases from one instance to a different instance, i.e. from QA environment to Integration environment or to UAT, this issue of orphan users occurs.

Login are instance level principals and Users are database level principals. Each user account at the database level is mapped to a login at instance level. As part of this mapping, sid (security ID) of the login is mapped to

--
-- Gather details of login and users
--
SELECT uid, name, sid
FROM sys.sysusers
WHERE name = 'Copient_Logix'
GO

SELECT name, sid
FROM master.sys.syslogins
WHERE name = 'Copient_Logix'
GO

Login and User SID mapping

 

So, when you restore a database from one instance to another instance, this sid mapping will be incorrect; Resulting in orphan users at the database level. To correct this, we could take these steps.

Step 1:

Get a list of users that are orphans.

--
-- Gather list of orphan users
--
EXEC sp_change_users_login 'Report'
GO

Step 2:

--
-- Fix the orphan user
--
EXEC sp_change_users_login 'Auto_Fix', 'user'
GO

 

 

Hope this helps,
_Sqltimes

Read Full Post »