Sample Databases - SQL Server

Below are the few links to install sample databases on the SQL Server.

http://sqlserversamples.codeplex.com/
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=10679
http://www.microsoft.com/download/en/details.aspx?id=23654
http://blog.sqlauthority.com/2008/08/10/sql-server-2008-download-and-install-samples-database-adventureworks-2005-detail-tutorial/

HELP! - PF Details

REGIONAL OFFICE [KARNATAKA (Bangalore)] Bhavishyanidhi Bhavan, No. 13, Raja Ram Mohan Roy Road,
P.B. No. 25146, Bangalore – 560 025 (KN).
General – EPBAX : 22216566, 22216596, 22214901, 22214961
E-mail: rpfcbglr@dataone.in / rpfcbglr@vsnl.net
Designation Phone Fax R.P.F.C 080-22277700 080-22273991 P.R.O 080-22279130        22384722
- SUB REGIONAL OFFICE  [KARNATAKA (Bangalore)]

BOMMASANDRA

2nd Floor, CMC Building, Begur Road, Bommanahally, Hosur Road, Bommasandra, Bangalore – 560 068. E-mail: srobmsbglr@dataone.in

Designation Phone Fax A.P.F.C (OIC) 080-25734492 080-25733817

K.R PURAM

36, “Lakshmi Complex”, NH-4 (Opposite Syndicate Bank), K.R. Puram, Bangalore – 560 036. E-mail: epfokrpbglr@hotmail.com

Designation Phone Fax A.P.F.C (OIC) 080-25611955        25613382
080-25610209 P.R.O 080-25617629 -

PEENYA

Plot No. S(1), 1st Cross, 1st Phase, Peenya Industrial Estate, Bangalore – 560 058. E-mail: rpfcpeenya@dataone.in

Designation Phone Fax R.P.F.C 080-23721377 080-23721377 General 080-23721355         23721355


More on :- http://www.sarkaritel.com/ministries/min_labour/epfo_index.htm

To Check status and other info :- http://www.epfindia.com/

max degree of parallelism

 If the computer has only one processor, the max degree of parallelism value is ignored.

Value	Edition of SQL Server
1	SQL Server Express
2	SQL Server Workgroup
4	SQL Server 2008 Web
4	SQL Server 2008 Standard
32	SQL Server 2008 Enterprise running on x86 operating systems.

Full Text Indexing

All user-created databases in SQL Server 2008 and later versions are always enabled for full-text indexing.


For previous versions we can find using below method,

SELECT DATABASEPROPERTYEX('DBNAME', 'IsFulltextEnabled');

Result :- 

1 = TRUE
0 = FALSE
NULL = Input not valid

SQL Server AlwaysOn - Part 2 - Availability Groups Setup

Create Sample Database and Create Backups

First thing we need to do is connect to the primary server using SSMS and create two databases and back them up to the network share we created. In this example I'll create database RammerJammer and RollTide and then create database backups.

Specify Name

In SSMS go to Management, right click Availability Groups and click New Availability Group Wizard. Once the wizard appears click Next on the main screen and create a unique Availability Group name on the Specify Availability Group Name screen. I’ll name my group AG-Bama and then click Next.

Select Databases

On the next screen we will need to select our databases that we want added to our availability groups. This screen also has a status column that will let us know ahead of time if our databases meet the prerequisites. I’ll select both databases and click Next.

Specify Replicas

On the next screen click Add Replica… and connect to the other server (Denali2) Replica Mode can be set to Automatic Failover, High Performance, or High Safety.

• Automatic Failover: This replica will use synchronous-commit availability mode and support both automatic failover and manual failover.
• High Performance: This replica will use asynchronous-commit availability mode and support only forced failover (with possible data loss).
• High Safety: This replica will use synchronous-commit availability mode and support only manual failover.

Connection Mode in Secondary Role can be set to Disallow connections, Allow only read-intent connections, or Allow all connections.

• Disallow connections: This availability replica will not allow any connections.
• Allow only read-intent connections: This availability replica will only allow read-intent connections.
• Allow all connections: This availability replica will allow all connections for read access, including connections running with older clients. For this example, I’ll choose Automatic Failover and Disallow connections to my secondary role and click Next.

Specify Availability Group Listener

On the next screen we’ll setup the listener. I’ll take defaults and choose Next.

Select Data Synchronization

The next screen we will need to specify the Perform initial data synchronization option and type the UNC path of the network share we created earlier and click the Next. Hopefully we’ll see SUCCESS for our validations:

Validation

Summary

Click Next to view the summary and Finish to configure or you can click the Script button if you’d like to perform this action at a later time:

Results

During the configuration you can see the wizard complete the following: Configures endpoints Create Availability Group Create Availability Group Listener Join secondary replica to the Availability Group Create a full backup of DB1 Restore DB1 to secondary server Backup log of DB1 Restore DB1 log to secondary server Join DB1 to Availability Group on secondary server Create a full backup of DB2 Restore DB2 to secondary server Backup log of DB2 Restore DB2 log to secondary server Join DB2 to Availability Group on secondary server.  When done click Close to close the wizard. To

View the Availability Group in SSMS

In SSMS, drill down to Management and Availability Groups. Here you will see your Availability Group. Drill down one farther and you’ll see Availability Replicas, Databases, and Group Listeners.

To add a database, replica or view the dashboard right click on the Availability Group Name.

The dashboard will help you determine if your databases are Synchronized and Healthy.

For demo purposes I’ll change my Connection mode in Secondary Role from Disallow all connections to Allow all connections (In SSMS, right click Availability Group and go to Properties) This will allow me to connect to my secondary server and open the databases.

If we had left this set to disallow all connections when we try to open the secondary servers databases we would have received a database is inaccessible error. I am going to create a table called NatlChamps on my primary server in the RammerJammer database.

Within seconds if I switch over to my secondary server, the NatlChamps table is already created.

AlwaysOn is an excellent new feature of SQL Server and I can't wait to use this in my production environments.

SQL Server AlwaysOn Feature - DENALI

Environment:  I have setup two separate VM’s running in VM Workstation with Windows 2008 R2 Enterprise Edition and SQL Server 2008 R2 Enterprise named Denali and Denali2.

First thing we need to do is make sure both servers have .NET Framework 3.0 Features and Failover Clustering installed. To do this, go to Server Manager, Features, Add Feature. Check .NET Framework 3.0 and Failover Clustering and Install as shown below.

Once .NET and Failover Clustering are installed we can configure the cluster. Go to Control Panel, Administrative Tools, Failover Cluster Manager and click Validate a Configuration.

On the “Validate A Configuration Wizard” page click Next, then enter the names of the SQL Servers you want to configure. In this example we are using Denali and Denali2.

Click Next to run through some validation tests. If you receive any errors click View Report to view the errors.

As you can see from above I received errors on my validation, because I am only using one network card. This means I have a single point of failure. In a production environment you would want to fix this, but this is only a warning and does not stop us from configuring clustering. From the main screen click “Create a Cluster”

After clicking "Create a Cluster" and clicking Next on the main page you should see the "Access Point for Administering the Cluster". Here is where you will type in the name of your cluster. This doesn’t need to be your server name. Use a name to distinguish this cluster from other clusters. In this example, I’ll use DenaliCluster.

Click Next to install the cluster.

After the creation, you can see the install report as shown below.

From the Failover Cluster Management screen you can see I have an error regarding my storage solution (no quorum drive) by the yellow exclamation point over my Cluster Name. No worries in a test environment, but you would want to address this in production.

After creating the cluster, we’ll need to install SQL Server Denali on both servers (in this example, I'm using Denali CTP3). On the installation screen we could click "New SQL Server failover cluster installation", but since we're only using Availability Groups we can just use the standard installation, "New SQL Server stand-alone installation…"

Accept all defaults, choose SQL Server Feature Installation and select Database Engine Services and Management Tools – Complete.

Accept defaults, specify a domain user on the Server Configuration screen and click Next. On the Database Engine Configuration screen specify Mixed Mode Authentication and enter a password for the sa account.

Click Next, Next, Next, Install. In a few minutes you should have a working copy of SQL Denali. Once Denali is installed on both servers we’ll need to do a few things to configure it.

Enable TCP/IP: Click Start, Programs, Microsoft SQL Server Denali CTP3, Configuration Tools, SQL Server Configuration Manager. Click SQL Server Network Configuration, Protocols for MSSQLSERVER, and right click TCP/IP and enable.

Enable AlwaysON: In SQL Server Configuration Manager, click SQL Server Services, right click SQL Server (MSSQLSERVER) and choose Properties. Click the AlwaysOn High Availability tab and Enable AlwaysOn Availability Groups.

Add Startup Parameter: In SQL Server Configuration Manager, click SQL Server Services, right click SQL Server (MSSQLSERVER) and choose Properties. Click the Startup Parameters tab, type –T9532 in the startup parameter box and click Add and Restart Services.

Setup Logins: Open SSMS and connect to one of your instances. Click Security, right click Logins and click New Login. Create logins for the accounts you used for your SQL Services. For example, I used SQLBrady/SQLUser for my service accounts so I'm going to make a login for this user and give this login sysadmin rights from the Server Roles page. Do this for both instances.

Create file share for backups and replicas: If you've ever setup log shipping you know you have to have a file share on a server and this is the same for this new feature. Create a file share on one of the servers and give read/write access to all your service accounts. Once clustering is setup, Denali is installed and configured, we can create our first Availability Group for AlwaysOn.

Certification Path