- Posted by Justin on September 30, 2008
I am using the VSTS Database Editon to manage my schema in development as well. For deployments I am using a combination of VSTS Database Edition generate scripts, manually created data migration scripts and nant.
Steps for generating scripts for deployment:
- Create a new database with the schema from the last production release.
- Do a Schema Compare in VSTS DB Edition with the project as the source and the new database as the target. Export to the Editor so that you can get the sql script to run but don’t execute the script yet. This will create you the upgrade script.
- Do a Schema Compare in VSTS DB Edition with the new database as the source and project as the target. Export to the Editor so that you can get the sql script to run but don’t execute the script yet. This will create you the back out script.
- Scan thru the generate scripts and make any additions/changes to handle the data migration. Unfortunately it is hard to auto generate a script to manage the data side of the upgrade.
- To test the scripts, restore a copy of the production database and run the upgrade/back out scripts against it.
Nant Script used for Upgrade Deployment:
<!—List of upgrade scripts -->
<property name="releases" value="3.0.1,3.0.2,3.0.3,3.0.4,3.0.5,3.0.6,3.0.7" />
<!—Reverse list of upgrade scripts used for back out -->
<property name="releases.reverse" value="3.0.7,3.0.6,3.0.5,3.0.4,3.0.3,3.0.2,3.0.1" />
<!—Current production release. Could get from DB. Used to determine how many back out scripts to run -->
<property name="current.version" value="3.0.7" />
<!— Release package number. Used to determine when to start running the upgrade scripts.
<property name="current.release" value="3.0.8" />
<!—Loop thru the releases, run all once you either hit the current release or what is in prod original. -->
<!-- Files are name v.X.X.X.AlterScript.sql and vX.X.X.DataAlterScript.sql -->
<property name="runmorepappscripts" value="false" />
<foreach item="String" in="${releases}" delim="," property="count">
<if test="${property::get-value('count') == property::get-value(current.release')}">
<property name="runmorepappscripts" value="true" />
</if>
<if test="${property::get-value('runmorepappscripts') == 'true'}">
<property name="filename" value="${path::combine(release.database.dir, 'v' + count + '.AlterScript.sql')}" />
<if test="${file::exists(path::combine(release.database.dir, filename))}">
<echo message="Running ${filename} script against ${database.name} database on ${database.server}" />
<sql
connstring=""
transaction="true"
delimiter="GO"
delimstyle="Line"
batch="false"
print="true"
source="${path::combine(release.database.dir, filename)}"
verbose="${verbose}" />
<echo message="Completed ${filename} script against ${database.name} database on ${database.server}" />
</if>
<property name="filename" value="${path::combine(release.database.dir, 'v' + count + '.DataAlterScript.sql')}" />
<if test="${file::exists(path::combine(release.database.dir, filename))}">
<echo message="Running ${filename} script against ${database.name} database on ${database.server}" />
<sql
connstring=""
transaction="true"
delimiter="GO"
delimstyle="Line"
batch="false"
print="true"
source="${path::combine(release.database.dir, filename)}" verbose="${verbose}" />
<echo message="Completed ${filename} script against ${database.name} database on ${database.server}" />
</if>
</if>
<if test="${property::get-value('count') == property::get-value(current.version')}">
<property name="runmorepappscripts" value="true" />
</if>
</foreach>
Nant Script for backout
<!—Loop thru the releases reverse, run all until you hit what was original in prod. Files are name v.X.X.X.AlterScript.sql and vX.X.X.DataAlterScript.sql -->
<property name="runmorepappscripts" value="true" />
<foreach item="String" in="${releases.reverse}" delim="," property="count">
<if test="${property::get-value('count') == property::get-value(‘current.version')}">
<property name="runmorepappscripts" value="false" />
</if>
<if test="${property::get-value('runmorepappscripts') == 'true'}">
<property name="filename" value="${path::combine(release.database.dir, 'v' + count + '.BackoutAlterScript.sql')}" />
<if test="${file::exists(path::combine(release.database.dir, filename))}">
<echo message="Running ${filename} script against ${database.name} database on ${database.server}" />
<sql connstring="" transaction="false" delimiter="GO" delimstyle="Line" batch="false" print="true" source="${path::combine(release.database.dir, filename)}" verbose="${verbose}" />
<echo message="Completed ${filename} script against ${fuzion.database.name} database on ${fuzion.database.server}" />
</if>
<property name="filename" value="${path::combine(release.database.dir, 'v' + count + '.BackoutDataAlterScript.sql')}" />
<if test="${file::exists(path::combine(release.database.dir, filename))}">
<echo message="Running ${filename} script against ${database.name} database on ${database.server}" />
<sql connstring="" transaction="false" delimiter="GO" delimstyle="Line" batch="false" print="true" source="${path::combine(release.database.dir, filename)}" verbose="${verbose}" />
<echo message="Completed ${filename} script against ${database.name} database on ${database.server}" />
</if>
</if>
</foreach>
- Posted by justin on April 16, 2007
Today I started working on my cruise control setup and added my Team Edition for Database Professionals project into the build. Everything went well except that the warning messages from it were taking up a good portion of my log file.
Most of the warnings are coming from the TDS3006 error which basically means that it cannot figure out where a column comes from. To me this error is just about worthless to me since having an alias on my tables/columns seems to cause the problem even though the statement is technically valid.
Luckily enough you can suppress this message. To suppress warning messages for the Team Edition for Database Professional you need to add the error number without the TDS to the suppress warning textbox on the project properties\build.
A complete list of error messages for Team Edition for Database Professionals, http://msdn2.microsoft.com/en-us/library/bb203958(VS.80).aspx
- Posted by justin on February 21, 2007
Who the heck made this decision? This is one of the worst decisions. I frankly don’t plan on install sp2 on any of my production servers since it would take a lot of effort to recover if something did go wrong. I have seen several instances in the past where a sql server pack did have to be uninstalled because some change broke an application and it was not caught before the production install.
SP2 Rollback requires complete reinstallation
- Posted by justin on February 21, 2007
Passing lists to SQL Server 2005 with XML Parameters
Overview
SQL Server 2005's XML capabilities make it a easier to pass lists to SQL Server procedures.
Background
I recently needed to write a stored procedure which took a list of ID's as a parameter. That's one of those things that seems like it would be really simple, but isn't. You'd think you could just pass in a comma delimited string of id's: @ids = '3,5,7,8' and use something like 'SELECT * FROM Products WHERE ID IN (@ids)'. Nope, it doesn't work. I still remember my surprise when I ran into that six or seven years ago.
There are a huge variety of workarounds for this issue - see Erland's comprehensive list ranging form SQL Server 6.5 to 2000. I've used several of these, and while they worked I never liked them. Probably the best method is to just use a SPLIT table valued function which splits your string and returns a table. It's clean, but all of your procedures depend on the existence of that function.
It was also possible to use OPENXML in SQL Server 2000. The syntax was obviously put together by C++ programmers (you have to prepare a document and work with an integer handle, which feels a lot like a pointer), and there were some limitations to be aware of, but it pretty much worked.
This time around, I decided to try this with SQL Server 2005's XML capabilities and see if it was any easier. It is.
Getting started with SQL Server 2005's XML Syntax
XML variables in SQL Server 2005 make it easy to "shred" XML strings into relational data. The main new methods you'll need to use are value() and nodes() which allow us to select values from XML documents.
DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>'
SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)
Which gives us the following three rows:
3
6
15
Alright, just show me how to pass a list in a procedure parameter already!
Here's a proc which takes a single XML parameter. We first declare a table variable (@Products) and load the XML values into it. Once that's done, we can join against the @Products table as if it were any other table in the database.
CREATE PROCEDURE SelectByIdList(@productIds xml) AS
DECLARE @Products TABLE (ID int)
INSERT INTO @Products (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)
SELECT * FROM
Products
INNER JOIN
@Products p
ON Products.ProductID = p.ID
Now we can call it as follows:
EXEC SelectByIdList @productIds='<Products><id>3</id><id>6</id><id>15</id></Products>'
Which gives us the following:
| ProductID |
ProductName |
SupplierID |
CategoryID |
QuantityPerUnit |
UnitPrice |
UnitsInStock |
UnitsOnOrder |
ReorderLevel |
Discontinued |
ID |
| 3 |
Aniseed Syrup |
1 |
2 |
12 - 550 ml bottles |
10 |
13 |
100 |
25 |
0 |
3 |
| 6 |
Grandma's Boysenberry Spread |
3 |
2 |
12 - 8 oz jars |
25 |
120 |
0 |
25 |
0 |
6 |
| 15 |
Genen Shouyu |
6 |
2 |
24 - 250 ml bottles |
15.5 |
39 |
0 |
5 |
0 |
15 |
In order to use this, you'll need to an XML string with your ID's. In our application, Steve was handling the application code, and I talked him into doing this via quick and dirty string concatenation. His method worked great:
public static string BuildXmlString(string xmlRootName, string[] values)
{
StringBuilder xmlString = new StringBuilder();
xmlString.AppendFormat("<{0}>", xmlRootName);
for (int i = 0; i < values.Length; i++)
{
xmlString.AppendFormat("<value>{0}</value>", values[i]);
}
xmlString.AppendFormat("</{0}>", xmlRootName);
return xmlString.ToString();
}
What's next?
This is a very simple use of XML in SQL Server. You can pass complex XML documents containing business objects to insert and update in your relational tables, for instance. If you're going to do that with a large amount of data, have a look at Ayende's clever use of SqlBulkCopy to handle that more efficiently.
- Posted by justin on February 8, 2007
SQL Server 2005 has a ROW_NUMBER Function that can help with paging records for you database applications. ROW_NUMBER returns a sequential number, starting at 1, for each row returned in a resultset.
If I want the first page of 10 records from my log file sorted by Date DESC, I can use the ROW_NUMBER FUNCTION as follows:
SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 1 AND Row <= 10
The second page of 10 records would then be as follows:
SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 11 AND Row <= 20
If you have a lot of records, using TOP X in the inner SELECT clause may speed up things a bit as there is no use returning 1000 records if you are only going to grab records 11 through 20:
SELECT Description, Date
FROM (SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 11 AND Row <= 20
We can rap this up in a Stored Procedure as follows:
CREATE PROCEDURE dbo.ShowLog
@PageIndex INT,
@PageSize INT
AS
BEGIN
WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)
SELECT Date, Description
FROM LogEntries
WHERE Row between (@PageIndex - 1) * @PageSize + 1
and @PageIndex*@PageSize
END
source: http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
Another Article on the same topic from Scott Mitchell (http://feeds.feedburner.com/ScottOnWriting?m=86)
My two latest articles on 4Guys examine how to efficiently page through very large resultsets in ASP.NET 2.0 using SQL Server 2005's new ROW_NUMBER() keyword. While default paging is as simple as point-and-click in ASP.NET 2.0, it's horribly inefficient and naive, bringing back all records from the underlying data source for each page of data displayed. Custom paging, on the other hand, allows you, the developer, to write code/stored procedures that will intelligently bring back the right subset of records.
The performance difference between these two techniques can be profound for sufficiently large resultsets. As I detail in Custom Paging in ASP.NET 2.0 with SQL Server 2005, paging through a 50,000 record table using default paging was nearly two orders of magnitude less efficient than custom paging. One challenge with custom paging, though, is that adding the boilerplate bi-directional sorting functionality is no longer a point-and-click proposition. Fortunately, it's not terribly difficult to implement, although there are a few subtle points that, if missed or overlooked, can impact performance. In Sorting Custom Paged Results I show how to efficiently include bi-directional sorting with custom paging - all it takes is a bit of dynamic SQL in your stored procedure and indexes on the columns that can be sorted.
Implementing custom paging in ASP.NET 2.0 is a good deal more involved than implementing default paging (as was the case in 1.x), but, in my opinion, custom paging in 2.0 is much easier to implement and get working right than in 1.x. (Ditto for default paging in 2.0 vs. default paging in 1.x.)
The whole custom paging vs. default paging brings up a good question: do you use custom paging all the time? That is, whenever creating a data interface using a GridView (or DataGrid), do you always use custom paging, or do you only implement custom paging if you know the resultset will contain hundreds or thousands of records? Personally, I do default paging if there are going to be less than 100 records in the resultset because of the ease of implementing default paging vs. custom paging. This can be a dangerous tactic, though, because even when you might expect that a resultset will remain relatively small, unless the resultset is something like the months of the year or the states in the US, there's no real guarantee that the size won't later swell into something less manageable. ... food for thought!
- Posted by justin on January 19, 2007
By default, SQL Server 2005 Express Edition and SQL Server 2005 Developer Edition do not allow remote connections. To configure SQL Server 2005 to allow remote connections, complete all the following steps:
- Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
- Turn on the SQL Server Browser service.
- Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.
See How to configure SQL Server 2005 to allow remote connections for more details.
- Posted by justin on January 17, 2007
One of the questions that I had using ActiveRecord was how to deal with Text fields in a MS SQL Server Database. I ran across the post below.
From http://www.ayende.com/Blog/2006/12/30/NHibernateAndLargeTextFieldsGotchas.aspx
There are two gotchas associated with working with large text fields with NHibernate.
The first is that you must specify type="StringClob" (or ColumnType = "StringClob" in Active Record), if you are using SQL Server, since NHibernate needs to set the Size property on the parameters.
The second is that if you plan of letting NHibernate generate your schema, you need to specify sql-type="NTEXT" (or SqlType="NTEXT" in Active Record), otherwise NHibernate will generate an NVARCHAR field.
when using SQL-Server 2005 and it's dialect, you can spare the "StringClob" thingie. SQL-Server 2005 allows nvarchar(MAX), which gives you the length of ntext, the full-text indexing ability, and the nvarchar syntax (like, '%', concatenation, etc.)
The trick is the elimination of the "record cannot span pages". since now it can, you can use a "normal" field, with no length limit. (the actual length limit of about 4 zilion characters is not really a limit).
- Posted by justin on January 17, 2007
I have started playing with the ActiveRecord enterprise pattern. Since I mainly program in .NET I am using the Castle Project implementation. So far it has been pretty cool but still trying to understand how to meet all of my user scenarios and performance targets. I would expect to see several post over the next couple of days on ActiveRecord as I learn more about it.
A good blog for in-depth info on ActiveRecord is http://www.ayende.com/Blog/
- Posted by justin on March 17, 2006
You can now take online SQL Server 2005 courses for free - https://www.microsoftelearning.com/catalog/itproDev.aspx#sqlServer. I have not tried any of the courses yet but typically the online MS courses are decent.
- Posted by justin on March 17, 2006
Pretty cool blog post on the history the MS MSDE database and how the SQL Express edition has changed.
http://blogs.msdn.com/euanga/archive/2006/03/09/545576.aspx (external)