Reconstruction of Mixed Fragmentation in Distributed Database Management System
By taheruddin
Distributed Database Management System (DDBMS), the way to manage the bulky demand of information in the current world is working by the combination of networking and Database technology. For deploying DDBMS, major strategies to ensure performance, scalability and reliability are fragmenting with placement of fragments and replication. Fragments are the unit of distribution and the most significant factor to the performance of a DDBMS. But fragments are needed to be reconstruct and/or queried in constructive sense to support end user information need. This hub demonstrates the technique of reconstruction of fragments using Microsoft SQL Server.
Steps followed to accomplish the job of rebuilding from fragments are as follows
- Installation of SQL Server, SQL Server Management Studio Express and network between servers
- Creating fragments (database, relations with data convenient for reconstruction)
- Configuring servers to function remotely
- Creating database users with appropriate privilege for accessing them remotely
- Adding linked server
- Executing queries for reconstruction and processing user request
1. Installation of SQL Server, SQL Server Management Studio Express and Network between Servers
Microsoft SQL server 2005 and Microsoft SQL server 2005 Management Studio Express was installed on 2 computers those are already connected by LAN. Installing SQL server and Management Studio is a very easy task, just following the installer wizard. Computer name of these 2 servers are TAHER and COMREMOTE.
2. Creating Fragments (Database, Relations with Data Convenient for Reconstruction)
3 fragments of a sample relation of product information are placed on 2 servers. One contains fragment 1 when other contains fragment 2 and 3. Database created on the computer TAHER with name local_db and on computer COMREMOTE it was remote_db. Figure and table below depicts the placement of fragmentations.
Product Information Total Relation
Fragments: Schema and Sample Data
3. Configuring Servers to Function Remotely
Configuring server includes the following tasks-
1. Enabling SQL Server authentication together with Windows authentication
- Open SQL Server Management Studio and login using windows authentication
- In the Object Explorer sidebar, right-click on the top SQL Server node, then click Properties.
- In the Server Properties pop-up box, click on Security, then in the Server authentication section select "SQL Server and Windows Authentication mode" and click OK.
2. Checking Surface Area Configuration
- Open SQL Server Surface Area Configuration
- Click on Surface Area Configuration for Services and Connections
- On the left side, expand your SQL Server instance, Database Engine, Remote Connections. On the right side, select Local and remote connections, select using both TCP/IP and named pipes.
- On the left side, select SQL Server Browser, Service.
- On the right side, if the startup type is Disable, change to Automatic and click Apply and click Start button to start the service. Then, click OK
3. Enable protocols and SQL Server Browser
- Open SQL Server Configuration Manager, then expand SQL Server Network Configuration, and click on Protocols for SQLEXPRESS (or MSSQLSERVER).
- Enable all the protocols on right side list.
- Then expand SQL Native Client XX.X Configuration, and click on Client Protocols
- Enable all the protocols on right side list.
- Click on SQL server services, double click on SQL Server Browser, click the Service tab, set Start Mode Automatic, click Apply, and click Ok.
- Start / Restart SQL Server Browser
- Restart SQL Server (SQLExpress)
4. Creating Database Users with Appropriate Privilege for Accessing Them Remotely
1. Creating user
- Login SQL Server (TAHER) using windows authentication.
- Now in the Object Explorer, open the Security node, then right-click on the Logins node and click New Login.
- In the New Login window, enter in the Login name (taheruddin), then select SQL Server authentication. Type in Password and Confirm Password. The password should have letters and numbers. Then uncheck Enforce password expiration and uncheck User must change password at next login. Then change the Default database drop-down box to local_db.
- Then on the left-hand side click User Mapping. In the Users mapped to this login: section check the Map checkbox next to the database local_db. Then in the Database role membership section, check db_owner ("dbo" should be added to the Default Scheme automatically for the user login once saved). Click OK to save changes.
- The new User/login should be added to the database. To check to make sure, go the Object Explorer, expand the Databases node, then open the Security node, open the Users node, see the User you just created is in the list of Users for the database.
- Now we need to provide the user "db_owner" schema ownership and role membership. Double click on the User name (taheruddin), then in the Database User window make sure that db_owner is checked in both Schemes owned by this user and Database role membership. Then click OK.
1. Provide sysadmin privilege (required for adding linked server)
- Under the Security node of the server, expand Server Roles node, find sysadmin and right click on it, click Properties.
- Server Role Properties – sysadmin window appear. On this window, click Add button, in Select Login window click Browse button, check the box for user taheruddin to add.
- Click OK, again click OK, again click OK.
2. Do the steps 1and 2 for the other server (COMREMOTE)
5. Adding Linked Server
Thinking the server taher is local and the server COMREMOTE is to be assessed from taher. So, COMREMOTE need to be added on local one. Steps for adding a linked server-
- Expand the Server Object node under TAHER\SQLEXPRESS, right click on Linked Servers, click on New Linked Server... and New Linked Server window appears.
- In the Linked Server: field, enter the server name to link, COMREMOTE\SQLEXPRESS. Select the radio button SQL Server under Server Type.
- On the page list at the left side click Security, at the below part select Be made using this security context and enter the login name and password (created for SQL Server login on COMREMOTE) respectively in the field Remote login and With password, now click ok.
6. Executing Queries for Reconstruction and Processing User Request:
JOIN command facilitates the joining of Vertical fragmentation and UNION command facilitates the combining of Horizontal fragmentation.
Query (SQL Command) for reconstruction
SELECT Fragment_2.Product_ID, Name, Category, Subcategory, Company, Price, On_hand, Size, Color, Origin FROM ( [COMREMOTE\SQLEXPRESS].remote_db.dbo.Fragment_2 LEFT JOIN local_db.dbo.Fragment_1 ON Fragment_2.Product_ID=Fragment_1.Product_ID ) UNION SELECT* FROM [COMREMOTE\SQLEXPRESS].remote_db.dbo.Fragment_3 GO
The technique shown in this hub is a very basic example of reconstruction. In practical, lots of application access lots of views created form lots of different sources even running varieties of DBMS product such as Oracle, MySQL, and Microsoft SQL. But the basic concept is same for all. Thereby this hub will be very helpful for an inception.
Comments
No comments yet.
