![]() Processes change over time, fields or tables are co-opted to mean different things, or the database that was originally bought was designed for operating a hotel not a hospital, so all the patient data is in the table marked ‘guests’. Note that this can be, and quite often is, rather different to how any particular database or relational ruleset views the business data. As data vault is focused on the business, the first step of a data vault is always to talk to the business – find out what the business call the entities that they interact with, find out how they interact with them, get a feel for how it all hangs together in the mind of the business. With the initial inspection out of the way, we can start getting down to the business of molding this data into a data vault. ![]() Looks like I am going to have to go and whip up some dummy data to finalise out this series – but that’s fine, dummy data can be made. ![]() How terribly disappointing from a demonstration point of view. Looking at the data between the databases, that also appears to be totally unchanged, apart from a slightly anomalous change in the last modified date on the ‘Transaction’ table. Hooray for backwards compatibility, but boo for trying to demonstrate what I wanted. There are some changes to indexes and index methods, but in all major ways, the AdventureWorks database hasn’t changed in 15 years. ![]() At least not from a data relations point of view. However, this isn’t a hard-ball exercise, and so we can just use the in-built ‘Schema Compare’ function in Azure Data Studio.Ĭomparing the 2008 database to the 2012 database to the 2022 database, literally nothing has changed apart from the version numbers. I’ve got some basic scripts that dump all the metadata out to a database that I can then carry forward for checking schema drift, and building up lineage pathways. If you’re hard-balling it gives some great starting places that can be automated to SQL scripts. There’s a lot to look at, depending on how far you want to go with this inspection. We need to see if the data has changed to see if there is more, less, or different data in the tables. We need to see how the database has changed, in case there are new tables, changed fields, etc. Primarily, there are two things that need to be looked at the database, and the data. LDF files of the databases, please contact me and we’ll work something out.Following on from the installation, we can now have a look at what we’ve got. So you can download them by clicking the links below. The reason to share these scripts is because I’m probably not the only one that still wants to use these databases occasionally. In this case DataMasker hosted the files I wanted. I found the diagrams by searching in Google for the name. This makes it a little bit easier to start using these databases. I’ve also included the ERD (Entity-Relationship Diagram) for both databases. You can delete this from the script, or undo this after the generation of the database(s). One thing I added for my own use, is after the databases are created, I set them to Read-Only. So after deleting these from the script, it works perfect. This is removed in SQL 2012, and MSDN advises you to remove this functionality as soon as possible if you still use it in old systems. This is a way to change database options in SQL 2000-2008. Both scripts contain a call to sp_dboption. There’s only 1 thing that doesn’t work if you run the scripts. I’m glad that they decided to add the create script to the. SQL 2000 databases can’t be automatically converted to be SQL 2012 compatible. But if you try to attach these databases to a SQL 2012 instance, you’ll get an error. LDF file of both the Pubs and Northwind databases, and a ReadMe file. My first encounter with SQL Server was on the pubs database, and it still sticks to me as “fun and easy”.īut if you try to find them, you need to download an MSI file that extracts the files to your local system. Those were small databases, that were still understandable for starters. In “Ye Olde Days” I worked with Pubs and Northwind. But for some examples I just want a smaller database. Nowadays Microsoft offers you the AdventureWorks database as extra download for all new versions of SQL Server. Normally I would create my own tables with sample data, but I want to give them the opportunity to repeat the demos again on their own. At the end of my presentation, I wanted to show some demo queries. Last week I was working on a SQL Server presentation, to explain the basic of databases and how SQL Server works to a few colleagues.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |