A “one-to-many” relationship is one of the most common relationship types as many real world scenarios can be represented using it. For instance, the same product can be sold by more than one supplier; a customer can have more than address, and so on.May 21, 2013
One of the common questions our customers have about auditing is – how to automate transaction log reading and have all the information you might need at hand. Another common request is – how to save the transactions I see in the ApexSQL Log grid, so I can easily filter, group, order and query them without running the whole ApexSQL Log reading process again? In case of large transaction logs, the reading process may take a long time, and I want to avoid running it each time I want to see the transactionsMay 20, 2013
ApexSQL Diff and ApexSQL Data Diff provide a user friendly GUI for SQL comparison and synchronization of database schemas and database objects. They both include the command line interface which provides the exact same options, and which you can use to schedule unattended comparisons and synchronizations
But, what can be done when the features that the GUI and CLI provide are simply not enough? In such cases a more flexible solution is needed and the good news is – there’s a programmable APIApril 30, 2013
Application deployment is not an easy task. For more complex updates, besides a new version of a database that should be deployed, a new version of the application should also be deployed, and even the environment configuration changes (e.g. IIS settings for a web application or some other server settings) and post-deployment testing might be necessary.
Problems with complex deployments
A complex deployment often involves a lot of manual work, which makes it slow and error prone. While these characteristics are not a big problem in a test or development environment, they are certainly unacceptable in production.April 30, 2013
When synchronizing large databases that contain millions of records, you will come across several challenges The first challenge is to just compare such databases. When the database tables contain millions of records, their comparison through the ApexSQL Data Diff graphical interface will be very slow and in such cases, it is strongly recommend using the ApexSQL Data Diff command line interface. As there’s no need to show millions of records visually in the tool’s grid, such large databases are processed faster.April 29, 2013
The saying “An ounce of prevention is worth a pound of cure” is ever so true when it comes to SQL Server security. Even if everything seems fine with your SQL Server environment from a security standpoint (i.e. no unexpected slowdowns or increased network traffic; none of the data or the objects are damaged corrupted or missing), as we’ve outlined in several articles before, having an auditing system up and running can be literally a life savior when it comes to any suspicious activities, such as unauthorized permission changes or compromised SQL logins. So, how can one set up SQL Server auditing?April 26, 2013
One of the most important tasks for a DBA aiming to keep database and the data in it secure and away from unauthorized access or, heaven forbid, malicious changes is to always stay on top of the effective SQL Server permissions his users have over the SQL instances as well as the databases, database objects and data stored in them. Although this might seem like a pretty straightforward task, as the number of database users grows on one hand, and the number of databases and objects on the other things can get really complicated. Add to that the ever changing business requirements, and soon, unless you have some kind of documenting system in place, you can end up with users not having sufficient permissions or even worse – users having more permissions that they actually requireApril 23, 2013
Due to the sheer volume of data usually involved in an Extract – Transform – Load (ETL) process, performance is positioned very high on the list of requirements which need to be met in order for the process to go as smoothly as possible. Here are some guidelines which will help you speed up your high volume ETL processesApril 23, 2013
The applications used by travelling sales representatives, or other field workers – delivery drivers, visiting nurses, etc., are designed to collect data from remote locations and then send it to a data center. Also, the data from the data center, occasionally need to be sent back to these remote locations, to keep them up to date.
For example, whenever nurses pay a visit to a patient, they enter the information about the visit into the database on mobile devices. At the end of the day, all these entries created during the day are sent to the central database in a hospital. After that, the nurses can synchronize mobile devices with the database in the hospital data center, so they get the new information about their patients, and also, the information about any new visits they need to make the next day.
In the scenario such as this, there’s constantly a need to synchronize the information from a mobile device to a central database.April 19, 2013
Taking over a database you’re not familiar with requires you to dedicate some time to analyze and understand it. Deciphering an inherited database takes some time, that among other factors, depends on the available documentation (if any), comments left in object SQL, and database complexity.April 18, 2013
Even though Dynamics CRM 2016 is very on clear on the fact that that record deletion is permanent and even provides a warning, it’s not unusual one or more CRM records to be deleted by accident. The bad news is that once the records have been deleted, there is no way to undo the deletion as removing a record from CRM actually translates to deleting it from the underlying SQL database.
That is one of the biggest differences between Dynamics CRM 4 and Dynamics CRM 2016. Unlike Dynamics CRM 2016, Dynamics CRM 4 uses the “soft delete” approach. This means that a deleted record in Dynamics CRM 4 could be recovered relatively effortlessly – to recover a deleted record from Dynamics CRM 4 all you have to do is to connect to the underlying database, locate the deleted record and simply update the value in that record’s IsDeleted column to false. So, how can one go about recovering deleted records from a Dynamics CRM 2016 database?April 12, 2013
Being able to revert to the latest available backup immediately is a key requirement that every SQL Server DBA needs to meet. There are so many scenarios in which immediate action needs to be taken – starting from actual disk failure or corruption, system failure all the way to UPDATEs without WHERE clauses, malicious INSERTs or accidental DELETEs, DROPs or TRUNCATE operations. However, usually this is easier said than done
Even if you take meticulous care of your database backups, have the last available database backup verified and at hand and are sure that restoring it won’t overwrite any valid changes made to the database since the accident has occurred – the fact that the system will experience downtime until the backup is fully restored still remains. Luckily, downtime caused by the time needed for a full database backup restore can be minimizedApril 12, 2013
The time when source control systems were used only for code development is gone. Today, database versioning is quite common, even in small companies.
As there are many different source control systems, with different features and options, there are no strictly defined rules how to use them. Each company can create its own standards and rules for using a source control system for database versioning, following the best practices and recommendations.April 11, 2013
One of the ways to keep stored procedure, function or view schema hidden is using the WITH ENCRYPTION statements. In this way, access to objects’ DDL script can be locked, so that unauthorized users cannot see them:
CREATE FUNCTION [dbo].[ufnGetAccountingEndDateEnc]() RETURNS [datetime] with encryption AS BEGIN RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112)); END; GO
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