• SQL Server Cast

    Import Export Wizard is not Copy Database Wizard

    • 1 Comments

    During a recent work with one of our customers I did notice that it is not always clear what tools are available and required to copy SQL Server database objects, understanding “object” as a whole database or only part of it (table, view, user, etc.)

    As part of this work, my customer was trying to copy several databases between two different SQL Server instances but after several tests he was finding that although the tables were correctly copied, other objects as views and primary keys were not correctly transferred. It come to my attention that he was using the SQL Server Import Export Wizard to transfer the databases between one instance and another. Import Export Wizard can be launched from within SQL Server Management Studio (SSMS) or directly from a command prompt window typing DTSWizard.exe (further information about how to start Import Export Wizard can be found on this MSDN link)

     

    As indicated on the startup page on the Import Export Wizard, this tool can be used to copy data between different data sources and we have the option to recreate the destination database and tables, however this is not the right tool to use if we want to copy the whole database (not only tables, but all other objects) from one instance to another. The following Books Online link tries to clarify this difference:

    The purpose of the SQL Server Import and Export Wizard is to copy data from a source to a destination. The wizard can also create a destination database and destination tables for you. However, if you have to copy multiple databases or tables, or other kinds of database objects, you should use the Copy Database Wizard instead

    Copy Database Wizard will allow you to copy entire databases from one SQL Server instance into another (or the same, if you desire to do so) retaining the full database structure and objects. You can use two different methods to copy the databases and it is possible to keep the source database online while the copy is taking place, the following image shows these options:

     

    You can launch Copy Database Wizard directly from SSMS right-clicking on any database > Tasks > Copy database. You will see that this option (red) is side-by-side with the Import Export Wizard (green):

     

    Copy Database Wizard support the copy of databases between SQL Server 2000 and SQL Server 2005 providing an effective and simple way of upgrading databases between these two versions. For this wizard to work, SQL Server 2005 Service Pack 2 should be installed not only on the server where we are executing the tool but also on the destination server. For more information about this option please, refer to SQL Server Books Online.

    In summary, if you want to copy entire databases between different SQL Server instances, Copy Database Wizard is your tool. If you want to export data or import data from/into SQL Server or other data sources or just want to copy some tables, use Import Export Wizard. If you need to copy data and perform modifications during this process with a fine-grain control you will find a better solution by building and Integration Services package in Business Intelligence Development Studio.

  • SQL Server Cast

    How to Quickly Review your SQL Server Cluster Configuration on Windows Server 2003

    Working at Microsoft Customer Support Services (CSS), we are frequently asked by our customers to check their SQL Server cluster configuration.  Event though the task of installing and configuring a SQL Server cluster have been simplified every new Windows Server version, it is not strange to find clusters with non-optimal configurations. Part of this problem relies on the amount of settings the IT professional has to take into account when setting up a new cluster. Fortunately, Windows Server 2008 has introduced significant changes into the cluster setup, configuration and maintenance with the aim of simplifying most of the hard work for the IT professional.

    The goal of this post is to provide a quick guide for checking for some of the most common mistakes when configuring SQL Server on a Windows Server 2003 failover cluster. For this task we will use the CLUSMPS.EXE utility included in MPSReports.

    MPSReports to the Rescue

    Microsoft Product Support’s Reporting Tools (aka MPSReports) is probably one of the most popular tools among Microsoft Support professionals. This tool is used in most of the support incidents to gather SQL Server and general Windows logs and settings, including the failover cluster ones.  When executing MPSReports (for SQL Server you will have to download and run MPSRPT_SQL.exe executable file) we are actually executing a group of scripts and executables behind the scenes; one of these executables, CLUSMPS.EXE, will dump the SQL Server cluster configuration to a text file in the form of SERVERNAME_CLUSTER_MPS_INFORMATION.txt.

    The setup of MPSReports is pretty straightforward but is always recommended you check the ReadMe.txt file to have a better understanding of what test are performed and what log files are generated. You can find this ReadMe.txt here and additional information about this tool here.

     

    After running this tool we will find the cluster configuration file into the resultant CAB file in the MPSReports folder (default path for this folder is %WINDIR%\MPSReports\). We can optionally execute CLUSMPS.EXE directly by opening a Command Prompt windows on the active cluster node and running the tool from %WINDIR%\MPSReports\SQLServer\BIN\x86 path. The following picture describes the command-line options available:

     

    It is important to note that neither CLUSMPS.EXE nor the rest of the tests performed by MPSReports will change the Windows or SQL Server configuration in any way.

    General Cluster Configuration

    The first section of the resultant SERVERNAME_CLUSTER_MPS_INFORMATION.txt file provides a general overview of what nodes are part of the cluster and what cluster groups and resources are located on these nodes. This is an at-a-glance view of the information we can find by using Windows Cluster Administrator (cluadmin.exe) tool.

    This section includes Nodes Information, Group and Resource Information, which will be useful if you need to dig deep for cluster events into cluster.log, (see my previous post for more information on this) and Resource to Guid Information.

    Network Configuration

    The Network Configuration section includes Cluster Networks Information, Cluster Networks Priority, Network Interfaces, and Network Interfaces Binding Order. It is important to do not overlook these settings. In most of the cases not configuring network settings correctly will cause your SQL Server instance to do not perform optimally.

    Cluster Networks Information will show a list of the networks recognized by Microsoft Cluster Services (MCS). On a typical cluster configuration, each one of the cluster nodes will include at least two Network Interface Cards (NICs): One is used for internal cluster communications between nodes (commonly referred as "Heartbeat” network) and the other is used for external communications (“public” network).

    Pay attention to the Network Role column, by default you will see that both the Heartbeat and Public network are configured to support All Communications (“All Comm”) but it is a best practice to modify the Heartbeat configuration so it listens to Internal Communications only. To do so open the Heartbeat network properties on the Cluster Administrator utility (cluadmin.exe) and select the option Internal Cluster Communications Only (Private Network):

     

    Cluster Networks Priority shows the priority of each one of this networks. As a best practice, ensure that the private network is listed with the highest priority. This setting can be changed by accessing the cluster properties on Cluster Administrator utility:

     

    The Network Interfaces Binding Order determines how each network protocol is bound to each services that make use of it. As a general rule, the most-used protocols should be listed first. The recommended binding order for a Windows cluster is as follows:

            • External public network
            • Internal private network (Heartbeat)
            • Other connections, if any

    Network bindings can be configured using the Advanced > Advanced Settings option in Control Panel > Network and Dial-up Connections.

    The following Knowledge Base article provides specific guidelines to configure network components on a Windows cluster: Recommended private “Heartbeat” configuration on a cluster server, Server Clusters: Network Configuration Best Practices for Windows 2000 and Windows Server 2003.

    You can also find best practices to configure the different network components on a cluster on the following TechNet link: Network Configuration Best Practices for Windows 2000 and Windows Server 2003.

    Cluster Resources Dependencies

    On each one of the cluster groups, it is frequent to find resources that depend on others to function properly. These dependencies are depicted on the Dependency Tree List and Dependency List sections of the SERVERNAME_CLUSTER_MPS_INFORMATION.txt file. We usually do not need to take SQL Server cluster dependencies into account, these are automatically configured at setup time and should not be changed from defaults but it is worth the time to check our current dependencies tree against the recommended settings. You can find this information and other special considerations on Knowledge Base article KB835185.

    Here you can find default dependencies for the SQL Server 2000 failover cluster components:

    SQL Server (SHILOH) { SQL Server }
        +(1)-----Depends On-> SQL Network Name(SQL2000)  { Network Name }
            +(2)-----Depends On-> SQL IP Address1(SQL2000)  { IP Address }
        +(1)-----Depends On-> Disk S:  { Physical Disk }


    SQL Server Agent (SHILOH) { SQL Server Agent }
        +(1)-----Depends On-> SQL Server (SHILOH)  { SQL Server }
            +(2)-----Depends On-> SQL Network Name(SQL2000)  { Network Name }
                +(3)-----Depends On-> SQL IP Address1(SQL2000)  { IP Address }
            +(2)-----Depends On-> Disk S:  { Physical Disk }


    SQL Server Fulltext (SHILOH) { Microsoft Search Service Instance }
        +(1)-----Depends On-> SQL Server (SHILOH)  { SQL Server }
            +(2)-----Depends On-> SQL Network Name(SQL2000)  { Network Name }
                +(3)-----Depends On-> SQL IP Address1(SQL2000)  { IP Address }
            +(2)-----Depends On-> Disk S:  { Physical Disk }

    And here are the default dependencies for the SQL Server 2005 failover cluster components:

    SQL Server (YUKON) { SQL Server }
        +(1)-----Depends On-> Disk Y:  { Physical Disk }
        +(1)-----Depends On-> SQL Network Name (SQL2005)  { Network Name }
            +(2)-----Depends On-> SQL IP Address 1 (SQL2005)  { IP Address }


    SQL Server Agent (YUKON) { SQL Server Agent }
        +(1)-----Depends On-> SQL Server (YUKON)  { SQL Server }
            +(2)-----Depends On-> Disk Y:  { Physical Disk }
            +(2)-----Depends On-> SQL Network Name (SQL2005)  { Network Name }
                +(3)-----Depends On-> SQL IP Address 1 (SQL2005)  { IP Address }


    SQL Server Fulltext (YUKON) { Generic Service }
        +(1)-----Depends On-> Disk Y:  { Physical Disk }

    Cluster Service Account Rights

    The Account Privileges section includes a list of the different right required by the Microsoft Cluster Service account as well as any missing permissions. The following example illustrates an scenario where two of the permissions are missing for this account:

                  _____________________________________________
                 |/////////////////////////////////////////////|
                 |//         Current Effective Rights        //|
                 |/////////////////////////////////////////////|

    Act as part of the operating system.
    Back up files and directories.
    Restore files and directories.
    Adjust memory quotas for a process.
    Increase scheduling priority.
    Log on as Service.
    Debug programs.
    Manage auditing and security log.
    Access this computer from the network.

                  _____________________________________________
                 |/////////////////////////////////////////////|
                 |//     Missing Current Effective Rights    //|
                 |/////////////////////////////////////////////|

    Load and unload device drivers.
    Impersonate a client after authentication.

    The required permissions should be configured on each one of the cluster nodes. Failure in configuring proper rights for the Cluster Services account will cause you SQL Server cluster to show permissions error message or to fail completely during startup or failover operations. For a list of what permissions are required by this account in Windows Server 2003 please, refer to the Knowledge Base article KB269229.

    Other Information

    If you plan to make use of Distributed Transaction Services within SQL Server you should plan way ahead how to install the Microsoft Distributed Transaction Coordinator (MSDTC) cluster resource. The Knowledge Base article How to configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 cluster is the best place to start. In case you need to revert or modify an existing MSDTC clustered installation so it can be used by SQL Server, refer to this link.

    For a more in-depth analysis of the general SQL Server cluster does and don’ts I highly recommended to check the following Microsoft Knowledge Base article: Clustered SQL Server do’s, don’ts, and basic warnings.

    The following TechNet link contains a quick reference guide to install and configuring new Microsoft cluster in Windows Server 2003: Quick Start Guide for Server Clusters. For specific SQL Server guidelines refer to SQL Server Books Online.

    During this post I have tried to summarize the key points to look for when checking SQL Server cluster configuration on Windows Server 2003. This is not a complete checklist and working over the different links provided in the post will give you a more complete information. The new Cluster Validation Wizard introduced in Windows Server 2008 cluster simplifies and reduced significantly the overhead associated with cluster configuration in previous Windows versions. If you haven’t had the opportunity to work with Server 2008 cluster, I encourage you to setup your lab and get familiar with this new cluster model.

Page 1 of 1 (2 items)