Allen White

Getting IP Address/Port information from SMO

In response to a question in the MSDN SMO forum yesterday I started playing around with the SMO WMI wrapper objects. The object is to find the IP address and port used for each of my servers. I started with my own workstation for my testing. The first thing I needed to do was to add the imports for the SMO WMI Namespace at the top of my program (Imports Microsoft.SqlServer.Management.SMO.Wmi). I then wrote the following code:

        Dim mcServer As ManagedComputer
        Dim strPhysName As String
        Dim strInstanceName As String
        Dim objServerInstance As ServerInstance
        Dim objProtocol As ServerProtocol
        Dim objProtocolProperty As ProtocolProperty
        Dim objIPAddress As ServerIPAddress
        Dim objIPAddressProperty As IPAddressProperty
        Dim file As System.IO.StreamWriter

        strSrvName = "MyServer\Instance"
        strPhysName = "MyServer"
        strInstanceName = "Instance"

        file = My.Computer.FileSystem.OpenTextFileWriter("C:\IPProperties.txt", True)

        'Get WMI Network values
        mcServer = New ManagedComputer(strPhysName)
        objServerInstance = mcServer.ServerInstances.Item(strInstanceName)
        objProtocol = objServerInstance.ServerProtocols.Item("Tcp")
        file.WriteLine("Protocol: " + objProtocol.Name)
        For Each objIPAddress In objProtocol.IPAddresses
            file.WriteLine("IPAddress: " + objIPAddress.Name + ", " + objIPAddress.IPAddress.ToString)

            For Each objIPAddressProperty In objIPAddress.IPAddressProperties
                file.WriteLine("IPAddressProperty: " + objIPAddressProperty.Name + ", " + objIPAddressProperty.Value.ToString)
            Next
        Next

        For Each objProtocolProperty In objProtocol.ProtocolProperties
            file.WriteLine("ProtocolProperty: " + objProtocolProperty.Name + ", " + objProtocolProperty.Value.ToString)
        Next

        file.Close()

The first thing the program does is open a text file for my returned properties. After that I instantiate a ManagedComputer object, set the ServerInstance object to the SQL Server instance I'm interested in, then grab the ServerProtocol object for the TCP/IP protocol (which is identified by the property name of "Tcp". Once I have that I iterate through the ServerIPAddress objects within the protocol, and iterate through the IPAdressProperty objects in each ServerIPAddress object. Once that's done I iterate through the ServerProtocol object's ProtocolProperty objects, and display the names and values of each of the properties for the overall TCP/IP protocol.

I did all this to discover what values are set so I can make targeted requests of WMI to get the IP address and port, as I mentioned before. The results I got back were interesting. First, here's the result of my ipconfig command:

Windows IP Configuration

Ethernet adapter Local Area Connection:
        Connection-specific DNS Suffix  . : ws.mydomain.com
        IP Address. . . . . . . . . . . . : 10.47.120.123
        Subnet Mask . . . . . . . . . . . : 255.255.0.0
        Default Gateway . . . . . . . . . : 10.47.2.1

Ethernet adapter Local Area Connection 2:

        Media State . . . . . . . . . . . : Media disconnected

When I looked at the result of my application, though, I saw these results:

Protocol: Tcp
IPAddress: IP1, 10.8.3.23
IPAddressProperty: Active, True
IPAddressProperty: Enabled, False
IPAddressProperty: IpAddress, 10.8.3.23
IPAddressProperty: TcpDynamicPorts, 0
IPAddressProperty: TcpPort, 
IPAddress: IP2, 192.168.1.100
IPAddressProperty: Active, True
IPAddressProperty: Enabled, False
IPAddressProperty: IpAddress, 192.168.1.100
IPAddressProperty: TcpDynamicPorts, 0
IPAddressProperty: TcpPort, 
IPAddress: IP3, 127.0.0.1
IPAddressProperty: Active, True
IPAddressProperty: Enabled, False
IPAddressProperty: IpAddress, 127.0.0.1
IPAddressProperty: TcpDynamicPorts, 0
IPAddressProperty: TcpPort, 
IPAddress: IPAll, 0.0.0.0
IPAddressProperty: TcpDynamicPorts, 3181
IPAddressProperty: TcpPort, 
ProtocolProperty: Enabled, True
ProtocolProperty: KeepAlive, 30000
ProtocolProperty: ListenOnAllIPs, True
ProtocolProperty: NoDelay, False

The IP address values on each of the three reported IP addresses don't match the IP address assigned on my network to my system. The Port my instance is using is in the 'IPAll' IPAddress in the TcpDynamicPorts property, so that's good, but I'm curious why the IP Address I get from ipconfig isn't returned in my application results.

I poked around the Configuration Manager and found that the Properties window for the TCP/IP protocol (under Network Configuration) matches the data coming from my application, but I really don't know where these values were established.

If anyone has a good idea where this is coming from, and maybe how I can get the ipconfig IP address back from SMO/WMI I'd appreciate it.

Allen

PASS Friday - Performance to Podcast

Friday brought some really great sessions, starting with David Campbell's keynote and his discussion of improving the customer experience. I've spent a lot of time in the MSDN forums this year and the Microsoft presence there is strong, and he's responsible for that.

Brad McGehee of SQL-Server-Performance.com gave a very detailed presentation about performance, but it was hard to hear him. After lunch I attended Matt Zito's presentation on Zen and the Art of Database Automation, which was interesting. Matt's a great speaker and I jotted a lot of ideas down to implement when I get back. Peter Ward actually did a great job of presenting SMO at an introductory level, but he started with examples of using first DMO then SMO using VBScript, and I learned a while ago you can't manage your servers using SMO and VBScript. You can manage your local default instance but you can't define a server to connect to.

Chuck Boyce had asked me to stop by early in the week to do a podcast, and I kept forgetting, so I joined him at his ad hoc studio, and he had Louis Davidson and me interview each other. It was fun getting to know Louis that way, and I hope Chuck's happy with the podcast.

After the conference I went out to dinner with my international friends Morten Baden Rohde and Johan Bijnens before turning in for an early night. Tomorrow is the Big Game, my Ohio State Buckeyes dotting the I on that number 1 season against that team up north. The news today about the passing of Bo Schembechler was sad, he was a great coach and a great man, but he would have wanted both teams to put their best effort on the field, and it's time to do that. I'll be watching the game from the Sport Restaurant and Bar in Seattle with the OSU Alumni who gather there every game.

GO BUCKS!

Allen

PASS Thursday - Data Dude Rocks!

It's really been neat to have people at PASS come up to me and tell me how much they enjoyed my presentation, and how they plan to use the ideas I shared with them. I'm anxious to see the evaluations.

The keynote with Steve Ballmer was entertaining, especially his response to the question about Microsoft "selling out" in the agreement with Sun and Novell. I was able to go to both sessions Gert Drapers presented on Data Dude, and then picked up the CD of CTP 7 in the Microsoft booth so I can start using it to document my production databases, if nothing else. I met briefly with Dandy Weyn before his session on using SSIS to perform SQL Server Maintenance. Dandy is active in the MCT newsgroups and it was great to meet him, and his session gave me some good ideas. The last session was Kalen Delaney's on Data Storage Metadata, and she always has such a great way of explaining things so they make perfect sense.

Quest Software hosted a party that allowed me to meet a few more people, and spend time with those I only get to see once a year at this conference, so thanks to Heather and crew from Quest.

Allen

PASS Wednesday - Presentation Day!

Wow, what a day! Starting with the keynote by Paul Flessner all the way to the Idera party at the Rock Bottom Brewery, it was an incredible day. I was especially touched by Paul's emotional close to the keynote. (Michael's demo really does work - I've seen it, but he should have read my blog about the "Cannot Generate SSPI Context" error message .)

I didn't attend a morning session because I wanted to go to the Speaker's Lounge and practice my presentation, as well as to make sure the technical stuff related to recording the session was working. I had a chance to chat with Kalen Delaney and tell her how much I enjoyed her pre-conference session, and I managed to not go 'all fan boy' in the process. She really is very nice and an incredible resource for the SQL Server community. I was also able to spend some time catching up with Randy Dyess from Solid Quality Learning. Randy worked on the 70-444 Alpha Review team with me last year.

After lunch I went to my presentation room and got the presentation all set up and communicating with the recording system, and had a chance before the session to chat with Jon Baker and Tony Mann, and then I got started. Other then some technical performance issues related to the recording system and running the presentation from a Virtual PC image it went well. I received a few questions from the audience (I'd asked them to hold until the end because of the volume of demos, and I didn't want to shortchange any of them.) There were a number of good questions afterwards and the comments I received the rest of the day told me that people enjoyed the presentation and are interested in SMO.

Back in the Speaker's Lounge to wind down I got to chat with Adam Machanic, Victor Isakov (another 444 Alpha review teammate) and Ted Malone, and when Gert Drapers came in and joined the conversation I was able to tell him how his talk about DMO a few years ago was how I got started down the road that led me to my involvement in SMO today. I hope I can have a similar influence on others.

Victor gave a good presentation on Table Partitioning, and then it was time for the vendor reception. I had an opportunity to spend some time with Tony Davis, of Red Gate Software and the SimpleTalk newsletter, and enjoyed that conversation very much. It was a great opportunity to mingle with the people in the SQL Server community I've gotten to know, and to meet others, including Dejan Sarka of Solid Quality Learning.

After the reception I attended parties hosted by SoftArtisans and by Idera, and got to meet more great people and had a great time relaxing after a very exciting day. Thursday should be exciting as well.

Allen

PASS Tuesday - Query Plans in SQL 2005 and Thank You SQLServerCentral.com

Kalen Delaney not only writes great books, but delivers great session content as well. I spent the day Tuesday in Kalen's pre-conference session on Query Plans in SQL Server 2005. The last time I took a performance and tuning class was for Sybase SQL Server version 4.9, so things have changed just a little (grin) and it was great to have one of the best in the business up front delivering the material. Her analogy using how "normal" people arrange the clothes in their closets to explain hash joins was wonderful!

I met some great people at the Speaker Orientation meeting, and had a nice chat with Joe Celko about his next SQL Puzzles book. The PASS reception was a great place to renew acquaintences with people, including Rick Heiges, Tony Mann and Kevin Klein. The SIG Quiz Bowl was fun, and some people who probably wouldn't have volunteered for the quiz in past year's format walked away with some nice prizes.

Of course, the place was buzzing with the news about Red Gate's purchase of SQLServerCentral.com. I've chatted with Brian Knight at every PASS and TechEd conference for the past few years, and he's one of the nicest guys you'll ever meet. I congratulated him on the great news. At their reception I finally got to meet Steve Jones and Andy Warren (well, I've spoken to them at past conferences, but they didn't know me). I've got an article coming out on their site next week, so they were able to put a face with my name.

About five years ago when researching a problem I was having I discovered SQLServerCentral.com and subscribed to their newsletter. Because of their site I learned about PASS, and when I finally was able to attend PASS for the first time three years ago here in Seattle, things started to change for me, having found a great community doing the same kinds of things I was doing. Because of PASS I've made great business contacts and friends, and on Wednesday I'll be one of the speakers delivering content to others like me. Brian, Steve and Andy were the catalyst for making that happen and will always be thankful to them for that.

Allen

PASS Monday - Practical Performance Monitoring session

The first day of PASS pre-conference sessions was great. I attended Andrew Kelly's presentation on Practical Performance Monitoring, and am very happy I did. There have been a lot of articles about gathering performance metrics, but it always seemed there was something missing in the information that prevented me from being able to gather the data. Andrew's presentation provided all the details I needed on how to get the data and a variety of ways of making use of it. Now my boss will be happy that I can provide more detailed metrics.

After the session I attended a meeting of MCT's that Lutz Ziob, DM of Microsoft Learning, hosted to hear comments about the Instructor Led Training products for SQL Server 2005. There's some dissatisfaction with the current offerings and it was great that Lutz was able to accept the honest concerns of the training providers present in the meeting. It also gave me a chance to renew my acquaintences with Fernando Guerrero of Solid Quality Learning and Colin Lyth of Microsoft, both of whom I'd worked with on the Objective Domain for the Database Developer track.

Allen

In Seattle and Ready for PASS

After a very enjoyable two and a half day trip from Cleveland to Seattle aboard Amtrak, I'm in Seattle and excited about PASS. The last time PASS was held here in Seattle was three years ago, and I met some really great guys working on what they called the SIG Challenge (or something like that) where we worked together on a database project in competition with three other teams. We didn't win, but we became friends and meet at PASS every year. This year Morten Baden Rohde of JN Data A/S in Denmark decided to meet me in Chicago and take the train with me to Seattle. (We'll meet up with Johan Bijnens of Arcelor in Belgium on Monday.)

We got to Seattle, checked in to our hotels and had dinner at the Fox Sports Grill, then picked up our registration materials. (The Speaker ribbon on my badge this year is VERY cool.) Included in the green back pack they gave us is a DVD with about a dozen Hands-On Labs, and the Virtual PC images to run them yourself. I can't think of a better way to learn about this technology than to be able to practice it in the VPC environment, where you can break things and just reinstall the VPC image and try again. Kudos to Microsoft and to PASS for making this available to all the attendees of this conference.

I also wanted to thank Morten for helping me tune some of the demos in my presentation after I did a practice session for him on the train on Saturday. It really helps having someone who understands the technology sit through the session to make sure I'm delivering useful information. He'll be in the audience, along with Johan, who sent me this small code snippet:

With [Allen's SMO-session at SQLPASS]
    .Checked = true
    .RemindMe=true
    .AskSillyQuestions=False
    .CuchFrequency=(-1)  ' 1 per minute (-1 = disabled)
    .HaveABeerAfterward=true
End With

("Cuch" meaning cough, of course.)

It's making friends like these, as well as all the great technical resources that make PASS so worthwhile.

Allen

Time to Head West for PASS

I've got the coolest boss in the world, because he let me book my trip to Seattle on Amtrak's Empire Builder. I leave Cleveland at 2:37am tomorrow morning, and then transfer to the Empire Builder tomorrow afternoon. I'll arrive in Seattle on Sunday at 10:20am. I return right after the Ohio State Buckeye's game against that "team up North", as Woody used to call them. Yes, I found a bar in Seattle where the OSU Alumni hang out for the games, so I won't be alone with my Scarlet and Grey, and my Buckeye Beads.

The week next week is getting quite full, with sessions during the day, and meetings and receptions in the evening. I hope I get to see a lot of people who've visited this blog. I also found out that my first article will be published on SQLServerCentral.com next week. This is perfect, as I became aware of PASS because of that site.

Well, one of the nice things about taking the train is that I'll be out of touch for a few days - it's a little time to reflect and renew, and to prepare for my session on SMO. I plan on blogging each day on the new things I'm learning at the conference, so have a great weekend and I'll be back next week.

Allen

Taking the Tedium out of User Maintenance using SMO

We have some of our production servers in a domain that does not trust our corporate domain, so I need to use SQL logins to connect with these servers. One of the servers supports an application that has many databases, all structurally alike, but each for their own 'client' data. Due to some personnel changes I had to remove some users and add a number of additional ones. Now, adding a user to each of over 100 databases, and enabling the db_datareader database role for the user in each database. For one user alone that's a tedious process, but imagine doing four or five users. Needless to say, SMO comes to the rescue once again.

The first thing I did (outside the SMO application) was add the login for each user. That part's easy in Management Studio. The rest I did in the application. First is establishing the connection to the server, and instantiating the object for the login, because we're going to reset the default database after granting the database access. (Notice the use of a SQL login to connect, as it's not in the corporate domain.)

        Dim srvMgmtServer As Server
        Dim strUser As String
        Dim objLogins As LoginCollection
        Dim objLogin As Login

        strUser = "juser"

        srvMgmtServer = New Server()
        Dim srvConn As ServerConnection
        srvConn = srvMgmtServer.ConnectionContext
        srvConn.ServerInstance = "MyServer"
        srvConn.LoginSecure = False
        If srvConn.LoginSecure = False Then
            srvConn.Login = "remadmin"
            srvConn.Password = "pas$w0rd"
        End If

        objLogins = srvMgmtServer.Logins
        objLogin = objLogins.Item(strUser)

All the databases used by this app are prefixed by either va_ or vb_, but the 'boilerplate' databases start with app_default, and the application master database is called app_master. Once we've established that the database qualifies and is not a snapshot database, we then attempt to instantiate the user object for that user. If it doesn't exist we create it. Then we check to see if it has the db_datareader role assigned to it, and if not, do so.

        ' Browse the database collection on the target server
        Dim lisDBs As DatabaseCollection
        lisDBs = srvMgmtServer.Databases
        Dim objDB As Database
        For Each objDB In lisDBs
            If Not objDB.IsDatabaseSnapshot Then
                If Left(objDB.Name, 3) = "va_" Or Left(objDB.Name, 3) = "vb_" Or _
                   Left(objDB.Name, 11) = "app_default" Or objDB.Name = "app_master" Then
                    Dim objUsers As UserCollection
                    Dim objUser As User

                    objUsers = objDB.Users
                    objUser = objUsers.Item(strUser)
                    If (objUser Is Nothing) Then
                        objUser = New User(objDB, strUser)
                        objUser.Login = strUser
                        objUser.Create()
                    End If
                    If Not objUser.IsMember("db_datareader") Then
                        objUser.AddToRole("db_datareader")
                        objUser.Alter()
                    End If
                End If
            End If
        Next

Lastly, once we're certain the user has access to the app_master database we set the login's default database to the app_master.

        objLogin.DefaultDatabase = "app_master"
        objLogin.Alter()

These few lines of code saved hours of tedium, and I'm also certain I would have missed a database or two along the way.

Allen