There are certain situations when creating a build script from a database is necessary – when a specific object (table, view, stored procedure, user, etc.) needs to be recreated or rolled back to a state it had earlier. If an online database doesn’t contain this object anymore or contains a newer version of it, the only places to look at are full database backups. If a database is versioned under source control, an object can be easily found there, but let’s assume that only SQL backups are available.April 5, 2013
Sometimes the best test data is the data you have in your live, production SQL Server database. Since using production data is most often not an acceptable option, this requires retrieving the data from a live database table and inserting it into a new table. The challenge arises if you don’t want to insert all records from the original table but only the records that comply with certain conditions.
How to create INSERT INTO script that moves only specific data from one table to another.April 5, 2013
When work on a database in a development environment is completed, developers are required to send the new version in for testing. If the testing is done on one or two machines – this is not a problem. However, if there is a need to test the new version on 10 or 15 machines (with different operating systems, SQL Server versions, or service packs installed), this can be tedious.
Another situation where scripts need to be executed on multiple servers is if there are many databases for different clients that were all derived from the same database and share many common objects. Any changes made in the development must be pushed to all users.April 4, 2013
Even with all precautions taken, mishaps with databases are still a possibility. Although it is good to be prepared for a disaster – the database is in the full recovery model and transaction log and database backups are created regularly, there are still some situations when a problem cannot be solved using an out-of-the-box solution.
One of these issues is restoring a single object. It’s not unusual to delete a SQL database object (e.g. a stored procedure) that is still needed, or modify it when the old version might be required later.April 4, 2013
There are numerous scenarios when data synchronization between the two databases is needed, such as distributing data to different locations, integration of data from different locations into a central repository, or just a simple synchronization between a test and production database.
The recommended solution is to use database replication.
However, even with database replication there’s a need to check whether all the data is synchronized and to synchronize it if needed.April 4, 2013
It’s common knowledge that running database changes directly in production, without thorough testing first, should be avoided.
If there are enough resources in the environment, there would be at least one testing and one production SQL Server instance. However, that introduces another challenge. When everything is set up in the test, and runs smoothly and as expected, how can it easily be applied to the production instance?April 4, 2013
Collaborating on database development introduces a series of challenges. For instance, having the development team change the tables, views, stored procedures and other objects in a single, shared database, although intuitive, can introduce severe issues down the road as valid changes can be lost or overwritten by an unsuspecting teammate. This issue might be mitigated by restoring a database backup – under the assumption a valid backup exists. Even if it does, overwriting the development database with a backup means losing all of the valid database changes that have occurred since the backup was taken; not to mention the fact that restoring a large backup can take time – during which none of the developers can work on the database being restored.April 4, 2013
In scenarios where a development project of a large scale is being completed, DBA skills can really be put to the test. Due to the nature and the dependencies of the software which is being developed, it’s not uncommon for the DBA to be in charge of dozens of environments, used by multiple development teams working on different aspects of the project. In some cases, this scale of development doesn’t only require for multiple SQL Server instances to be set up across environments – more often than not it requires different versions of SQL Server to be accessible across environments as wellApril 4, 2013
Frequently, developers and DBAs need to search databases for objects or data. If you’d ever searched for a database function that contains a specific table column or a variable name, or for a table that contains specific data, you would have found out that there’s no one click solution, such as Ctrl+F
As there is no out-of-the-box solution in SQL Server management Studio, nor Visual Studio, here are a couple of options you can use:April 4, 2013
The first part of the series – SQL Formatting standards – Capitalization, Indentation, Comments, Parenthesis, explains the importance of having clean SQL code. In short, deciphering someone else’s code is time-consuming. Clean and neat SQL code can be read faster; SQL reviewing and troubleshooting is more efficient; joint development efforts are more effective; handing off projects from one team to another is smoother than for inconsistently written SQL.
As there are neither style nor standards to format SQL, it’s up to the team to create its own set of formatting standards. Here are some recommendations to format joins, value lists, code structure, arithmetic, comparison and logical operations.April 4, 2013