To implement DHCP, you must install and configure the DHCP service on at least one computer running Windows 2000 or 2003 server within the network. For DHCP to function properly, we must manually allocate the IP address on the server and set up the clients for dynamic address configuration. We will install the DHCP service on the first computer, referred to as SYS1 here on.
Use the Add/Remove programs utility in control panel. Then click the Add Windows Components selection.1. Choose Networking services, select Dynamic Host Configuration protocol.
2. Click Next, and you will be prompted for the Windows server 2003 source files. Required files will be copied to your hard disk.
3. Click Finish to close the Windows Components Wizard.
The topology of a network is the pattern used to connect the computers and other devices with the cables or other network medium. There are several basic types of network topologies, or structures in networks. A network can be connected by using any one of the following three topologies:
1.Bus
2.Star
3.Ring
Star topology
In a star topology, each computer is connected to the hub using a separate cable. Most of the Ethernet LANs installed today, and many LANs using other protocols as well, use the star topology. Star LANs can use several different cable types, including various types of twisted-pair and fiber optic cable. The unshielded twisted pair (UTP) cables used on most Ethernet LANs are usually installed using a star topology. Each computer connects to the hub with its own cable, the hub propagates all signals entering through its ports out through all of its other ports. The main advantage of the star topology is that each computer has its own dedicated connection to the hub, providing the network a measure of fault tolerance.
Concept of Ip addressAn important concept of networking is IP addressing. Currently a 32-bit network address is in use all over the world. This address contains 4 octets i.e, four numbers. Each octet can be any number from 0 to 255. Each octet is separated by a period. IP addresses must be unique for each computer in the network. IP addresses commonly fall within three classes: Class A, Class B, and Class C. Class assignments are based on network size and the availability of IP addresses. Each network device needs a unique IP address. The system administrator, or anyone who coordinates IP address assignment and configuration, should assign this address from the pool of addresses he defines. For example if the network contains 25 computers, he may define it as 1 to 25 (192.168.0.1 to 192.168.0.25). In Class A, the first octet refers to network and the next three octets refer to hosts. In Class B, the first two octets refer to network and the next two octets refer to the host. In Class C, the first three octets refer to the network and the last octet refers to host. The term network and host are relevant, when we want to be part of the Internet. If the network we are setting up is not a part of the Internet, then we need to use the following IP addresses of Class C, and they are 192.168.0.0 to 192.168.255.255. These addresses are called private addresses. These addresses are designed to be used within an organization. The last octet in this IP address can be of our choice. Note that in DHCP server we have to provide the range of possible numbers, so that DHCP server will allocate a number with in this range automatically. For public addresses to be used on the internet, InterNIC an organization established to monitor and allocate public IP addresses accepts the requests for these IP addresses and allots the same on a first come first serve basis.
SQL Server Database Server installationAfter we are done with windows operating system installation and setting up we can now go ahead with sql server installation. The important topics in installation and setting up sql server database are:
How to Secure Databases in Sql ServerDatabases have to be secured to ensure that only authorized users can access a SQL Server database. To secure the database the following steps have to be done using the Enterprise Manager. In sql server 2005, there are changes in executing the following steps. If you want to know exactly how it is done in sql server 2005 refer the book titled “Database Programming using visual basic 2005, c# 2005 and sql server 2005”.
1.Creating Logins.
2.Adding Logins to Server Roles.
3.Creating Database Roles.
4.Granting Permissions.
In order for users to get to the data provided by SQL Server, they must first log in to SQL Server. This is the first layer of security that is implemented inside SQL Server itself. SQL Server logins control which individuals or group of individuals have permissions to connect to a SQL Server.
There are two basic types of logins. There are logins (windows logins) that are created internally in SQL Server and logins that reference existing windows users or groups. The standard logins are created by the SQL administrator internally in SQL Server and are primarily for non-windows or remote users to use to log in.
2.Adding Logins to Server RolesAs an administrator, we need to allow other people to perform certain administrative activities on the SQL Server. By default, individuals with login access to SQL Server have no implied privileges on the server. If you require specific logins be allowed to perform certain administrative tasks on the server, you need to give users the permissions to do so.
Server roles are basically groups that exist at the server level. The server roles are built into SQL Server and have specific permissions pre-assigned to them. By adding logins to these Server Roles, you allow those logins to perform the actions for which the role has permissions. For example, adding a login to the dbcreator server role allows those users to create databases in SQL Server. The set of roles that exist at the server level and the permissions assigned to them are all predefined. You cannot create your own server roles, nor can you change their permissions. Because of these restrictions, the server roles are called fixed server roles.
3.Creating Database RolesIn SQL Server, groups are called roles. Server roles exist at the server level and allow their members to perform server wide operations. SQL server also provides a number of built-in roles for each database it contains. The built-in database roles, better known as fixed database roles, provide a convenient mechanism for assigning special database permissions to users. SQL Server does allow you to create your own database roles.
4.Granting PermissionsPermissions need to be assigned for users to be able to execute statements and interact with the objects in a database. Permissions can be assigned using Enterprise Manager. In the enterprise manager, Expand the Databases, select the Northwind database and Click the Tables object in the Enterprise manager tree. Then all the tables will be displayed in the right pane. Click on the Categories table, properties dialog will be displayed. Click on the Permissions button, and then the object properties dialog box will be displayed. A checked box for permission implies that the permission is granted. A box with an X in it implies that the permission has been denied, and a cleared check box means that the permission is revoked.
Understanding the Windows Authentication ModeWindows authentication is generally preferred because it provides an optimal level of integration with Windows 2003 server. User and group accounts from Windows are granted or denied access to SQL Server. Windows 2003 authenticates the user when the user logs on to the network. Because the password is authenticated at network login, SQL Server does not need to know or verify the password of a user. Windows Authentication provides the following advantages over SQL Server Authentication.
Windows Authentication can grant group accounts to access SQL Server, thus minimizing the over head of login administration.
Users are authenticated by Windows 2003, resulting in a secure authentication over a network.
Users could be able to use the same user credentials for network and database access. Audited events can be tracked to a network user.
SQL Server Authentication is preferred in the following scenarios.
1. The user is not logging into a Windows domain.
2. Your network does not have a Windows 2003 domain.
3. It is not feasible to manage all Internet users on the windows domain.
4. You prefer to manage them separately from your normal Windows domain administration.
5. An application is acquired from a vendor that requires SQL Server Authentication.
To add a Standard SQL Login using the Enterprise Manager, follow these steps.
a. Select your server in the Enterprise Manager tree.
b. Expand the Security node, and select logins.
c. Right-click Logins, and select New Logins from the pop-up menu. d. Ensure that the SQL Server Authentication option is selected. e. Enter the name of the SQL login in the Name field. f. Select the default database for the user.
g. Click OK.
h. Confirm the password for the login.
To develop a client application that establishes a connection to a data source, we must plan the way in which the connection is made. This includes determining the security mode of the designated data source, and whether it requires a user ID and Password. Security is necessary to protect the information contained in the database. We usually implement one of two primary security options.
1. Standard security mode. 2. Integrated security mode.1. Standard security mode
Standard security mode is the default security mode for SQL Server. Standard mode uses the SQL Server security model for every connection to the database. It supports non-trusted environments, such as the internet. Note that users will not necessarily first connect to a Microsoft Windows 2003 or NT server for authentication. SQL Server will perform its own authentication in this situation. The system administrator or database owner can create userIDs, user names, and groups for each database on the server. When this mode is in use, the user must enter a user ID and password that has been established for the database.
2. Integrated security modeIntegrated security mode allows SQL Server to use Microsoft Windows 2003 or Windows NT authentication mechanisms to validate all connections to the database. We use integrated security in network environments in which all clients support trusted connections. Integrated security allows applications to take advantage of Windows server security capabilities. With integrated security, user maintain a single user ID and password for both Windows 2003 and SQL Server. If SQL Server is using Integrated Security, the values of user ID and password are ignored.
Sql server uses the service security account to access windows resources. The service security account is the logon account that SQL Server uses to access Windows resources. The service security account is not a login for users connecting to SQL server.
Creating a Dedicated Service Security AccountTwo options exist for the service security account. The first option is local system account and the second option is dedicated domain user account.
The local system account is a Windows 2003 operating system account with full administrator rights on the local computer. We use this account to install SQL server where we need not integrate SQL Server with other server applications such as Exchange server.
The recommended option in production environments is to use a dedicated domain user account. The preferred way to use a dedicated domain user account is to create an account in a Windows Active Directory domain that can be referenced by all the computers involved in a domain. We recommend creating a specific account just for SQL Server rather than share a general network system administrator account. This will help reduce the chance that a network-system administrator will one day delete the account or change the password, causing SQL Server to fail.
We use Active Directory Users and Computers to create and configure a dedicated Windows 2003 user account. The following are the steps to create a service security account.1. Ensure that, you are logged on to the domain controller as Administrator.
2. Click Start->Programs->Administrative tools and then click Active Directory users and computers. The Active Directory users and computers screen appears.
3. In the console tree, expand VKINFOTEK.com. Right click users, point to new and then click user.
4. The New Object – User dialog box appears.
5. In the First name text box, type SQL Service. In the User logon name text box, type SQL Service, and then click next.
6. Type the password in the Password Text Box and Confirm Password text box and select the Password never expires check box, and then click Next.
7. Click the Finish button.
8. In the console tree, click users. The details pane displays a list of all users in the Users container. Verify that the SQL service domain user account has been created and close Active directory users and computers.
In a production setting data entered by the user is stored in a sql server database tables. These tables cab be created using designer or using the scripts. Using scripts is a more professional way of designing the database. Let us now see how it is done.
Creating Tables with SQL ScriptsIf you are developing a database for mass deployment or repeatable installations, the benefits of developing the database schema in scripts become obvious, as listed below.
1. All the code is in one location. Working with SQL scripts is similar to developing an application with Visual Basic or C#.
2. The most current version of the database may be installed without running change scripts or restoring a backup.
Drawbacks
The T-SQL commands may be unfamiliar and the size of the script may become overwhelming. In a situation where foreign-key constraints are embedded within the table, the table-creation order is very strict. If the constraints are applied after the tables are created, the table-creation order is no longer a problem; however, the foreign keys are distanced from the tables in the script.
The following CREATE TABLE DDL command creates the Customer table. The table name, including the name of the owner (dbo), is provided, followed by the table’s columns. The final code directs SQL Server to create the table on the primary filegroup. Apart from the columns, the only information you normally supply when creating a table is the name.
CREATE TABLE dbo.Customer (
CustID INT NOT NULL PRIMARY KEY NONCLUSTERED, CustName VARCHAR(30) UNIQUE NOT NULL, City VARCHAR(50) NOT NULL )
ON [Primary]
Enforcing data integrity ensures that the data in the database is valid and correct. Keys play an important role in maintaining data integrity.
Creating Primary keys
The relational database depends on the primary key. The uses of the primary keys are:
1. To uniquely identify the row.
2. To serve as a useful object for a foreign key.
3. Primary keys can be single columns for fast joins and where clauses.
4. Primary keys never need updating.
5. Note that Primary keys should not contain data that dynamically change, such as a timestamp column, a date-created column, or a date-updated column.
Setting a column, or columns, as the primary key in Enterprise Manager is as simple as selecting the column and clicking the primary-key toolbar button. Enterprise manager creates primary keys with clustered indexes. This is a poor index choice and waste of the one clustered index available for a table.
In the following code, declaring the primary-key constraint in the Create Table statement is shown.CREATE TABLE dbo.Customer (
CustID INT NOT NULL PRIMARY KEY NONCLUSTERED, CustName VARCHAR(30) UNIQUE NOT NULL, City VARCHAR(50) NOT NULL
)
ON [Primary]
Using Identity columns
By far the most popular method for building primary keys involves using an identity column. Like an auto-number column or sequence column on other databases, the identity column generates consecutive integers as new rows are inserted into the database.
Advantages of using Identity column for primary keys:1. Integers are easier to manually recognize and edit than GUIDs.
2. Integers are small and fast
3. An identity column used as a primary key with a clustered index (a common, but poor practice) may be extremely fast when retrieving a single row with a single user. However, that configuration will cause lock-contention hot spots on the database.
CREATE TABLE dbo.Customer (
CustID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED, CustName VARCHAR(30) UNIQUE NOT NULL, City VARCHAR(50) NOT NULL
)
ON [Primary]
GUIDs is a 16-byte hexadecimal number that is essentially unique among all tables, all databases and all servers. The uniqueness is due to the GUID generator using several factors, including the computer NIC code, the MAC address, the CPU internal ID, and the current tick of the CPU clock.
Advantages of using GUIDS1. The randomness of the GUID helps reduce database hot spots by spreading new rows around the table or index and avoiding lock contention.
2. GUIDs discourage users from working with or assigning meaning to the primary keys.
3. GUIDs are forever. The table based on a typical integer-based identity column will hold only 2,147,483,648 rows.
4. Easier to program with GUIDs than with identity columns.
CREATE TABLE dbo.Customer ( CustID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT (NEWID())
PRIMARY KEY NONCLUSTERED, CustName VARCHAR(30) NOT NULL, City VARCHAR(50) NOT NULL
)
ON [Primary]
1. Improves the speed of execution of queries.
2. Enforces uniqueness of data.
3. Speeds joins between tables.
1. Takes disk space to store.
2. Data modification takes longer as indexes have to be updated.
3. Takes time to create an index.
1. Columns that are frequently used in a SELECT list and a WHERE clause.
2. Columns where data will be accesses in sequence by a range of values.
3. Columns that will be used with the GROUP BY or ORDER BY clause to sort the data.
Features of Indexes
1. Indexes accelerate queries that join tables, and perform sorting and grouping
2. Indexes can be used to enforce uniqueness of rows.
3. Indexes are useful on column in which the majority of data is unique. An index on columns containing large amount of duplicate data is not useful.
4. When you modify the data of an indexed column, the associated indexes are updated automatically.
5. Maintaining indexes requires time and resources. You should not create an index that would not be used frequently.
6. The clustered index should be created before nonclustered index. Clustered index changes the order of rows. The nonclustered index would need to be rebuilt if it is built before clustered index.