Sql server 2008 R2 setup, configurations & remote accessing using java

1. Introduction
2. System requirements
3. Configurations steps


Introduction


I am going to share my experience about Sql server 2008 R2 setup, configurations & remote accessing using java.

System requirements


Operating system:

Windows 7 (preferable) / Windows Server 2008 standard

Database system:

SQL Server 2008 R2 (preferable) / SQL Server 2008

.Net Framework:

Microsoft .NET Framework 3.5 SP1

Database Management tool:

SQL Server Management Studio

Configurations steps


SQL Server 2008 R2 installation:

1. In Instance Name page , please select the Default instance
2. In Authentication Mode page, please select the Windows Authentication Mode

SQL Server 2008 to allow remote connections:

1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. Click SQL Server Services, make confirm SQL Server (SQLEXPRESS) and SQL Server Browser running.
3. If SQL Server Browser is stopped then select it’s properties and point to Service tab, change the Start Mode Disabled to Automatic, click the apply button, then click start option using right mouse click over SQL Server Browser.
4. Restart the SQL Server(SQLEXPRESS) using right mouse click over SQL Server(SQLEXPRESS).
5. Click SQL Server Network Configuration, point to Protocols for SQLEXPRESS, point to TCP/IP, make sure TCP/IP status is Enabled.
6. Open TCP/IP Properties form using right mouse click over TCP/IP, point to IP Address tab, point to TCP Port in Last section, change TCP Port to 1433, and click Apply button

Create exceptions in Windows Firewall:

1. Click Start, point to Control Panel, point to Windows Firewall Settings
2. Click Change settings link, point to Exceptions tab
3. Click Add port… button, do the following

Name: 1433
Port number: 1433
Protocol: TCP

click OK, and click apply.

Alternative process to create exceptions in Windows Firewall:

1. Click Start, point to Administrative Tools, open Windows Firewall with Advanced Security.
2. Click Inbound Rules, Click New Rule link at the top of right section.
3. Select Port radio button, click next.
4. Select TCP radio button, Enter port number in Specific local ports section such as

Specific local ports: 1433

5. and click next.
6. Select Allow the connection, click next button, again click next button
7. Enter Name Ex. 1433
8. Click Finish button.

Import database using .bak extension file:

1. In SQL Server Management Studio, Right-click the Databases folder, Click the Restore Database.
2. Enter database name into To database section. Ex. testDB
3. Select the From device, and point the testDB.bak file location
4. Click the OK button

To create a SQL Server login that uses SQL Server Authentication (SQL Server Management Studio):

1. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
2. Right-click the Security folder, point to New, and then click Login.
3. On the General page, enter a name for the new login in the Login name box. Ex. test
4. Select SQL Server Authentication.
5. Enter a password for the login. password example: test-1
6. Select the Enforce password policy and Enforece password expirationoptions. In general, enforcing password policy is the more secure option.
7. Make confirm User must change password at next login is not selected.
8. Select Default database as testDB.
9. Click OK.

Add user in testDB database:

1. In SQL Server Management Studio, Open Databases folder, Expand testDB
2. Right-click the Security folder, point to New, and then click User.
3. Enter a User Name, enter login name which has been created. Example: test
4. Select the db_datareader, db_datawriter (as you need) in Owned Schemas section
5. Click OK

Access database using JAVA:

1. Requirement: sqljdbc4.jar
2. Add jar file in Java project
3. Connection string

String conString =jdbc:sqlserver://000.000.0.00\\SQLEXPRESS:1433;databaseName=testDB;userName=test; passWord=test-2;";

Please feel free to ask me any question. I will try my beyond.
Regards, palash

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s