AdventureWorks 2008R2 Details

The following changes have been made in the design of AdventureWorks2008 and AdventureWorks2008R2:

The way people and businesses are represented has changed. Some tables have been added, renamed, and deleted. These changes enable better integration with the Microsoft ADO.NET Entity Framework.

New data types and attributes are now demonstrated in the database including:

date and time

hierarchyid

geography

filestream

Integrated full-text support has been incorporated into the database.

The schema introduces the idea of a business entity. A business entity is a person or business which has zero or more addresses and zero or more people related to it. For example, the people related to a store would typically be purchasing agents of a retail
sporting or bicycle store who buy bicycles wholesale from AdventureWorks Cycles. A person related to an employee might be the employee’s emergency contact. All business entities have a common key (the
BusinessEntityID). This allows us to build a conceptual model using the Entity Framework which demonstrates table per type inheritance. It also simplifies the schema by having a single table (Person.BusinessEntityAddress) relate addresses to business
entities instead of using specialized tables (for example CustomerAddress,
VendorAddress, EmployeeAddress.

The concept of a customer has also been broadened. In AdventureWorks for SQL Server 2005, a customer is either a store (wholesale) or an individual (retail). In AdventureWorks2008, a customer can be any person or a store. This enables employees (for example)
to be customers without storing redundant information about them.

The hierarchyid data type is used to organize employees and their managers into a tree, and organize production documents into a tree (much like a file system).

The date and time data types are used instead of the datetime data type when appropriate (date for columns like
HireDate in the HumanResources.Employee table, and time for columns like
StartTime in HumanResources.Shift table).

The integrated full-text feature of SQL Server 2008 is used to demonstrate full-text querying over resumes stored in the
HumanResources.JobCandidate table.

Fictitious addresses stored in the Person.Address table have been fictionally geocoded and the result stored in the
SpatialLocation column to demonstrate the new spatial features in SQL Server 2008.

For specific information about these changes an entity relational diagram showing the conceptual model of business entities, a relational schema diagram, a Transact-SQL script and the CSV data files necessary to build the database are included in the AdventureWorks2008Scripts.msi
installer located in
The Sample Databases at CodePlex project.

AdventureWorks 2008R2 Installation

To install AdventureWorks2008R2 perform the following steps:

1. Make sure you have installed the full-text search engine and enabled FILESTREAM during the installation process for the SQL Server 2008 instance where you will be using the sample database.

Note: EF 1.0 Compatibility Issues

The Entity Framework team would like us to let you know that AdventureWorks2008 is a little bit ahead of the curve in terms of the Katmai features it uses. Some datatypes in AdventureWorks2008 (such as hierarchyid and geometry) are not supported in the entity
framework. The workaround is to exclude tables like Production.Document from your model if possible since there is currently no support for the hierarchyid datatype in Entity Framework 1.0. Unfortunately the Entity Framework tooling which updates your model
from the database will pull in tables like Production.Document even if they were specifically excluded when the model was created, so use of that feature on AdventureWorks2008R2 is not supported at this time. We look forward to a follow-on release of Entity
Framework which has full SQL Server 2008 type support.

Comments

I am running Win 7 64x SQL 2008R2 .....There are some issues with these installs.... I was unable to get filestream working ... I have even ran the MS Hot fix for the service pack but that did not work for me. So I downloaded the Adventureworks that does not use filestream and am now getting this error: The database 'AdventureWorks2008R2' cannot be opened because it is version 661. This server supports version 662 and earlier. A downgrade path is not supported.

*** Any help would be greatly appreciated *** Thanks mtrahan17@cox.net

I am getting an error while installing the AdventureWorks2008R2_RTM.exe:Failed Object Initilization (ISupportInitilize.EdnInit). An exception occurred while executing a Transact SQL statement or batch. Error at object'Databse Selection' in markup file, DatabseInstaller/component/databseselection.xaml' line 78 position 3.

AdventureWorks2008_SR4, while installation produce following error: A fetal occurred during Instalation Detail:Failed Object Initilization (ISupportInitilize.EdnInit). An exception occurred while executing a Transact SQL statement or batch. Error at object'Databse Selection' in markup file, DatabseInstaller/component/databseselection.xaml' line 78 position 3, what should i do , keep in mind i ve log on in administrative mode.Naveed Malaysia

I'm relatively new to databases and I have a problem. I've recently purchased the book 'Pro Entity Framework 4' and it requires that I use the adventureworks database in order to complete the code samples. The problem is that it needs the adventureworks database before the schemas had been updated.

Does anyone know where there is a site that has all of the previous versions of the adventureworks databases, as I've downloaded every version that Microsoft has to offer and non of them are the right one.

I cannot get adventureworks db installed. Even though installation says it completed succesfully, AdventureWorks db is nowhere to be found. I wnat to setup SSIS training for my team and it is becoming very difficult. If Microsoft really wants people to use these products, they should make them more accesible.

I have donwloaded the AdventureWorks for the SQL 2008 to find out how the new GEOGRAPHY-type works. The described column "SpatialLocation" in the table "Person.Adress" does not exists. Very bad: there are no GEOGRAPHY-type in any table! Microsoft: Did you give up the new types?

hello all there was a file called SQL2008.AdventureWorks_OLTP_DB_v2008.x86.msi that have the new DB version and it modification for adventureworks2008currently when i go to the link provided and downloaded the DB i found it the old adventureworks of version 2005does anyone has an idea about where to get the setup file of AdventureWorks2008.msi

I followed the instructions for enabling FILESTREAM for "Transact-SQL access" and "file I/O streaming access" listed in the blog entry referenced in Item 5 above (note that two checkboxes need to be checked). This enables the feature through the SQL Server Configuration Manager (SSCM).

I then followed the instructions given by "debdrup" to set "filestream_access_level" to "2". I opened a "sqlcmd" window by typing "sqlcmd -S yourServerName\sqlexpress" at a cmd shell prompt. I just copied the three commands from this web page and pasted them into the sqlcmd window. After the SQL commands executed, I entered two "exit" commands, one to exit the "sqlcmd" sessions and the second to close the cmd shell window.

Next, I opened an Explorer window, navigated to "C:\Program Files\Microsoft SQL Server\100\Tools\Samples", and double-clicked on the "RestoreAdventureWorks2008.sql" script file. This action opened the script file in a Microsoft SQL Server Management Studio window. I clicked the "connect" button to accept the defaults on the "Connect to Database Engine" dialog. Line 3 of the script must be edited to point the @source_path variable to the location where Microsoft SQL Server was installed. In my case, I used the default installation directory and Line 3 reads as follows:

SET @source_path = 'C:\Program Files\Microsoft SQL Server\100\'

You can save the updated script file if you wish.

Finally, I clicked on the "! Execute" toolbar button to run the script. With the FILESTREAM feature FULLY configured, the script executed without complaint and created a new copy of the AdventureWorks2008 database (I did not have any existing .mdf or .ldf files in the "samples" directory).

The error message you are receiving is caused by a sanity check that was intentionally placed in the original, installed copy of the "RestoreAdventureWorks2008.sql" script file. Line 3 of the script must be edited to point the @source_path variable to the location where Microsoft SQL Server was installed. In my case, I used the default installation directory and Line 3 reads as follows:

SET @source_path = 'C:\Program Files\Microsoft SQL Server\100\'

I will try to update my original procedure to include this step and re-post it.

Hi All,I folowed instruction from JB3 but when i "Execute" to run the script, I got the message " Msg 50000, Level 16, State 1, Line 8@source_path must not be empty. Set this value above to the path for the root directory of your SQL Server 2008 installation" please help me resolve this problem. thank in advance

Now I tried the installation using the SQL script provided with the Database sample "RestoreAdventureWorks2008.sql"I got an error "Filestream disabled" even when I enabled it as per instructions above.I right clicked in the SQLX2008 Server then clicked on "Properties", the click on "Advanced" then Enabled Filestream to "Transact SQl access enabled" and clicked OK. Ran again the script and this time worked OK.

This is my experience installing SQL2008.AdventureWorks_OLTP_DB_v2008.x86.msiI followed all the instructions above. When I reached step 6 I tried to restore the DB using "Restore a DB" with SQL Server Management Studio.I had to create first a blank AdventureWorks2008 DB because Restore asks for a DB Name to restore to. Then I provided the source Backup DB location and name and I got the following error screen:"Microsoft Sql server Management studio failed for server SQLX2008 (This is the name for the instance)The backup Set hold a Backup of a DB other than the existing AdventureWorks2008."So it did not restore it.

Just as a folow-up: Turns out you have to do it both by SSCM and a sp_configure, so here's the command for the latter:EXEC sp_configure 'filestream_access_level', '2'RECONFIGUREGO

Note: I'm not responsible for any effects this may have on what people can do on your server. It just works on my production enviroment, but before anyone tries it I'd suggest someone look into what it does to the database security.

I came from SQL Server 2000 and decided to install SQL Server 2008 for a new project. I installed SQL Server 2008, RC0 Express+ version. For SQL Server 2000 I have one installed program, that being labeled "Microsoft SQL Server" in the programs directory. For SQL Server 2008 I have six program entries, two of which are other versions of SQL Server those being SQL Server 2005, and SQL Server Compact 3.5. Seems to me this could use some explanation. I also spent at least a day wandering through the Microsoft SQL swamp looking for useful vegetation and trying not to get bitten by the varmints. I am feeling pretty well bitten at the moment. You really need to differentiate between Sample Databases and Samples. In paragraph #2, above and many other places the two terms seem to be intertwined. Also how many Adventureworks databases are there? I keep seeing references to what appear to be three different databases, Adventureworks, AdventureworksDW, and AdventureworksAS. I would really like to just be able to download and install a single Adventureworks database for my SQL Server 2008 RC0 Express + code. Is that possible? If so can you point me to some information on how to do that. If I want the code samples I will download those separately.

When I tried to restore the sample databases as instructed above, the script generated the following error.

"OSTST99\SQL2([username]): Msg 3169, Level 16, State 1, Line 1The database was backed up on a server running version 10.00.1300. That version is incompatible with this server, which is running version 10.00.1075. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. OSTST99\SQL2([username]): Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally."

It means I need either a sample database backup that was created in the version 10.00.1075 or I need to update my current server version which is CTP release in the Microsoft Launch 2008 event into 10.00.1300. Please get me updated in this topic.

executed sql as given for installing on AdveturworksDB2008. I get response for the first one as Filestream already enabled and for the restore it says filestream not enabled.please document how to to restore. Also there is no MDF file.