SNESSUG 4/14/2010

April 14, 2010 at 7:32 pm (SNESSUG, Visual Studio) (, , , )

Tonight’s Southern New England SQL Server Users group is sponsored by Idera. Our presenter is Scott Abrants of Iron Mountain. He’s talking about deploying databases using Visual Studio Team System:Database Edition. We have a good turnout with 12 people (yeah, we’re small).

Scott’s presentation was a lot of fun and very informative. He’s very involved with automating his deployments to a fare-thee-well. He really has Visual Studio dancing and singing. It was a very thorough overview of the VSTS:DBE soltuion. Other user groups should be jealous that we got to see this presentation.

Permalink 3 Comments

How do -You- use SQL Server

December 1, 2009 at 10:57 am (nHibernate, SCOM, SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, Tools, TSQL, Visual Studio) (, )

I’ve been tagged by a misplaced yankee, uh, New Englander, whatever. The question is, how do I/we use SQL Server where I work. That’s a tough one. It would make a much shorter, and easier, blog post to describe the things we don’t use it for. However, keeping with the spirit of these tags, I’ll try to lay out it.

For those that don’t know, I work for a rather large insurance company. This means that we have lots and lots of databases, but not much data. We also are cheap. That means we’ll run an app into the ground rather than spend the money & time to replace it. We have apps still running from the 70’s and 80’s propped up by ancient men with pocket protectors, spit, bailing wire and happy thoughts. This also means that we have apps running on SQL Server 7, 2000, 2005 and 2008. Give me a couple of weeks and I’m sure I can get an R2 app deployed. There is also a few Oracle databases, our warehouse and Peoplesoft in particular. We even have a DB2 and, I think, one Sybase database somewhere.

I don’t want to go into lots of details about the type of data we store, lest I get in trouble, but basically, think insurance and you’ll get a pretty good idea of a lot of it. Add in the fact that my company prides itself on engineering to avoid risk and you’ll know that we gather quite a bit of information about the things that we insure. There are lots and lots of very small databases. Our largest db’s are just breaking 100gb, but must are in the 20-60gb range. We have a ton of OLTP systems gathering all the different data. These have been developed in just about every possible way. We even have a couple of systems using nHibernate under development. We move, mostly, pretty standard structured data. We have a few processes that are using XML, mostly from third party sources, to import data, so we’ve learned how to shred that stuff into the database. Spatial data, insurance remember, is the big new thing on the block. We’re seeing lots more implementations taking advantage of this. We don’t see much in the way of unstructured data, but some of the reports from the engineers falls into this realm. We also get quite a few photo’s from them that want us to store. We’re working on using FileStream to keep those in sync with the db rather than storing them within the database itself.

Currently, and I hate this, the overwhelming majority of our OLTP data is collected in SQL Server. All our datamarts used for reporting are in SQL Server. BUT, in the middle sits our Oracle warehouse. So we have to convert our data from SQL Server into Oracle and then back into SQL Server. It’s fun. Swapping data types over & over, shrinking column names only to re-expand them into something a human being can read… It’s lots of fun.

We use SSIS for almost all our ETL processes, although we have a few DTS packages still chugging away on some of the 2000 servers. We’re running a bit of replication, but nothing major. We have several fail-over clusters in production. We’re running virtual machines in lots of places. We’re expanding our Reporting Services implementation pretty rapidly. After attending Buck Woody’s sessions at PASS this year we’re getting Central Management Servers and Policy Based Management implemented.

Most of my time is spent working with development teams. We do most our deployment work using Visual Studio. I do database design, stored procedure writing and tuning, data access methods… you name it and I’m involved with it. The level of our involvement varies from team to team, but by & large, we get involved early in most development projects and are able to influence how databases are developed.

For monitoring we’re using Microsoft System Center Operations Manager (or whatever it’s called this week). We’ve modified it somewhat, adding special rules & monitors to keep an eye on the system better. We also use Idera’s SQL Diagnostic Manager to help keep a more detailed eye on the systems. I already mentioned that we use Visual Studio for most of our development & deployments. We also use several Red Gate products, Compare, Data Compare, Prompt, pretty much every day.

That’s pretty much it. We keep busy and keep the systems up, running & happy most of the time.

Because I think they’ll have interesting answers, I’m going to pass this on to Jeremiah Peschka and Tim Ford.

Permalink 4 Comments

Visual Studio Team System Article

June 24, 2009 at 8:06 am (Visual Studio) (, , )

I wrote up an article on how we’re configuring & deploying databases to disparate systems using a combination of database projects, server projects and compound projects in conjunction with configurations that has been published over at SQL Server Central. Please click over & read it.

Permalink Leave a Comment

MSDN Blog on Data Management in VSTS:DB

March 31, 2009 at 2:21 pm (Visual Studio) (, , , , )

Another excellent post over on Barclay Hill’s blog. This time he’s showing how to use pre- and post- deployment scripts to manage data when deployment might result in data loss. I am jazzed for part 2 of this set to see how what I should have been doing all along.

Permalink Leave a Comment

Database.sqlpermissions

March 19, 2009 at 2:29 pm (Visual Studio) (, , , , )

Raise your hand if you think this is a real pain in the bottom method for editing user permissions? Yeah, me too. Visual Studio Team System Database Edition is far to fine a tool to make us edit XML to set database user permissions. A co-worker has posted a change request on MS Connect. Connect works really well as long as people vote for what you report. I’ve seen several things change in SQL Server or get fixed primarily because of the reports in Connect. So if doing this:

<PermissionStatement Action=”GRANT”>
     <Permission>EXECUTE</Persmission>
    <Grantee>UserRole</Grantee>
     <Object Name=”dbo” Type=”SCHEMA”/>
</PermissionStatement>

Makes you crazy and you would rather type this:

GRANT EXECUTE ON SCHEMA :: dbo TO UserRole ;

Then click on the link and get the word in front out to Microsoft.

Permalink Leave a Comment

A Fix for nHibernate’s Parameter Problem

March 11, 2009 at 6:26 am (nHibernate, Visual Studio) (, , , )

I had previously outlined a problem with nHibernate code that, in my opinion, was going to cause a lot of problems on production systems especially with memory and the procedure cache. However, it does appear that there is a possible solution. That doesn’t eliminate my concerns over nHibernate, but it sure does help.

Permalink 1 Comment

MSDN Magazine: Article on VSTS:DB

March 3, 2009 at 9:49 am (Visual Studio) (, , , , )

Jamie Laflen and Barclay Hill have published an article in MSDN Magazine outlining the new features in in VSTS:DB GDR. The description of the intent and use of the server project is extremely enlightening. I wasn’t aware of the master.dbschema files available for use within a project. Luckily I haven’t needed them yet. Another thing I wasn’t aware of, if you use the refactoring tools, say rename a table, not only does it save you a lot of typing, but the project will remember that the table was renamed and instead of dropping and recreating it in the next deployment, it will issue SP_RENAME. I’m spreading that word to my team right now. Another good point is that you can make a configuration to work only on your local machine by setting the “Configuration deployment settings for:” to “My isolated development environment.”

Great article guys. Thanks for putting it out there.

Permalink 2 Comments

VSTS:DB Custom T-SQL Static Code Analysis Rules

February 10, 2009 at 8:52 am (Visual Studio)

Ah, now this is handy. I knew when Barclay Hill started blogging that we’d see good information. I’m going to bounce this off my team and see what we can come up with. If I can get anything good AND get it to work, I’ll post it here.

Permalink Leave a Comment

VSTS:DB More on Composite Projects

February 6, 2009 at 10:47 am (SQLServerPedia Syndication, Visual Studio)

It occurred to me, all the searches coming by looking for information on composite projects in VSTS:DB, maybe instead of wondering how it’s done you’re wondering why it’s done.

In the GDR release there are three kinds of projects you can create; database, server, partial. Database and Server projects are created directly through the VS GUI as new projects and I covered that a bit in the last post. Partial projects are something different.

Partial projects are pieces of structure or code, for example a tally table, that you want to share across multiple projects. Partial projects are created by accessing the object or objects you want to define as a partial project in the Solution Explorer. Right click on the object(s) and select Export As Partial Project. This creates a .files file (repetitive, I know). You can then go to the other projects and, in the Solution Explorer, right click on the project itself and select Import Partial Project. Navigate to where you created the .files partial project (avoided the repetition that time) and select it. It will make the objects defined within a part of your project. It will add a folder within the project definition in Solution Explorer showing the source of the partial project and a date & time so you’ll know when it was imported.

fig11

So, if you have common code that you share amongst your databases, partial projects are the mechanism for defining them and communicating them between projects.

Why then would you make the other types of projects, combining Server and database projects into a composite project. Speaking only for myself, the main reason I would do that is to be able to deploy a database to more than one environment and make changes depending on the environment I’m deploying to. So, for example, you create a database that has all the necessary objects, tables, procs, roles. Then you create multiple server projects for the various environments, Development, Sandbox, QA, Continuous Integration, Staging, Production, whatever. Then you create a composite project combining your database project with each of these server projects. This way, the server project has the necessary security settings for Development, which are different than QA, just as an example. The composite project can have the new roles and security settings unique to the environment. For example, in development, in addition to the needed application roles, you want to have a developer role that has data_reader, data_writer, ddl_admin, security_admin, but you’re revoking create table, create index and some others. If you define that role in the composite project, then you can deploy to development without fear of messing up security. You can create a different composite project for QA and create a QA role that can execute procedures and has data_reader, more than the roles necessary for production, but less than what’s needed in development.

Another example that comes to mind is creating a database project that has structures only. Then you create a composite project that has only stored procedures. The composite can deploy a full database, but you can give the developers access only to the composite project and not to the project that maintains the structure. This way, they can write code, but can’t affect structure and you can control that within source control, let alone at the database level.

The possibilities for the various types of database project management are very wide because of the composite projects in the GDR.

Permalink 3 Comments

VSTS:DB Composite Projects

February 4, 2009 at 11:16 am (SQLServerPedia Syndication, Tools, Visual Studio) (, , , )

I’ve seen several searches go by from people who are looking for how to create composite projects. Here’s a quick run-down. In the example, I’m going to create a main database project called BaseDB, a server project called MyServer, and a composite project that combines the two called Comp. This project is just another database project as you can see in Figure 1:

New Database Project

New Database Project

Once the new project is created, you need to right click on the References object in the Solution Explorer. Figure 2:

fig2

From the pop-up menu select Add Database Reference. The window in Figure 3 will pop up:

Add Database Reference

Add Database Reference

You can select the other projects for a dynamic view into their changes, or for a static look you can select a .dbschema file. Unfortunately, the .dbschema file is very static. You won’t get changes as they’re made. Usually I use the projects.

You can try to affect Database Reference Variables for the project, but usually this isn’t necessary. If there are indirect references you can suppress them. Again, this isn’t something I’ve had to use so far.

Once you’ve created the references to other projects or .dbschema files, you can see these on the References tab of the project Properties as shown in Figure 4:

fig4

You now have a composite project. There are a number of other options you should consider. For example, how are the deployments done. One common approach that I’ve taken is to have the base database do a full rebuild of the database and then have the other two projects do incremental deployments. You’ll need to make sure that all the same Configurations are set up between the projects so that the stuff like the connection strings are the same between projects. You’ll want to make sure that the base project and the composite project are deploying to the same database name.

There’s more, but that should get you started.

Permalink 1 Comment

Next page »