Search results matching tag 'tsbt-db'

DefaultDataPath

gertd — Mon, 30 Nov 2009 08:00:00 GMT

VSDB uses the the ($DefaultDataPath) SQLCMD variable to represent the location where you would place your data and log file of your database. The deployment engine sets the value of SQLCMD variable by querying SQL Server using the following query:

   1:  DECLARE @value nvarchar(512),

   2:          @rc    int;

   3:  EXEC    @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @value output-- , 'no_output';

   4:  SELECT  @value AS [Value]

The problem is that this value by default is not getting populated when you install SQL Server! You have to set it using SQL Server Management Studio, using the Server properties dialog, on the Database Settings tab, you will find the “Database default locations”.

You can programmatically set it using the following query:

   1:  EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'd:\dbs\MSSQL10.MSSQLSERVER\MSSQL\DATA'

This brings up the next question, what does VSDB return as the value if the DefaultData location Registry entry is not set? It returns the location of the master.mdf file. If you would turn on SQL tracing you will see the following query fly by:

   1:  DECLARE @filepath nvarchar(260),

   2:          @rc int

3:

   4:  EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @filepath output, 'no_output'

5:

   6:  IF ((@filepath IS NOT NULL) AND (CHARINDEX(N'\', @filepath, len(@filepath)) = 0))

   7:      SELECT     @filepath = @filepath + N'\'

8:

   9:  IF (@filepath IS NULL)

  10:      SELECT     @filepath = [sdf].[physical_name]

  11:      FROM       [master].[sys].[database_files] AS [sdf]

  12:      WHERE      [file_id] = 1

13:

  14:  SELECT @filepath AS FilePath

Now this still does not mean it works, because SQL Server or SQL Server Management Studio not validate if this location actually exists and is accessible from within SQL Server.

So the question becomes how can you protect yourself against this inside my deployment?

The following script can be helpful as a pre-deployment script to check if the $(DefaultDataPath) location actually exists and take action accordingly.

DataPathExistsCheck.sql

   1:  -- If we raise an error we want to stop execution of the deployment engine

   2:  :ON ERROR EXIT

3:

   4:  SET NOCOUNT ON

   5:  DECLARE @value nvarchar(512),

   6:          @rc    int;

   7:  EXEC    @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @value output-- , 'no_output';

   8:  -- debug: shows the output

   9:  -- SELECT  @value AS [Value], @rc as [RC];

10:

  11:  DECLARE @result int

  12:  DECLARE @table as table(FileExists int, DirExist int, ParentDirExists int)

  13:  -- debug: shows the output

  14:  -- EXEC [master].[dbo].[xp_fileexist] @value

  15:  INSERT  @table EXEC [master].[dbo].[xp_fileexist] @value

16:

  17:  IF EXISTS (SELECT * FROM @table WHERE DirExist = 1)

  18:  BEGIN

  19:      PRINT ' DIRECTORY ' + @value + ' EXISTS'

  20:  END

  21:  ELSE

  22:  BEGIN

  23:      PRINT ' DIRECTORY ' + @value + ' DOES NOT EXIST'

  24:      RAISERROR('DIRECTORY DOES NOT EXIST', 16, 1)

  25:  END

26:

  27:  :ON ERROR IGNORE

I hope this is helpful and builds a better understanding on how VSDB works under the covers,
GertD @ www.DBProj.com

Oracle provider Beta Available!

camerons — Wed, 22 Jul 2009 07:00:00 GMT

As many of you already know, I started my career at Microsoft as the PUM responsible for building the first version of Database Professionals, which was later merged into the VSTS Developer box.

I was thrilled to see the announce email ( which I have pasted verbatim into this post ) hit my inbox, announcing the beta availability of the Oracle DSP provider that works with the beta version of VSTS 2010. What this essentially means is that with these new bits from Quest Software, you can point the DBPro functionality at an Oracle database!

If you have seen the capabilities that the DBPro feature set enables but have been unable to take advantage of it due to the fact that your organization runs on top of an Oracle database, follow some of the links below!

You'll be glad you did!

Cameron

<BetaAnnounceEmail>

The beta is LIVE!!
It seems like just yesterday that we began working on Project Fuze, the DSP for Oracle database support in Visual Studio Team System 2010… maybe that’s because I didn’t have to write the code! Anyway, after many, many months in our super-secret development lab we’ve finally finished the beta version! The public beta is now available on TeamFuze.net – make sure that you visit Microsoft’s site for more information on their public beta of Visual Studio Team System 2010 since you’ll need to have that running in order to try out Project Fuze.

Your participation is key!
Beta participation is free and open to anyone. However, in order to make the software actually useful to you, we need to hear back from you about the things you like (send that to me…) as well as the things you don’t like (send that to the developers!). Seriously, without your invaluable feedback, the beta is nothing more than a glorified preview. We want to do everything possible to make sure that Project Fuze will deliver an excellent experience and make you more productive in Visual Studio Team System; we can’t do that without your consistent, honest feedback. So please join the forums and post often! We’re listening!

Check out the video!
We recorded a video demonstration of how to use Project Fuze inside of Visual Studio Team System 2010. In it we cover creating a new Oracle database project, importing your schema, managing and altering objects, comparing your changes to the live schema and deploying them back to the database. Don’t forget to bookmark our YouTube playlist for future video updates.

Thanks to all of you for your patience these last few months!

-- The TeamFuze Team

For more information or to opt out of this newsletter, please contact info@teamfuze.net

</BetaAnnounceEmail>

DBSchema file for SQL LiteSpeed XP’s

gertd — Wed, 10 Jun 2009 07:00:00 GMT

Based on a customer request I created a .dbschema file which contains the definitions of all the extended stored procedures used by SQL LiteSpeed 5.0 that live inside the master database. Since schema import does not import the definition of extended stored procedures, the user could no resolve the reference to the extended stored procedures.

The following 39 extended stored procedures are defined inside the dbschema file:

Extended Stored Procedure name

[dbo].[xp_append_file]
[dbo].[xp_backup_database]
[dbo].[xp_backup_log]
[dbo].[xp_delete_tsmfile]
[dbo].[xp_encrypt_decrypt]
[dbo].[xp_file_search]
[dbo].[xp_get_temp_filename]
[dbo].[xp_memory_size]
[dbo].[xp_objectrecovery]
[dbo].[xp_objectrecovery_createscript]
[dbo].[xp_objectrecovery_executeselect]
[dbo].[xp_objectrecovery_viewcontents]
[dbo].[xp_procedure]
[dbo].[xp_read_file]
[dbo].[xp_rebind_tsmmc]
[dbo].[xp_remove_file]
[dbo].[xp_replicate_activity_statistics]
[dbo].[xp_replicate_job_statistics]
[dbo].[xp_restore_attachedfilesonly]
[dbo].[xp_restore_checkpassword]
[dbo].[xp_restore_checksumonly]
[dbo].[xp_restore_database]
[dbo].[xp_restore_filelistonly]
[dbo].[xp_restore_headeronly]
[dbo].[xp_restore_log]
[dbo].[xp_restore_setinfo]
[dbo].[xp_restore_verifyonly]
[dbo].[xp_sls_bcp]
[dbo].[xp_slsAddRegMultiString]
[dbo].[xp_slsReadProgress]
[dbo].[xp_slsRemoveRegMultiString]
[dbo].[xp_slssqlmaint]
[dbo].[xp_sqllitespeed_debug]
[dbo].[xp_sqllitespeed_licenseinfo]
[dbo].[xp_sqllitespeed_version]
[dbo].[xp_view_sls_auditlog]
[dbo].[xp_view_tsmcontents]
[dbo].[xp_view_tsmfilespaces]
[dbo].[xp_view_tsmmc]

You can download the dbschema files from the following SkyDrive location:
http://cid-526eb9977609d51e.skydrive.live.com/self.aspx/DBProj.com/DBSchema/SQLLiteSpeed/SQLLiteSpeed.dbschema.zip

There are 3 dbschema files, one for each version:

I hope this helps others who need to reference SQL LiteSpeed procedures.

GertD @ www.DBProj.com

Declarative Database Development

gertd — Fri, 05 Jun 2009 07:00:00 GMT

Define What You Want, Not How You Get There!

If Not Exists…

Database development is many aspects behind in comparison to regular application development. If you look how database development is integrated with regular application development or development processes it becomes even more obvious that there is room for improvement in this area. When asked, you will find that many database developers are spending their valuable time writing, maintaining, and testing database deployment scripts. The practices may vary, some write them by hand, some use schema comparison tools to generate the script and modify that, and some went all the way to create their own tools to generate these kinds of deployment scripts.

However, the outcome of these efforts is all the same, a set of SQL statements captured inside .SQL script files or embedded as SQL statements inside some application or installer program like WiX. In general they all look like this:

   1:  --

   2:  -- [dbo].[DataLoading]

   3:  --

   4:  IF (OBJECT_ID('[dbo].[DataLoading]', 'U') IS NULL)

   5:  BEGIN

   6:  CREATE TABLE [dbo].[DataLoading]

   7:  (

   8:      [TableName]    SYSNAME NOT NULL,

   9:      [MultiValue]    TINYINT NOT NULL,

  10:      [RowCount]    BIGINT    NOT NULL,

  11:      PRIMARY KEY CLUSTERED

  12:      (

  13:          [TableName],

  14:          [MultiValue]

  15:      )

  16:  )

  17:  END

  18:  GO

Fig 1: A typical fragment of a manually written deployment script

Most scripts are significantly more complicated, especially when you start dealing with incremental version updates. If you ever wrote or maintained one of these scripts you know this is when the problems with this approach become clear. For the simple cases it is all straightforward—check if the object exists, if not create it—this is even true when adding constraints, etc., since the object is easily identifiable. How about adding a column, changing its data type, and renaming a column name or changing the column order inside an index without changing the index name? As soon as you have multiple changes on a single aspect of an object, ordering and the ability to detect which change needs to be made gets very complicated.

Some database developers therefore stamp each and every object, using an extend property or by maintaining a version table, with versioning information about the object. This works great, as long as you are the only one and/or only tool that makes the changes to the schema, since SQL Server can and will not enforce the versioning information.

The fact that SQL Server does not maintain and expose enough useful information when it comes to versioning of the database schema, nor can it make any guarantees that the schema has been changed or tempered with, only compounds the problem.

The next problem with this approach is that the execution time of the deployment will grow over time. What most database developers will do is maintain two scripts, a new deployment and an incremental deployment, in the same fashion as described above. When time progresses you are accumulating lots of changes, which all have to be executed in the same time order to guarantee the right results. This might also lead to having to touch or rebuild objects multiple times, because dependencies between objects cannot be honored between change scripts across time periods. In other words change script v4 to v5 needs to be independent of v7 to v8 and therefore cannot collapse operations together for efficiency sake.

This brings me to the last obstacle for this approach; your script can only reflect your knowledge of the system as you understand at the time you are writing the script. What do I mean by that? Say you are distributing an application; the user has a need for some additional reporting and adds VIEWs to the system inside their own schema, providing abstraction and isolation from your schema. Great practice! They are very happy and add an index over the view, which makes the VIEW schema bound. Now it is time for you to update your schema and you happen to have a need to update one of the tables that is covered by a schema-bound view from the user. You do not know about this view, so the reality is that most deployments will fail at this time.

The Alternative Approach

So what is the alternative you might ask? If you analyze the problems described above there are a couple of conclusions you can draw:

SQL Server does not provide any guarantees about the schema version deployed.
You cannot rely on your knowledge about the state of the system; the system might have changed underneath you, or might be on a different version or is in some weird state.
The number of permutations to validate inside your deployment script is therefore endless and you cannot compensate for all possible cases.

The root cause, however, is the fact that you are encoding state transitions inside your deployment script that are based on a state of the system at time T. When you are deploying this at time T+5 the world might look and be in fact different and all you could do is “fail” the deployment since the outcome of your deployment is now in doubt.

Now what if you describe how you want your schema to look, instead of encoding inside a script how to implement it? This is what declarative database development is about. You define what you want, not how you get there or physically implement the database.

Let’s dive into how the Visual Studio 2008 Team System Database Edition GDR product leverages declarative database development to produce a model-driven deployment of database schemas.

Let There Be a Model

The first thing you need is a model representation of the database schema. The model describes all the objects inside your database schema; this is the “what you want” part. In order to create and fill the model, the system needs a description of the objects. Normally this is provided through a DSL (Domain Specific Language); VSDB uses the regular T-SQL DDL language to define the shape of schema objects, so there is no need to learn a new language or representation. So the model is fed a collection of DDL statements, which are living inside .SQL files, held together by a project file (.DBPROJ), which functions as the container for the source code files.

Fig 2: Database projects are containers of SQL files, which are compiled in to the schema model

The fact that the model is built based on a source-code representation of the database is a huge differentiator. This enables a true offline development experience and better integration with source code control systems and with aspects like build servers, etc. It also means that your model is allowed to be in an inconsistent or incomplete state; you can break dependencies inside the schema and the compiler will immediately flag those.

Fig 3: The Visual Studio error list indicates that your model is in an inconsistent state.

The underlying engine inside VSDB will perform the following actions on top of the source code when building the model:

Parse the source code, this will enforce the syntactic validity and transform the source code into a programmatically accessible representation, which is used to store the object inside the model.
Interpretation, will wire up all the object relationships.
Validation rules, this is the stage where semantic validation is performed, for example an object that has an instead of trigger is not allowed to be part of a transactional replication publication. Two in themselves valid and correct DDL statements, but together mutually exclusive.
Optionally you can enforce more rules over the model through the use of T-SQL Static Code Analysis.

When the model is in a valid and consistent state, you can now perform a build. This will perform all the steps described above, excluding running T-SQL Static Code Analysis since this is an optional step. When no errors are detected, it will persist the resulting model to disk, as a .DBSCHEMA file. The .DBSCHEMA file is effectively an XML serialized format of the database model, which functions as your database schema “blueprint,” this is the manifest that describes what you want inside the database.

How Do I Deploy this Puppy?

Now that you have a model representation, the question is how does it help solve the deployment problem? The easiest way to explain this is to describe the deployment process using the following diagram.

Fig 4: The VSDB deployment engine in action

The first step is to load the source model, if this is provided in the form of a .DBSCHEMA file this is simply deserializing the model.
The second step is to produce a model representation of the target database. This is done by importing the schema the same way you created the initial project representation, except it does not persist the resulting source code representation in this case.
Now that there are two models, compare the two models, taking into account the deployment settings and additional information like the .REFACTORINGLOG file. The deployment settings determine the rules and settings used to compare and to generate the deployment script. The rafactoringlog file is providing additional information to deploy changes that cannot be determined using a difference-based deployment engine. One of the key examples is a rename column operation, which cannot be distinguished from a drop-and-add column of the same type with a different name; however, since there is state associated with the change, it is very important to preserve this information.
The result of the model comparison is a list of differences. The deployment engine will then represent each atomic change as an individual action. Then the optimizer will take the collection of actions, fold them together where possible, separate them if needed, and place them in the correct dependency order.
This information is then used to generate the deployment script, which is a regular .SQL script that can be executed normally using tools like SQLCMD.EXE or SSMS (SQL Server Management Studio).
Optionally you can tell the deployment engine to directly update the target database, but by default only the deployment script is generated, since most people want to validate that it meets the expectations before actually executing.

The end result is that you create a deployment script taking into consideration the state of the schema as it exists in time, based on the “blueprint” of the schema that you provided.

The Icing on the Cake

Since most SQL Server deployments do not have Visual Studio installed, or you simply do not have access from your Visual Studio environment to the database that needs to get updated, there is a fully standalone command-line-based deployment engine that allows you to deploy the results of a build. This command-line engine is XCOPY deployable, so you can copy it onto a memory stick or server share and deploy your database from there.

   1:  "%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd.exe"

   2:  /a:Deploy

   3:  /manifest:MyDatabase.deploymanifest

   4:  /p:DeploymentConfigurationFile=MyDatabase.sqldeployment

   5:  /p:SqlCommandVariablesFile=MyDatabase.sqlcmdvars

   6:  /cs:"Data Source=MyServer\sql2008;Integrated Security=true"

Fig 5: Deploying from the command line using VSDBCMD.EXE

Using VSDBCMD.EXE is as easy as pointing it to the .DEPLOYMENTMANIFEST file, which is a container that holds all the build output together, but the tool allows you to override every part of the manifest.

This includes which deployment settings or variable files are to be used by the deployment engine.

You can also use VSDBCMD.EXE to create a model file from an existing database by using the import option like this:

   1:  "%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd.exe"

   2:  /a:Import

   3:  /cs:"Data Source=(local);Integrated Security=true;Initial Catalog=MySourceDB"

   4:  /dsp:sql

   5:  /model:MySourceDB.dbschema

Fig 6: Creating a model representation from a target database using VSDBCMD.EXE

The resulting file can now be used as the import file for the Schema Comparison tool, which will allow you to perform the visual comparison between two database schemas presented as a .DBSCHEMA file, a Database Project, or a connection to a database. This is an ideal support tool if you need to troubleshoot the differences between two database schemas.

Summary

Declarative database development and Visual Studio 2008 Team System Database Edition GDR can help you streamline your processes and integration of database development tasks and ease the deployment of your database schema, through a great standalone and redistributable deployment engine.

GertD @ www.DBProj.com

RTM of VSDB 2008 GDR R2

gertd — Wed, 22 Apr 2009 07:00:00 GMT

The QFE rollup release for the Visual Studio Team System 2008 Database Edition GDR release is now available.

You can download the SETUP.EXE from the regular location. (http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed)

If you have the VS2008 RTM version of the Database Edition or the RTM version of the VSDB 2008 GDR release installed, this setup will automatically upgrade your installation. If you have the RC installed of the QFE rollup, you have to uninstall first and then re-install the GDR.

For more detail on what got fixed in the QFE rollup, please see my previous posting: http://blogs.msdn.com/gertd/archive/2009/03/26/release-candidate-of-gdr-qfe.aspx

The final version number of the is 9.1.40413.00, so you can check in your Help->About to see if you have the latest version installed.

GertD @ www.DBProj.com

FIX: Blank Unit Test Designer Problem

gertd — Wed, 11 Mar 2009 07:00:00 GMT

If you are running in to the following problem when creating unit tests when using database projects, there now is a fix!

Consider the following scenario, you have you database project (.dbproj) open and requesting the creation of a Database Unit Test by right clicking on for example a stored procedure objects inside Schema View. This will active the Create Unit Tests wizard. However, the following problems occur in the Create Unit Tests wizard:

There are no type lists in the Types panel.
The Output project list is missing.
When you click the Settings button, the wizard closes immediately.
The OK button is unavailable.

This problem is in more detail described in the following KB article, http://support.microsoft.com/kb/962866

A fix is now available at MSDN Code Gallery, see http://code.msdn.microsoft.com/KB962866

-GertD

Oracle DSP Provider and Quest Software

camerons — Wed, 25 Feb 2009 08:00:00 GMT

I just wanted to make sure everyone had heard of the great news that was announced today at VSLive! in San Francisco. Quest Software has announced that it will be building atop the rich extensibility infrastructure that is present in what was the VSTS Database Professionals box ( aka "Data Dude" ), now merged into the VSTS Developer box ( as of last October ), and providing a DSP provider targeting Oracle databases. This is great news, 'cause what this really means is that database developers can now use DBPro functionality against Oracle backends, in much the same way as database developers can work against SQL Server!

Check out some of the press coverage of the announce to learn more...

This was the culmination of a lot of hard work by many people from both Microsoft and Quest. I personally am very glad and happy to see this actually come to pass!

WooHoo! :)

VSTSDB Blog Site

gertd — Mon, 23 Feb 2009 08:00:00 GMT

The Visual Studio Team System Database Edition team has created a new blog site to keep you informed and update about the latest developments.

Please check out: http://blogs.msdn.com/vstsdb

Thanks,
-GertD

DBProj.com

gertd — Thu, 19 Feb 2009 08:00:00 GMT

Some of you already know or noticed through sites like LinkedIn or Facebook that per the beginning of this month I started in a new role inside Microsoft. I moved in to an Software Architect role, working on the new Directory Services stack. Hence I needed a new place to publish my DataDude contributions to the community, this new place is DBProj.com

The goal of the site is very simple, provide as much as possible information about the “Visual Studio Team System 2008 Database Edition GDR” product. Yes, you are reading it correctly, the site will solely focus on the “GDR” release. When a next release will become available that builds on top of the GDR architecture, Visual Studio 2010, I will expand the scope to include that as well. Since it is no longer my primary job, I need to focus on something, I hope you understand.

Anyhow, from now on I will publish on the DBProj.com.

This is the end of a great 3-year journey, but that journey would not have been possible without the support of a great team of testers, program managers and developers.

Thank you all! This site is a contribution to your work and dedication!

Thanks,
-GertD forever a “DataDude”

Data Generator for SQL Server Sparse Columns

gertd — Fri, 06 Feb 2009 08:00:00 GMT

Genevieve, tester on the “DataDude” team, has created a new Data Generator which allows you to populate a random set of sparse columns through the use of an XML column set column. She posted it on the VS Code Gallery, http://code.msdn.microsoft.com/columnsetgenerator.

-GertD

PS: Dank je wel Genevieve!