To start with the introduction, DB Pro edition is intended for sandbox development of databases, and it includes tools for database comparison, data generation, unit testing, and database deployment.
I have worked equal number of years on application development using .NET as well as BI projects using legacy and MS BI tools. I have seen .NET project, version-controlled using tools like VSS and TFS. But I had never seen a version control mechanism of a similar standard for managing database objects on SQL Server. The only way that I have been evident in the past was generating scripts for all the DB objects and maintaining versions of the same in VSS. Third-party tools from Redgate and other similar tools have been popularly using the DB admin activities, which were mostly operated by some DBA.
DB Pro edition maintains the true version of the schema via a project-based system that, ideally, should be under version control. DB Pro edition manages each database object as a set of .sql files; it also understands the dependencies and relationships between these objects. This separation of data and schema, from a change management perspective, is a big deal.
Iterative Development Model
Using DB Pro a true iterative development process is possible for database centric projects. For iterative development model, at least two installations of SQL Server would be required: one that will function as your production system, and another as your development system. Depending on the complexity of your database and the software built on top of it, you may choose to have additional installations. For a team environment, you should have a local private instance for each database professional. In addition, you should have at least one shared development instance for integration and regression testing. (Remember, as part of your DB Pro edition license, you also receive a license for SQL Server 2005 Developer Edition, which has all the features of the Enterprise Edition.)
While DB Pro edition will let you create a database from scratch, you most likely have existing databases you need to manage. There are two ways to get your database's schema loaded into a project. The first is to import a SQL script file. This is useful, for example, if you've been managing your schema changes by storing copies of the schema in version control, or when you can't connect to your current version of the schema directly and do a live import due to security or other business requirements.
The second way to load your schema is to connect to a running SQL Server instance and import the schema live. You'd use this option if you've already created a new project or as part of project creation via the New Project wizard. If you work in an environment where you have a DBA, this second option offers a good way to integrate the DBA into the development process. In fact, in a perfect world, when you start working with DB Pro edition against an existing database, the DBA should create the project, load it into source code control, and then hand it off to development, but all the while, the DBA should remain present during the development life cycle.
DB Pro edition shreds the database schema into atomic objects represented as .sql files in the Solution Explorer and thus the file system (not just top-level objects like tables, views, and stored procedures, but indexes, constraints, and the like). In addition, the project gets a set of scripts segmented into a set of folders labeled Pre-Deployment and Post-Deployment. You can use these scripts to handle T-SQL operations that don't represent schema objects. Additionally, you can create your own management and utility scripts and organize them as you see fit.
The offline workspace encourages team collaboration. You also mitigate the inherent risk of making changes to the database schema with the ability to roll back. In addition, Team Foundation Server check-in policies enable teams to control workflow, making sure, for example, that all changes are associated with a work item, that all check-ins have comments, and so on.
The offline workspace provides a way to manage the database schema. However, it does not provide a runtime environment to actually create objects, run queries, or change data. You do this work against a live instance of SQL Server-your sandbox. Once you have imported the production schema, the next step is to publish the schema to your local developer instance of SQL Server.
Automated Data Generation using DB Pro
DB Pro has amazing data generation capabilites, which generally is one of the badly struggled requirement for any BI development project. During the development phase, especially for ETL functional and load testing; as well as for testing aggregations in OLAP part, huge and varied volume of data is inevitable for ensuring thorough implementation of the slated requirements.
A database project can contain any number of data generation plans. Each plan supports data generation for one or more tables in your schema. If you enable a table for data generation, you specify a data generation plan for each column. You can use the built-in generators or you can write your own custom data generators.
When you define a data generation plan, DB Pro edition automatically assigns a default value generator based on the data type of the column. In addition, DB Pro edition is aware of columns that enforce constraints, such as foreign key columns. You can use value generators, available for most of the built-in data types (such as strings, integers, GUIDS) to generate arbitrary data for a column. Each value generator supports a number of properties to control generation. If your column requirements are more complex, you can use the Regular Expression generator for generating phone numbers, postal codes, and the like. You can use the Data Bound generator to load existing data from tables in the database or from another supporting database. With the ability to create custom generators using managed code, you should have no trouble creating really useful data generation plans.
Database Comparison and Synchronization
DB Pro edition lets you compare your project's schema with any running database. In addition, you can compare the data contained in tables and views in two databases using the data comparison tools. You can run either the schema or the data compare tools with or without a DB Pro edition project open.
The schema compare tool allows you to specify the source and target schemas. You can compare a DB Pro edition project to a database, or compare one database to another. It provides all the functionality required to synchronize two database versions which may be deployed in two different evnironments for ex. to sync databases between DEV and FT / UAT / PROD environments.
DB Pro edition can generate a complete script for deploying your database project. This deployment script can do either a complete build or an incremental update. The build process can even consolidate all of your pre- and post-deployment scripts into one complete deployment script.
DB Pro edition uses MSBuild for its build process and supports integration with Team Build if you're using Team Foundation Server.
While DB Pro edition will let you create a database from scratch, you most likely have existing databases you need to manage. There are two ways to get your database's schema loaded into a project. The first is to import a SQL script file. This is useful, for example, if you've been managing your schema changes by storing copies of the schema in version control, or when you can't connect to your current version of the schema directly and do a live import due to security or other business requirements.
The second way to load your schema is to connect to a running SQL Server instance and import the schema live. You'd use this option if you've already created a new project or as part of project creation via the New Project wizard. If you work in an environment where you have a DBA, this second option offers a good way to integrate the DBA into the development process. In fact, in a perfect world, when you start working with DB Pro edition against an existing database, the DBA should create the project, load it into source code control, and then hand it off to development, but all the while, the DBA should remain present during the development life cycle.
DB Pro edition shreds the database schema into atomic objects represented as .sql files in the Solution Explorer and thus the file system (not just top-level objects like tables, views, and stored procedures, but indexes, constraints, and the like). In addition, the project gets a set of scripts segmented into a set of folders labeled Pre-Deployment and Post-Deployment. You can use these scripts to handle T-SQL operations that don't represent schema objects. Additionally, you can create your own management and utility scripts and organize them as you see fit.
The offline workspace encourages team collaboration. You also mitigate the inherent risk of making changes to the database schema with the ability to roll back. In addition, Team Foundation Server check-in policies enable teams to control workflow, making sure, for example, that all changes are associated with a work item, that all check-ins have comments, and so on.
The offline workspace provides a way to manage the database schema. However, it does not provide a runtime environment to actually create objects, run queries, or change data. You do this work against a live instance of SQL Server-your sandbox. Once you have imported the production schema, the next step is to publish the schema to your local developer instance of SQL Server.
Automated Data Generation using DB Pro
DB Pro has amazing data generation capabilites, which generally is one of the badly struggled requirement for any BI development project. During the development phase, especially for ETL functional and load testing; as well as for testing aggregations in OLAP part, huge and varied volume of data is inevitable for ensuring thorough implementation of the slated requirements.
A database project can contain any number of data generation plans. Each plan supports data generation for one or more tables in your schema. If you enable a table for data generation, you specify a data generation plan for each column. You can use the built-in generators or you can write your own custom data generators.
When you define a data generation plan, DB Pro edition automatically assigns a default value generator based on the data type of the column. In addition, DB Pro edition is aware of columns that enforce constraints, such as foreign key columns. You can use value generators, available for most of the built-in data types (such as strings, integers, GUIDS) to generate arbitrary data for a column. Each value generator supports a number of properties to control generation. If your column requirements are more complex, you can use the Regular Expression generator for generating phone numbers, postal codes, and the like. You can use the Data Bound generator to load existing data from tables in the database or from another supporting database. With the ability to create custom generators using managed code, you should have no trouble creating really useful data generation plans.
Database Comparison and Synchronization
DB Pro edition lets you compare your project's schema with any running database. In addition, you can compare the data contained in tables and views in two databases using the data comparison tools. You can run either the schema or the data compare tools with or without a DB Pro edition project open.
The schema compare tool allows you to specify the source and target schemas. You can compare a DB Pro edition project to a database, or compare one database to another. It provides all the functionality required to synchronize two database versions which may be deployed in two different evnironments for ex. to sync databases between DEV and FT / UAT / PROD environments.
DB Pro edition can generate a complete script for deploying your database project. This deployment script can do either a complete build or an incremental update. The build process can even consolidate all of your pre- and post-deployment scripts into one complete deployment script.
DB Pro edition uses MSBuild for its build process and supports integration with Team Build if you're using Team Foundation Server.
Article 
No comments:
Post a Comment