Systems Engineering and RDBMS

Archive for February 17th, 2007

Troubleshoot Your Network with NETDIAG.EXE

Posted by decipherinfosys on February 17, 2007

What is Netdiag.exe?

Netdiag.exe is a Windows 2000 and 2003 Server command line tool that can be used to effectively test the network connectivity of a computer, and provides valuable insight to the overall health of your network.  Netdiag can help you solve any number of network issues including:

  • Checking Virtual Private Networks (VPN) network tunnels
  • Domain Name Service (DNS) or Windows Internet Naming Service (WINS) name resolution problems
  • Active directory replication
  • Verifying the binding of a server’s network cards
  • Problems with Internet Protocol Security (IPSEC)
  • Winsock corruption
  • Verifying the ability of domain controllers to use Lightweight Directory Access Protocol (LDAP)

Installing Netdiag.exe

Netdiag is included as part of the Support Tools on the Windows Server CD. Once the Support Tools have been installed you can simply run ‘netdiag.exe’ from a command line. 

Using Netdiage.exe

Properly using netdiag involves a number of command line switches that need to be entered in a certain order. Not all of the switches are required, but the correct full syntax if you were to use them all is as follows:

netdiag [/q] [/v] [/l] [/debug] [/d:domain_name] [/fix] [/dcaccountenum] [/test:test_name] [/skip:test_name]

Below are the definitions of the various parameters:

/q: Specifies quite output and only displays errors

/v:Runs Netdiag in verbose mode, which dispays each action as it is being performed

/l:Sends the output of the Netdiag results to a Netdiag.log file

/debug:Runs Netdiag in debug mode

/d:domain_name: Used to locate domain controllers in a specified domain

/fix:This parameter detects and correct issues with DNS. It verifies that all DNS entries contained on a server are correct, and updates any invalid entries.

/dcaccountenum: Enumerates the computer accounts of the domain controller

/test:test_name:This parameter can be used to specify form a long list of netdiag tests that you can run. test_name can be any of the following values:

                  Autonet: Automatic Private IP Addressing (APIPA) address test
                  Bindings: Bindings test
                  Browser: Redir and Browser test
                  DcList: Domain controller list test
                  DefGw: Default gateway test
                  DNS: Domain Name Service (DNS) test
                  DsGetDc: Domain controller discovery test
                  IpConfig: IP address configuration test
                  IpLoopBk: IP address loopback ping test
                  IPSec: Internet Protocol security (IPSec) security test
                  IPX: Internetwork Packet Exchange (IPX) test
                  Kerberos: Kerberos Test
                  Ldap: Lightweight Directory Access Protocol (LDAP) test
                  Member: Domain membership test
                  Modem: Modem diagnostics test
                  NbtNm: NetBIOS over TCP/IP (NetBT) name test
                  Ndis: Netcard queries test
                  NetBTTransports: NetBT transports test
                  Netstat: Netstat information test
                  NetWare: NetWare test
                  Route: Routing table test
                  Trust: Trust relationship test
                  WAN: Wide Area Network (WAN) configuration test
                  WINS: Windows Internet Naming Services (WINS) service test
                  Winsock: Winsock test

You can specifiy multiple tests  by using multiple instances of the /test:test_namecommand, each separated with a space. So, for example, if you wanted to run three tests: DNS, IPSec, and WINS, a typical Netdiag command line would look like this:

netdiag /v /dcaccountenum /test:DNS /test:IPSec /test:WINS

 – /skip:test_name:Allows you specify one or more of the above tests that you want to skip during a particular Netdiag session. As with /test:test_name, you can specify multiple tests to skip by using multiple instances of the /skip:test_name command, each separated with a space.

Even in today’s point-and-click world, there are still a huge number of effective and powerful command line tools available for troubleshooting and monitoring. Netdiag is just one of many, but it is most certainly useful when examining your Windows Server infrastructure.

Posted in Networking | Leave a Comment »

Encrypted Stored Procedures in SQL Server – How Secure?

Posted by decipherinfosys on February 17, 2007

Yesterday, a good friend of mine asked me whether just using the “WITH ENCRYPTION” option is good enough to secure the source code in SQL Server. Oracle has the WRAP utility to do the same thing and it is much more robust. Anyways, in SQL Server, just because you have encrypted your source code using the “WITH ENCRYPTION” option does not mean that you are safe. One can use the dSQLSRVD utility to decrypt that code:

http://www.geocities.com/d0mn4r/dSQLSRVD.html

or even this stored procedure code (Decrypt2k) that is publicly available:

http://www.1perlscriptstreet.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5

The difference between the two is that domNar’s tool requires you to be a member of the sysadmin fixed server role. The stored procedure version above does not yield the decrypted code correctly for procedures with lengths more than 4K. And here is another link that talks about a couple of other options that exist for decrypting the encrypted code in SQL Server:

http://www.sql-shield.com/decrypt-stored-procedure.html

So, does that mean that there is really nothing that can be done to protect your source code in SQL Server? There are third party utilies like the one from SQL Shield mentioned above that can be used for doing this. You can also control this by using a very tight access control using roles and permissions but that can be done if you are the one who hosts the instance and have all the higher privileged accounts locked up.

In one of the future posts, we will look at encrytion of the data and what options does SQL 2005 provide for those.  It can be done using symmetric keys and is a very good feature.

Posted in SQL Server | Leave a Comment »

Parallel execution plans for queries

Posted by decipherinfosys on February 17, 2007

This is a really vast topic and we will put in several blog posts on this in the days to come. Let us start with evaluating what is a parallel execution plan and whether it is desired in all kind of applications. In the second part, we will start looking at specific RDBMS – Oracle, and then SQL Server and DB2 LUW to go over the details of how to look for parallel execution plan issues and how to do the configuration so that it suits your application(s) the best. We will also introduce system views that can be used to get more detailed information about these.

So, what does a parallel execution plan mean? It means that on machines that have more than one processor, the database engine can perform a query in parallel by using several operating system threads in an attempt to get the work done faster and thus decrease the response times for the queries. A serial plan on the other hand uses only one single thread for it’s execution. This, of course is decided by the engine based on a set of different configuration options (at the instance, database, table/index and query levels) and the hardware configuration as well.

That does sound very promising, isn’t it? And it would seem that a parallel plan always will provide a better response time since it can use multiple threads and get the work done faster. But that is not true. In a true OLTP system where the unit of work done is less, it makes more sense to have the work done serially rather than in parallel. Here is a real world analogy to explain this point: If say you are a VP of development and you have been asked by the CEO to write a one page proposal for the new product that is to be launched in the company. If you divide that work in the 10 direct reports that you have and each of those 10 direct reports are tasked with writing 2 lines each, then you will need to wait till each of them can finish their work so that you can accumulate it and prepare the final report and give it to your CEO.  A faster approach in this case would have been had you done the work yourself since the unit of work was small and really did not require any parallel effort among the team members.  In this case, the parallel effort infact made the process slower.  If on the other hand, if your CEO had asked you for writing a book that contained 10 chapters, in that case dividing that large unit of work among your direct reports would have been much better rather than doing it all by yourself.  So, it all depends upon the unit of work that needs to be done.

As a thumb rule if there are long running queries that do a large unit of work like in the case of a data warehousing/data mining projects, it makes perfect sense to have a parallel plan being generated.  If on the other hand, you have a true OLTP system where the queries are short, a serial plan is preferred.

In the next iteration, we will look into how to check for parallel execution plans in Oracle and which connfiguration options influence parallel plan generation.

Posted in DB2 LUW, Oracle, SQL Server | 2 Comments »