SQL SERVER – How to Compare the Schema of Two Databases with Schema Compare

Earlier I wrote about An Efficiency Tool to Compare and Synchronize SQL Server Databases and it was very much well received. Since the blog post I have received quite a many question that just like data how we can also compare schema and synchronize it. If you think about comparing the schema manually, it is almost impossible to do so. Table Schema has been just one of the concept but if you really want the all the schema of the database (triggers, views, stored procedure and everything else) it is just impossible task.

If you are developer or database administrator who works in the production environment than you know that there are so many different occasions when we have to compare schema of the database. Before deploying any changes to the production server, I personally like to make note of the every single schema change and document it so in case of any issue , I can always go back and refer my documentation. As discussed earlier it is absolutely impossible to do this task without the help of third party tools. I personally use Devart Schema Compare for this task. This is an extremely easy tool.

Let us see how it works. First I have two different databases – a) AdventureWorks2012 and b) AdventureWorks2012-V1. There are total three changes between these databases. Here is the list of the same.

  • One of the table has additional column
  • One of the table have new index
  • One of the stored procedure is changed

Now let see how dbForge Schema Compare works in this scenario.

First open dbForge Schema Compare studio. Click on New Schema Comparison.

It will bring you to following screen where we have to configure the database needed to configure. I have selected AdventureWorks2012 and AdventureWorks-V1 databases.

In the next screen we can verify various options but for this demonstration we will keep it as it is.

We will not change anything in schema mapping screen as in our case it is not required but generically if you are comparing across schema you may need this.

This is the most important screen as on this screen we select which kind of object we want to compare. You can see the options which are available to select. The screen lets you select the objects from SQL Server 2000 to SQL Server 2012.

Once you click on compare in previous screen it will bring you to this screen, which will essentially display the comparative difference between two of the databases which we had selected in earlier screen. As mentioned above there are three different changes in the database and the same has been listed over here. Two of the changes belongs to the tables and one changes belong to the procedure. Let us click each of them one by one to see what is the difference between them.

In very first option we can see that there is an additional column in another database which did not exist earlier.

In this example we can see that AdventureWorks2012 database have an additional index.

Following example is very interesting as in this case, we have changed the definition of the stored procedure and the result pan contains the same.

dbForget Schema Compare very effectively identify the changes in schema and lists them neatly to developers. Here is one more screen. This software not only compares the schema but also provides the options to update or drop them as per the choice. I think this is brilliant option.

Well, I have been using schema compare for quite a while and have found it very useful. Here are few of the things which dbForge Schema Compare can do for developers and DBAs.

  • Compare and synchronize SQL Server database schemas
  • Compare schemas of live database and SQL Server backup
  • Generate comparison reports in Excel and HTML formats
  • Eliminate mistakes in schema changes propagation across environments
  • Track production database changes and customizations
  • Automate migration of schema changes using command line interface

I suggest that you try out dbForge Schema Compare and let me know what you think of this product.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Auditing and Profiling Database Made Easy with ApexSQL Trigger and ApexSQL Audit

Do you like auditing your database, or can you think of about a million other things you’d rather do?  Unfortunately, auditing is incredibly important.  As with tax audits, it is important to audit databases to ensure they are following all the rules, but they are also important for troubleshooting and security.

There are several ways to audit SQL Server.  There is manual auditing, which is going through your database “by hand,” and obviously takes a long time and is quite inefficient.  SQL Server also provides programs to help you audit your systems.  Different administrators will have different opinions about best practices and which tools to use, and each one will be perfected for certain systems and certain users.

Today, though, I would like to talk about ApexSQL Trigger.  It is an auditing tool that acts like “track changes” in a word processing document.  It will log what has changed on the database, who made the changes, and what effects these changes have had (i.e. what objects were affected down the line).  All this information is logged, and can be easily viewed or printed for easy access.

One of the best features of Apex is that it is so customizable (and easy to use!).  First, start Apex.  Then you can connect to the database you would like to monitor.

Once you select your database, you can select which table you want to audit.

You can customize right down to the field you’d like to audit, and then select which types of actions you’d like tracked – insert, delete, or update.  Repeat these steps for every database you want monitored.

To create the logs, choose “Create triggers” in the menu.  The script written here will be what logs each insert, delete, and update function.  Press F5 to execute.  All this tracking information will be stored in AUDIT_LOG_DATA and AUDIT_LOG_TRANSACTIONS tables.  View these tables using ApexSQL Trigger reports.

These transaction logs can be extremely detailed – especially on very busy servers, where every move it traced.  Reading them can be overwhelming, to say the least.  Apex has tried to make things easier for the average DBA, though.

You can read these tracking logs in Apex, and it will display data and objects that affect your server – even things that were happening on your server before you installed Apex!

To read these logs, open Apex, and connect to that database you want to audit.

Go to the Transaction Logs tab, and add the logs you want to read.

To narrow down what results you want to see, you can use the Filter tab to choose time, operation type, name, users, and more.

Click Open, and you can see the results in a grid (as shown below).  You can export these results to CSV, HTML, XML or SQL files and save on the hard disk.

One of the advantages is that since there are no triggers here, there are no other processes that will affect SQL Server performance.  Using this method is also how to view history from your database that occurred before Apex was installed.  This type of tracking does require storage space for the data sources, as the database must be fully running, and the transaction logs must exist (things not stored in the transactions logs will not be recoverable).

Apex can also replace SQL Server Profiler and SQL Server Traces – which are much more complex and error-prone – with its ApexSQL Audit.  It can do fault tolerant auditing, centralized reporting, and “who saw what” information in an easy-to-use interface.  The tracking settings can be altered by the user, or the default options will provide solutions to the most common auditing problems.

To get started: open ApexSQL Audit, and selected Database Filter Settings to choose which database you’d like to audit.  You can select which tracking you’re like in Operation Types – DML, DDL, queries executed, execute statements, and more.  To get started, click Start Auditing.

After this, every action will be stored in the central repository database (ApexSQLCrd).  You can view the audit and create a report (or view the standard default report) using a wizard.

You can see how easy it is to use ApexSQL Audit.  You can easily set audits, including the type and time, and create customized reports.  Remote users can easily access the reports through the user interface (available online, as well), and security concerns are all taken care of by the program.

 Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – An Efficiency Tool to Compare and Synchronize SQL Server Databases

There is no need to reinvent the wheel if it is already invented and if the wheel is already available at ease, there is no need to wait to grab it. Here is the similar situation. I came across a very interesting situation and I had to look for an efficient tool which can make my life easier and solve my business problem.

Here is the scenario. One of the developers had deleted few rows from the very important mapping table of our development server (thankfully, it was not the production server). Though it was a development server, the entire development team had to stop working as the application started to crash on every page. Think about the lost of manpower and efficiency which we started to loose.  Pretty much every department had to stop working as our internal development application stopped working. Thankfully, we even take a backup of our development server and we had access to full backup of the entire database at 6 AM morning. We do not take as a frequent backup of development server as production server (naturally!).

Even though we had a full backup, the solution was not to restore the database. Think about it, there were plenty of the other operations since the last good full backup and if we restore a full backup, we will pretty much overwrite on the top of the work done by developers since morning. Now, as restoring the full backup was not an option we decided to restore the same database on another server. Once we had restored our database to another server, the challenge was to compare the table from where the database was deleted. The mapping table from where the data were deleted contained over 5000 rows and it was humanly impossible to compare both the tables manually. Finally we decided to use efficiency tool dbForge Data Compare for SQL Server from DevArt. dbForge Data Compare for SQL Server is a powerful, fast and easy to use SQL compare tool, capable of using native SQL Server backups as metadata source. (FYI we Downloaded dbForge Data Compare)

Once we discovered the product, we immediately downloaded the product and installed on our development server. After we installed the product, we were greeted with the following screen.

We clicked on the New Data Comparision to start our new comparison project. It brought up following screen.

Here is the best part of the product, we just had to enter our database connection username and password along with source and destination details and we are done. The entire process is very simple and self intuiting.

The best part was that for the source, we can either select database or even backup. This was indeed fantastic feature. Think about this, if you have a very big database, it will take long time to restore on the server. Once it is restored, you will be able to work with it. However, when you are working with dbForge Data Compare it will accept database backup as your source or destination.

Once I click on the execute it brought up following screen where it displayed an excellent summary of the data compare. It has dedicated tabs for the what is changing in what table as well had details of the changed data. The best part is that, once we had reviewed the change. We click on the Synchronize button in the menu bar and it brought up following screen.

You can see that the screen has very simple straight forward but very powerful features. You can generate a script to synchronize from target to source or even from source to target. Additionally, the database is a very complicated world and there are extensive options to configure various database options on the next screen. We also have the option to either generate script or directly execute the script to target server. I like to play on the safe side and I generated the script for my synchronization and later on after review I deployed the scripts on the server.

Well, my team and we were able to get going from our disaster in less than 10 minutes. There were few people in our team were indeed disappointed as they were thinking of going home early that day but in less than 10 minutes they had to get back to work.

There are so many other features in  dbForge Data Compare for SQL Server, I am already planning to make this product company wide recommended product for Data Compare tool. Hats off to the team who have build this product.

Here are few of the features salient features of the dbForge Data Compare for SQL Server

  • Perform SQL Server database comparison to detect changes
  • Compare SQL Server backups with live databases
  • Analyze data differences between two databases
  • Synchronize two databases that went out of sync
  • Restore data of a particular table from the backup
  • Generate data comparison reports in Excel and HTML formats
  • Copy look-up data from development database to production
  • Automate routine data synchronization tasks with command-line interface

Go Ahead and Download the dbForge Data Compare for SQL Server right away. It is always a good idea to get familiar with the important tools before hand instead of learning it under pressure of disaster.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Three Efficiency Tools for SQL Server from Devart

I just returned from successful road trip of TechEd India. The trip was extremely successful and I have got big chance to engage with community and friends. One of the most frequently asked question during the trip was what kind of efficiency tools do I use while working with SQL Server. I use many different tools and here is the list of my most favorite tools from Devart. If you are using them, do let me know as I would like to get your feedback about the tools.

SQL SERVER – Auto Complete and Format T-SQL Code – Devart SQL Complete

Some people call it laziness, some will call it efficiency, some think it is the right thing to do. At any rate, tools are meant to make a job easier, and I like to use various tools. Wanting a more efficient way to do something is not inherently lazy.  That’s how I see any efficiency tools. A few days ago I found Devart SQL Complete.  It took less than a minute to install, and after installation it just worked without needing any tweaking.  Once I started using it I was impressed with how fast it formats SQL code – you can write down any terms or even copy and paste.  The worst thing to encounter is code that goes all the way to the right side, and you have to scroll a million times because there are no breaks or indents.  SQL Complete will take care of this for you – if a developer is too lazy for proper formatting, then Devart’s SQL formatter tool will make them better, not lazier.

SQL SERVER – Development Productivity Tool – dbForge Studio for SQL Server

I have talked a little bit about dbForge before, but I would like to do a full product review now. Devart’s dbForge Studio for SQL Server is a fantastic tool for SQL Server development, as soon as I installed it I found so many things to learn. First off, it will increase SQL coding almost instantly. There is very little to learn, you are not just memorizing codes to “cheat” off of. DbForge Studio provides code completion options and automatic SQL formatting, so that you know your code will work. One of my favorite feature is “snippets,” which stores parts of code that you use over and over to cut down on typing and searching – because you know they’re always a few commands you use again and again!

SQL SERVER – Autocomplete and Code Formatting Tool – SQL in Sixty Seconds #041 – Video

Developers are often very busy in writing code and building application rapidly while racing against strict deadline. Developers want a tool which can help them aid them to write faster code along with a tool which auto format the code, so when they visit the code again they can read it. I came across a similar tool which can help achieve both the goals.

You can download the Devart SQL Complete demonstrated in this video for free.

Let us see the same concept in following SQL in Sixty Seconds Video:

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – SQL Server Data Tools – Business Intelligence for Visual Studio 2012 – SQL Server 2012 Data-Tier Application Framework

Microsoft SQL Server Data Tools provides an integrated environment for database developers to carry out all their database design work for any SQL Server platform within Visual Studio.  The SQL Server Object Explorer in Visual Studio offers a view of your database objects similar to SQL Server Management Studio. SQL Server Object Explorer allows you to do light-duty database administration and design work. You can easily create, edit, rename and delete tables, stored procedures, types, and functions. You can also edit table data, compare schemas, or execute queries by using contextual menus right from the SQL Server Object Explorer. Database developers can use the SQL Server Object Explorer in VS to easily create or edit database objects and data, or execute queries. The Microsoft SQL Server 2012 Data-Tier Application Framework (DACFx) is a component which provides application lifecycle services for database development and management for Microsoft SQL Server and Windows Azure SQL Databases.

Download SQL Server Data Tools – Business Intelligence for Visual Studio 2012

Download SQL Server 2012 Data-Tier Application Framework

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Development Productivity Tool – dbForge Studio for SQL Server

I have talked a little bit about dbForge before, but I would like to do a full product review now. Devart’s dbForge Studio for SQL Server is a fantastic tool for SQL Server development, as soon as I installed it I found so many things to learn.

First off, it will increase SQL coding almost instantly. There is very little to learn, you are not just memorizing codes to “cheat” off of. DbForge Studio provides code completion options and automatic SQL formatting, so that you know your code will work. One of my favorite feature is “snippets,” which stores parts of code that you use over and over to cut down on typing and searching – because you know there always a few commands you use again and again! Another time saver is the hints option, which will show you information about objects, and the navigation tool that allows toggling between items using only the F12 key.

Of course, all these features would simply be interesting trivia if you couldn’t design and alter tables, too. But dbForge Studio has that covered as well. Tables are always the hardest part, but with Table Designer, you can quickly set the properties using the visual table editors, then edit the script and rebuild as needed, previewing changes before going live. It really takes a lot of the stress out of altering tables.

Changing your database and the information stored there can also be a stress-filled event. But with Database Diagram tool, you can also edit and manipulate your database quickly and preview changes.  Scaling, grouping, and printing are no problem with Database Diagram. You can also build virtual connections to help visualize your data.

Of course, coding and modifications can seem simple in comparison to tackling the data within the database, especially when there is a complex structure and multiple dependencies. With Schema Compare, you can synchronize your database, even when they are extremely complex. You will also be alerted if there are problems so you can solve problems early, before they become catastrophes.  Schema Compare takes “snap shots” of your SQL Server database for easy comparisons later, which heads off drift. And if you are thinking that this is too good to be true, Schema Compare also has deployment script that help target different SQL Server editions, so it doesn’t matter what version you use, you can still use this tool.

DbForge Studio also allows automating deployment of the database to the production server. There are multiple tools available to help with this, including T-SQL Debugger, Query Profiler, Query Builder, and Data Import & Export. All of these tools can be scheduled and automated, and use easy “point and click” options rather than tedious coding.

While all this automating sounds great, anyone who has had to create and deal with reports is probably wondering if this can be automated, as well. The answer is yes! The Data Report Designer will compile your data and create a report in any of nine formats, so you have the perfect feedback. You can also schedule automatic report delivery.

And last, but certainly not least, let’s deal with the security features in dbForge Studio . I cannot say enough about how important security is, and how well Devart’s tool deals with this subject. The Security Manager feature allows the user to create visual editors for users, log-ins, and roles, and provides a feature for batch editing of objects. Of course, one of the most difficult tasks in security is fine-tuning the security to each individual user. You probably see where I am heading with this – the Security Manager allows setting up different privileges per schema object.

I hope this quick review has convinced some DBAs (and non DBAs!) to give dbForge Studio for SQL Server a try. I am sure you will not be disappointed. I certainly was not! Check out – dbForge Studio for SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Hide Yourself from SQL Server? – Guest Post by Balmukund Lakhani

Balmukund Lakhani (Blog | Twitter | Site) is currently working as Technical Lead in SQL Support team with Microsoft India GTSC. In past 7+ years with Microsoft he was also a part of the Premier Field Engineering Team for 18 months. During that time he was a part of rapid on-site support (ROSS) team. Prior to joining Microsoft in 2005, he worked as SQL developer, SQL DBA and also got a chance to wear his other hat as an ERP Consultant.

Balmukund is a great friend and one of the finest SQL Server Expert I know. When I requested him for Guest Post, he has indeed come up with a fantastic blog post and very interesting title of the blog post. I am sure many wants to learn the trick to hide from SQL Server, so here it goes in his own words.

Important: It is Balmukund’s Birthday Today – please wish him all the best for exciting SQL filled New Year! Wish him on Twitter or leave a comment on his Blog.

I am sure that all of us have used SQL Server Management Studio countless times. It’s the common tool used by all DBA and developers. Most of the time we give server name, user name and password (if needed) and hit connect. Right? Have you ever clicked on “Options >>” button and made some changes in connection properties? Pinal wrote about color coding of servers, using “use custom color” option, here and posted a video here which is available in connection properties tab.

In this blog, we are going to talk about the third tab “Additional Connections Parameters”. When we want to test connecting string and those options are not available in earlier two tabs, then we can provide them in this tab (available in SQL 2008 onwards). The parameters should be separated by semicolons (;). There are a few parameters in connection string which can be spoofed. Let us start from hiding from SQL Server.

Attempt 1: Hide from SQL Server

Enter following parameters in the Additional Connection Parameter and click on Connect.
Application Name=Pinal-SSMS;Workstation ID=Pinal-PC

Once we connect and try to check our connection properties, the result of this would be as follows:

SELECT HOST_NAME, program_name
FROM sys.dm_exec_sessions
WHERE session_id = @@spid

If any DBA sees this, they would think that Pinal is working but it’s Balmukund in reality. And, I have hidden myself.

Attempt 2: Hide SQL Server from User (Deceive User)

Now we have hidden ourselves from SQL Server – let us do something crazy. Let us hide the SQL Server from the user. This can be extremely confusing so I suggest to use it carefully or never use it at all. If I can, I would have put red flashing lights around previous statement.

Now let us change the Data Source property in Additional Connection Parameters place. Let us confuse user that they are connecting to instance SQLServer2000 even though they are really connecting to instance BIG. My localhost is named as BIG and in following connection I am providing a incorrect server name Big\SQLServer2000 in the Server Name. In normal case it will give us the error. However, in this case we will provide an incorrect name in the Login >> ServerName connection.

We will right away click on “Additional Connection Parameters” and will enter following text in the string Parameters:  Data Source=localhost

Now let us hit connect and you will see following situation.

Well, as warned earlier, SSMS would override the value and hence we are connected to a server which is in additional connection parameters. Everywhere in SSMS, we would see instance SQLServer2000 which is in login tab, but actual server is the SQL Server 2012. Now imagine a drop database executed in SSMS, thinking that this was ainstance SQLServer2000 but they end up on instance BIG. It is quite possible some of the commands will now return error if they are deprecated in instance SQLServer2000. This will be fun prank – which you should not play with anyone.

Moral of the story: Be careful while using this tab. Use this only for those settings which are not available in management studio by default.

Hope you have learned something new today.

Thanks Balmukund Twitter for this wonderful guest post.

Reference: Pinal Dave (http://blog.sqlauthority.com)