SQL SERVER – How to easily work with Database Diagrams

Databases are very widely used in the modern world. Regardless of the complexity of a database, each one requires in depth designing. To practice along please Download dbForge Studio now

The right methodology of designing a database is based on the foundations of data normalization, according to which we should first define database’s key elements – entities. Afterwards the attributes of entities and relations between them are determined.

There is a strong opinion that the process of database designing should start with a pencil and a blank sheet of paper. This might look old-fashioned nowadays, because SQL Server provides a much wider functionality for designing databases – Database Diagrams.

When using SSMS for working with Database Diagrams I realized two things – on the one hand, visualization of a scheme allows designing a database more efficiently; on the other – when it came to creating a big scheme, some difficulties occurred when designing with SSMS.

The alternatives haven’t taken long to wait and dbForge Studio for SQL Server is one of them. Its functions offer more advantages for working with Database Diagrams.

For example, unlike SSMS, dbForge Studio supports an opportunity to drag-and-drop several tables at once from the Database Explorer. This is my opinion but personally I find this option very useful.

Another great thing is that a diagram can be saved as both a graphic file and a special XML file, which in case of identical environment can be easily opened on the other server for continuing the work.

During working with dbForge Studio it turned out that it offers a wide set of elements to operate with on the diagram.

Noteworthy among such elements are containers which allow aggregating diagram objects into thematic groups.

Moreover, you can even place an image directly on the diagram if the scheme design is based on a standard template.

Each of the development environments has a different approach to storing a diagram (for example, SSMS stores them on a server-side, whereas dbForge Studio – in a local file).

I haven’t found yet an ability to convert existing diagrams from SSMS to dbForge Studio. However I hope Devart developers will implement this feature in one of the following releases.

All in all, editing Database Diagrams through dbForge Studio was a nice experience and allowed speeding-up the common database designing tasks.

Download dbForge Studio now.

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

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)