Buck Woody recently asked a question; how do you design a database. He outlined the process he followed and asked four questions about how each of us do our work: What process do you follow? How important are the business requirements? What tool do you use to create the design, do you need it to diagram, do you even care about diagrams? What's your biggest pain-point about designing? Funny enough, I haven't done a full on database design in over a year. My company just finished about 6 years of very hard-core engineering work, designing and building or redesigning and building, the majority of our data input and collection systems. Then, I was doing lots of design work. Now, we're either largely in maintenance mode for most of those systems, or the…
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…
It's official sports fans. Well, it's been official since last week since Adam Machanic set up the web site. The New England SQL Server Users Group and the Southern New England SQL Server Users Group are again jointly hosting a full day of SQL Server goodness on January 30th. The call for sponsors and speakers is open. We already have several local luminaries lined up to present including Aaron Bertrand on Management Studio Tips & Tricks and Scott Abrants on Automating Database Deployments with Visual Studio. Please register to spend a day with your peers, learning and networking. It's being held at the Microsoft Waltham office, a great facility. We should have a full day with lots to do and learn.
Last week I attended the Dev Connections conference for the first time. It was held in the Mandalay Bay resort in Las Vegas. The conference is different because it doesn't concentrate on any one technology, but combines a bunch of them; ASP, Sharepoint, SQL Server, Visual Studio & Architects. That makes it a bit more like Tech-Ed than the PASS Summit. I showed up on Tuesday night and was there through Friday. I attended a few sessions and presented three of my own on the SQL Server Central track of SQL Server Connection. Overall it was a very good experience. I enjoyed meeting a lot of different people that don't go to the PASS Summit. My sessions went well (except for an odd interruption half way through my morning session…
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.
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.
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…
I can't believe I forgot this: Test, Test, Test And when you're done testing, test some more. You can't assume that you know how something works. You need to know how it works. Test it. Verify your database design by testing. Check your queries by testing them. Be sure the new index works by testing it. Test the new deployment process before you announce it to the development (way before). When you get done with all this testing... Start testing again.
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…
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: [caption id="attachment_379" align="alignnone" width="450" caption="New Database Project"][/caption] Once the new project is created, you need to right click on the References object in the Solution Explorer. Figure 2: From the pop-up menu select Add Database Reference. The window in Figure 3 will pop up: [caption id="attachment_383" align="alignnone" width="450" caption="Add Database Reference"][/caption] You can select the other projects for a dynamic view into their changes, or for a…