Tag | SQL Server Posts

In a previous blog I describe in twenty one easy steps how to set up continuous integration using a combination of TeamCity and Red Gate tools. Okay, it’s easy only in a relative sense, giving that until recently database continuous integration was almost impossible to set up. One would always suspect that a twenty-one-step process is a long way from the optimal simplicity. Also, having to negotiate the command lines and learning the intricacies of their respective switches and exit codes is tedious ...
While I spend a certain amount of my time creating databases (coding around SQL Server and setup a server when I have to) it isn’t my bread and butter. Since I have run into a number of time that SQL Server needed to be tuned I figured I would step out of my comfort zone and see what I can learn. Brent Ozar packed a mountain of information into his session on making SQL Server faster. I’m not sure how he found time to hit all of his points since he was allowing the audience abuse him on Twitter instead ...
Production databases can get very large. This in itself is to be expected, but when a copy of the database is needed the database must be restored, requiring additional and costly storage. For example, if you want to give each developer a full copy of your production server, you’ll need n times the storage cost for your n-developer team. The same is true for any test databases that are created during the course of your project lifecycle. If you’ve read my previous blog posts, you’ll be aware that ...
The rise in demand for database continuous integration has forced me to skill-up in various new tools and technologies, particularly build servers. We have been using JetBrain’s TeamCity here at Red Gate for a couple of years now, having replaced the ageing CruiseControl.NET, so it was a natural choice for us to use this for our database CI demos. Most of our early adopter customers have also transitioned away from CruiseControl, the majority to TeamCity and Microsoft’s TeamBuild. However, more recently, ...
Because there is so little information on database continuous integration in the wild, I’ve taken it upon myself to aggregate as much as possible and post the links to this blog. Because it’s my area of expertise, this will focus on SQL Server and Red Gate tooling, although I am keen to include any quality articles that discuss the topic in general terms. Please let me know if you find a resource that I haven’t listed! General database Continuous Integration · What is Database Continuous Integration? ...
Developers have long since had to context switch between two IDEs, Visual Studio for application code development and SQL Server Management Studio for database development. While this is accepted, especially given the richness of the database development feature set in SSMS, loading a separate tool can seem a little overkill. This is where SQL Connect comes in. This is an add-in to Visual Studio that provides a connected development experience for the SQL Server developer. Connected database development ...
Testing a database upgrade script as part of a continuous integration process will only work if there is an easy way to automate the generation of the upgrade scripts. There are two common approaches to managing upgrade scripts. The first is to maintain a set of scripts as-you-go-along. Many SQL developers I’ve encountered will store these in a folder prefixed numerically to ensure they are ordered as they are intended to be run. Occasionally there is an accompanying document or a batch file that ...
This post will describe how to set up basic database continuous integration using TeamCity to initiate the build process, SQL Source Control to put your database under source control, and the SQL Compare command line to keep a test database up to date. In my example I will be using Subversion as my source control repository. If you wish to follow my steps verbatim, please make sure you have TortoiseSVN, SQL Compare and SQL Source Control installed. Downloading and Installing TeamCity TeamCity (http://www.jetbrains.com/t... ...
Typically, when I install SQL Server Management Studio and run it for the first time, the first thing I do is to close the Object Explorer Details window. After all, it doesn’t seem to serve a useful purpose, duplicating information that already exists in the Object Explorer. However, I have discovered that it has one important redeeming capability, the ability to allow multi-selection by holding the ctrl or shift key depressed during the selection. Why is this useful? If I want to rename an object, ...
There are a number of free T-SQL prettifiers out there, covering both code layout and syntax highlighting, but Format SQL is different in that it leverages the engine that powers SQL Prompt, the popular add-in that extends SQL Server Management Studio for SQL developers and DBAs. Simply type or paste T-SQL into the top text area, and it formats your SQL on the fly in the lower text area. This was developed by a small team at Red Gate during Down Tools Week, a quarterly occurrence, where everyone ...
Before we start thinking about database continuous integration, described in an earlier blog post, we need to pick a build tool in which we define the commands for our build process. NAnt is a .NET version of Ant, a tool to drive build processes. Like MSBuild, Microsoft’s equivalent, NAnt build scripts can be driven by continuous automation tools, such as TeamCity, CruiseControl, Bamboo and many others. Yes, it would be possible to drive your scripts with DOS batch files or PowerShell, but NAnt specializes ...
As part of my product management role I regularly attend trade shows and man the Red Gate booth in the vendor exhibition hall. Amongst other things this involves giving product demos to customers. Our latest demo involves SQL Source Control and SQL Test in a continuous integration environment. In order to demonstrate quite how easy it is to set up our tools from scratch we start the demo by creating an entirely new database to link to source control, using an individual database name for each conference ...
Although not everyone is practicing continuous integration, many have at least heard of the concept. A recent poll on www.simple-talk.com indicates that 40% of respondents are employing the technique. It is widely accepted that the earlier issues are identified in the development process, the lower the cost to the development process. The worst case scenario, of course, is for the bug to be found by the customer following the product release. A number of Agile development best practices have evolved ...
At http://blog.sqlauthority.com/2012/03/25/sql-server-download-free-ebook-introducing-microsoft-sql-server-2012/ there is a free e-book from Microsoft Press on introducing SQL Server 2012. This book is available as PDF, mobi and epub formats.
I'm in the process of building out a Data Warehouse and encountered this issue along the way.In the environment, there is a table that stores all the folders with the individual level. For example, if a document is created here:{App Path}\Level 1\Level 2\Level 3\{document}, then the DocumentFolder table would look like this:IDID_ParentFolderName1... 121Level 232Level 3To my understanding, the table was built so that:Each proposal can have multiple documents stored at various locationsDifferent ...
Installed package EntityFramework.SqlServerCo... after running my project was getting this error "The operation could not be completed. Unspecified error" Two ways to fix this:1. Install "Visual Studio 2010 SP1 Tools for SQL Server Compact 4" - Web Platforms Installer 2. Download directly offline installer from http://go.microsoft.com/fwl... After running it works great ...
This post shows how to customize the SQL query used by the Team Foundation Server 2010 SQL Server Reporting Services (SSRS) Stories Overview Report. The objective is to show test status for the current version while including user story status of the current and prior versions. Why? Because we don’t copy completed user stories into the next release. We only want one instance of a user story for the product because we believe copies can get out of sync when they are supposed to be the same. In the ...
SSRS is a powerful tool, but there is very little available to measure it’s performance or view the SSRS execution log or catalog in detail. Here are a few simple queries that will give you insight to the system that you never had before. ACTIVE REPORTS: Have you ever seen your SQL Server performance take a nose dive due to a long-running report? If the SPID is executing under a generic Report ID, or it is a scheduled job, you may have no way to tell which report is killing your server. Running this ...
MapRedux – #PowerShell and #Big Data Have you been hearing about “big data”, “map reduce” and other large scale computing terms over the past couple of years and been curious to dig into more detail? Have you read some of the Apache Hadoop online documentation and unfortunately concluded that it wasn't feasible to setup a “test” hadoop environment on your machine? More recently, I have read about some of Microsoft’s work to enable Hadoop on the Azure cloud. Being a "Microsoft"-leaning technologist, ...
SQLSaturday #141 South Florida 2012 will be here in less than 3 months! This is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held June 16, 2012 at Nova Southeastern University, Davie, FL 33314. The focus is on providing a good variety of topics, and making it all happen through the efforts of volunteers. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, ...
Recently I had to create couple SQL Server Reports (SSRS) with optional parameters built in. It took me a while to refresh memory how this can be done. It was very simple to create reports and processes behind, but connecting these two were are little bit challenging – stored procedure was tested and worked fine, but when the report was passing optional parameters it didn’t returned expected results. After tweaking SQL stored procedures and reports parameter options, the following approach turn to ...
Well I like to look at pretty pictures on deployment days to keep my mind on the good side of software. Well I guess it is not that bad, but today we moved Geekswithblogs.net to a new server pool to update hardware, get some extra machines in the mix, and move to SQL Server 2008 R2. If you see any issues, please alert us directly via comments or support@geekswithblogs.com. Thanks and hopefully we will see more benefits that issues. Technorati Tags: Geekswithblogs.net ...
There are pieces of information spread out on this in various postings for SQL Server Reporting Services used in Team Foundation Server. Here are two: Customizing Report Parameters for Team Foundation Server 2010 TFS 2010: Using Stories Progress in your Dashboards but scoped to Iteration or Areas I will attempt to pull together the essential information. First, there are two ways that SSRS reports filter on iteration and area path: …MDXParam (i.e. Query Based) parameter. For example, the Stories ...
The beta for BIDS Helper 1.6 was just released. We have not updated the version notification just yet as we would like to get some feedback on people's experiences with the SQL 2012 version. So if you are using SQL 2012, go grab it and let us know how you go (you can post a comment on this blog post or on the BIDS Helper site itself). This is the first release that supports SQL 2012 and consequently also the first release that runs in Visual Studio 2010. A big thanks to Greg Galloway for doing the ...
In my early development days, I used Microsoft Access for building databases. It made things easy since I only needed to package the database with the installation package so my clients would have access to it. When we began the development of a new package in Visual Studio .NET I decided to use SQL Server Express. It was free and provided good tools - also free. I thought it was a tremendous idea until it came time to distribute our new software! What a surprise. The nightmare Ah, the choices! Detach ...
Eric Ligman, from Microsoft, posted a great blog post this week listing all of the SharePoint 2010 Virtual Labs that are available from Microsoft. His blog entry is here: http://blogs.msdn.com/b/mss... He also posted other resources as well. I’ve copied his Virtual Lab links here: SharePoint Server 2010 Virtual Labs MSDN Virtual Lab: SharePoint Server 2010: Introduction ...
Do you write stored procedures that might be used by others? And those others may or may not have already started a transaction? And your SP does several things, but if any of them fail, you have to undo them all and return with a code indicating it failed? Well, I have written such code, and it wasn’t working right until I finally figured out how to handle the case when we are already in a transaction, as well as the case where the caller did not start a transaction. When a problem occurred, my ...
If you are installing SharePoint 2010 on a new box you might see the Setup Errors notification from the installer. In my case, I am using Windows Server 2008 R2, but I have seen the similar Error on Windows 7 as well with little less requirements. As you can understand all the “-“ bullets are the requirements that needs to be installed or configured on the box. There are two ways to do this 1) Microsoft SharePoint 2010 Product Preparation tool In the SharePoint 2010 Splash Form, you can find that ...
In my attempt of installing and configuring SharePoint 2010 Farm, I have given a new Windows Server 2008 R2 box that should have SQL Server 2008. So when I attempt to install SQL Server 2008 on it, I came across the following error message. Not so weird because every server product have a set of requirement to be installed. First off, before you do any thing Just run the Windows Update. That’s recommended because there can be some thing else that is not mention in this post or any other but can hanged ...
Prior to SSIS 2012 it was not possible to re-use or share the same cache across packages. What that meant is if you created a cache you will be able to consume it only in the very package where the cache was instantiated. Therefore, a developer could take only two routes from here – either make as much heavy ETL processing as possible inside the same package where the cache resides, or populate yet another cache object in every other package where it is needed. The latter was especially harmful leading ...
With as often as I write code that does not perform as well as I would like for it to, I can’t believe how hard it is to write slow code on purpose! My last post gave a mile-high overview of the new version of Redgate’s ANTS Performance Profiler and promised more detail to come. True to my word (this time), this post aims to take a fairly simple application with common “opportunities” and use ANTS Performance Profiler to quantify these opportunities and measure the results. If you’re working along ...
Today's Microsoft Press Ebook Deal of the Day at http://shop.oreilly.com/cat... is 50% off a series of E-books by William R. Stanekinin celebration of the launch of Microsoft SQL Server 2012. This offer will run to 14:00 PT on 8/March/2012. There are 12 other books also available at 50% off ...
We are looking to hire someone with (but not necessarily all of!) :- - Good web skills Javascript / Html / CSS + any javascript frameworks Jquery / Knockout etc - ASP.NET MVC 4 (or earlier) - ASP.NET Webforms ( any exposure ) - SQL Server / NHibernate ( or other ORMs ) - Webservices -WPF ( we do a number of inhouse windows apps ) - A love of software development and good development techniques! You can see what we do at http://www.outpostcentral.com Drop me an email at keith dot nicholas AT outpostcentral.com ...
If you have not heard the latest technical news yet, the SQL Server 2012 Virtual Launch event is today March 8, 2012! Register to not to miss this opportunity to interact with technology experts and win cool prizes! I am thrilled and excited! Why? Because like in the automotive industry, every new iteration of a vehicle is more fuel efficient, safer, more polished, looking more attractive and fun to drive. Sure, the the new version of the SQL Server is ready to take me further in what I can deliver ...
Last week was the Microsoft MVP Global Summit, where Microsoft brings together their independent experts and community leaders for a week of sessions, product group interactions, and some fun along the way. These nights are always epic, stretching into the wee hours of the morning. I brought my camera and flipcam with me this year to capture the events, and so I give you The Most Epic MVP Attendee Party EVAR in Three Acts! It has everything – Playing football on an NFL field, singing karaoke with ...
I have two Great Plains databases, let's call them DB1A and DB1B, that are schematically identical and the data is 99% identical but DB1B has a tiny bit more data. I'm trying to optimize a very complex query that uses views nested in views and is way too long to post all of here. The query was averaging 1 min 43 sec before I started. I noticed that all of the tables involved were heaps (Microsoft's fault, not mine) so I started converting them to clustered one by one, checking the plans and parallelism ...
Recently I have gone through the process of selecting a web hosting company for one of my clients. There are a lot of options out there and a number things you need to be cautious about. I will go over some of the decision points and questions you will want to ask a company before signing a contract. The first thing you need to do is define the features that make up you site. Is it made up purely of static content or does not use a database? If that is the case then you can choose just about any ...
Some Background The AdventureWorks database has been around for over a decade; a staple amongst sample databases. The first version of the AdventureWorks database appeared in time for SQL Server 2000. Microsoft has been good at keeping the AdventureWorks sample database up to date as new versions of SQL Server are released. Case-in-point: SQL Server 2012 is at RC0 and yet you can already find a version of AdventureWorks for it (albeit, it really isn’t that different from the SQL Server 2008 R2 version). ...
A couple of days ago I decided to reinstall the latest Microsoft Developer Training Kits. One of those kits was the Windows Azure Training Kit. It turned out to be a web matrix install that seemed to take forever. More importantly it installed SQL Server 2012 for me. It may have prompted me but I don’t remember but once installed I entered a couple of days of nightmare mode. My RSSBus ADO connection to QuickBooks had stopped working and worst of all LightSwitch was totally broken. The problem is ...
Today's $10 Deal of the Day from APress at http://www.apress.com/97814... is Pro SQL Azure."Pro SQL Azure introduces you to Microsoft's cloud-based delivery of its enterprise-caliber, SQL Server database management system—showing you how to program and administer it in a variety of cloud computing scenarios." ...
Classes starting soon...First topics to start with will be Visual Studio and .NET 4. After that there are sessions on SharePoint 2010 - Admin, Dev & General Usage Inviting all the people who wish to expand their knowledge in the field of Microsoft Technologies. I will be conducting FREE classes on various streams in Microsoft Technologies like: SharePoint - Administration and Development Visual Studio - 2010 C# and .NET 4 LINQ Team Foundation Server 2010 SSIS ASP .NET Programming Office Apps ...
The Problem After setting up a new instance of TFS I attempting to use the TFS 2010 Power Tools (Dec ‘11) Team Foundation Backups wizard. However, during the Backup Plan Wizard Readiness Checks, the “Grant Backup Plan Permissions” step failed with the error – Account… failed to create backups using path… The Fix Digging into the log created during the Readiness Check I found the following error - Error @xxx Microsoft.SqlServer.Managem... Backup failed for Server 'xxx'. ...
This blog provides links to a presentation and a sample application that shows how to load data in SQL Azure using different techniques and tools. The presentation compares the following techniques: INSERT, BCP, INSERT BULK, SQLBulkCopy and a few tools like SSIS and Enzo Data Copy. The presentation contains load tests performed from a local machine with 4 CPUs, using 8 threads (leveraging the Task Parallel Library), to a SQL Azure database using the code provided below. The test loads 40,000 records. ...
This article is written to assist SQL Azure customers to copy a SCHEMA container from one SQL Azure database to another. Schema separation (or compress shard) is a technique used by applications that hold multiple customer “databases” inside the same physical database, but separated logically by a SCHEMA container. At times it may be necessary to copy a given SCHEMA container from one database to another. Copying a SCHEMA container from one database to another can be very difficult because you need ...
When you first create a new table, there is no index created by default. In technical terms, a table without an index is called a “heap”. We can confirm the fact that this new table doesn’t have an index by taking a look at the sysindexes system table, which contains one for this table with an of indid = 0. The sysindexes table, which exists in every database, tracks table and index information. “Indid” refers to Index ID, and is used to identify indexes. An indid of 0 means that a table does not ...
Pros and Cons of NOLOCKI have seen some developers use WITH (NOLOCK) when querying in SQL Server and wonder why they use. Now i explored it and found that its useful to improve the performance in executing the query. However there is a disadvantage in using it. The disadvantage is that one may not be sure that they are getting the data which is currently being updated in the Table ie Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running.With ...
The landscape of SQL Azure backups is changing rapidly. A few tools are becoming available at no charge and Microsoft is adding capabilities over time. Here a quick update.Microsoft ToolsMicrosoft offers two primary backup mechanisms so far:Export/Import feature available on the Azure Management portalThe COPY operation as part of its T-SQL CREATE DATABASE statementThese mechanisms do not offer a scheduling component and do not work together. To obtain a transactionally consistent backup, you first ...
Inviting all the people who wish to expand their knowledge in the field of Microsoft Technologies. I will be conducting FREE classes on various streams in Microsoft Technologies like: SharePoint - Administration and Development Visual Studio - 2010 C# and .NET 4 LINQ Team Foundation Server 2010 SSIS ASP .NET Programming Office Apps & Open XML Basic SQL Server and Queries Windows Azure One Note All the courses will be taught using Microsoft Official Curriculum. Soft copies of Lab material, Virtual ...
Back in 2009 I was tasked with automating database dictionaries and schemas outputs for the CI (Continuous Integration) Server called Hudson ( which is probably now owned by Oracle and will become a name like Jenkins, but I digress. CruiseControl and CruiseControl.NET for Windows is another popular free CI, both of which are superior to the lightweight CI of TFS ).I setup ANT scripts with regular expressions that would get called nightly to rebuild the creation of html and image files with the schemaspy ...