First of all you need to read my previous article called “How to send SSIS logs (errors) through email” as the processes are pretty the same. However, you need to create some changes in the Execute SQL Task (ref.: section “J” number 5 and 12 of “How to send SSIS logs (errors) through email”). As an Execute SQL Task is used to collect the logs stored in SQL Server you need to be familiar with parameter mapping in Execute SQL task and know how it works. Assume that we need to just send the “Error” logs that are happened between “Event Handler Start Time” and the current time for the current package execution. As you can imagine it is slightly different from what we did in the previous article to email the SSIS logs to the system administrators. As there was just one system variable that we mapped in parameter mapping section in the Execute SQL Task. But, here we need to have one more system variable mapped to a parameter. Please note that we are using OLEDB connection to connect to the SSIS log database that we created before to store SSIS logs. So there are some important points with OLEDB Connection and Execute SQL Task parameter mapping and its SQL statement.
First of all I want to explain the process of sending SSIS Logs through email for better understanding. As you might know you can save SSIS logs in several ways for administration purposes. In this article our focus is on how to send SSIS logs to the administrator whenever an event like a package failure is occurred. Just assume that we have several SSIS packages and we are storing the logs in a SQL Server database. Almost all of the packages are running over night. Now, what if a package or some packages failed? Generally one of the most essential activities is that a notification email should be sent to the system administrators to let them know that something’s wrong with the package execution. It could be more helpful to send them the relevant information about the failure. Reading the following lines you can handle this important part of the process in your organisation.
There are some cases that you do not have any documentations for the database that you are working on and you need to investigate the entity relationships and so on. We can understand the database entity relationships better if we can find the number of references to the table or the number of tables’ dependencies. We can do the job easily by combining sys.tables, sys.foreign_keys and/or sys.sysreferences. The following simple T-SQL codes will retrieve tables’ references and tables’ dependencies:
Sometimes you might feel you need to store the database diagrams in a file basis. I’ve faced to this feeling specially when I was testing some databases which were under development. I didn’t like to create the whole database diagrams that I’ve created before in a database that a new version of it is newly released for testing as it is a kind of rework and it is a time consuming process. So I decided to store the database diagrams in a file system basis that I can reuse it again and again. The following solution could be great for sharing the database diagrams in the development team as well by sending them the diagram file by email and/or storing the database diagram file in a network shared folder. Here is a work around that might help you. First of all you should install diagram support in your database. To do so just right click on the database diagrams in object explorer in SQL Server Management Studio and select “Install Diagram Support”.
SENSITIVE DATA! It’s an interesting topic! In this post I’m trying to explain how to hash data to increase security during ETL. Assume that we have sensitive data stored in several secured source systems. The source systems are located in different countries and different regions. As the source systems themselves are secured, how we can cover data security needs during ETL process to read data from source systems and load into staging area? Apart from using secured network infrastructure, VPN, network tunnelling etc. we need to cover data layer security to extract sensitive data. One of the best ways is hashing data when it is extracting from source databases. Hashbytes is a T-SQL function that is available in SQL Server 2005 and later. As you might know there are many hashing algorithms, but, different SQL Server versions are supporting different range of hashing algorithms. For instance SHA1 is supported by SQL Server 2005 and later, but, if you are looking more secure hashing systems like SHA2, 256 (32 bytes) or 512 (64 bytes), you should use SQL Server 2012. Actually the hashbytes function will return null in earlier versions of SQL Server. If you are looking for a higher level of security like SHA3 that is originally known as “Keccak” you should wait for it for a long time as based on my investigations it is not supported even in SQL Server 2014 OR you can write your own SHA3 code OR just rely on some third party codes available on the Internet! So let’s get our hands dirty with using hashbytes in different versions of SQL Server.
First of all, I’d like to point to the fact that there is almost always a bottleneck with OLTP database’s performance that is related to disk speed. So in-memory processing can improve the current poor performance with disk-based tables significantly as well as achieving scalability.
In a very simple and quick explanation, Hekaton is new feature coming with SQL Server 2014 to handle in-memory processing for OLTPs. Actually it is something live xVelocity for OLAP. But, how Hekaton can help for improving OLTP performance and scalability? The answer is that SQL Server 2014 is trying to achieve these goals by using
- Optimised algorithms to access memory-resident data
- Eliminating logical locks using a concurrent control method for RDBMS (Relational Database Management Systems) that assumes that multiple transactions can complete without affecting each other, so that, the transactions can commit without locking the source data. This is called Optimistic Concurrency Control (OCC).
- Using lock-free objects to access all data, so threads that perform transactional work do not use locks for concurrency control.
- Using a new concept in SQL Server 2014 called Native Compilation. Native compilation refers converting programming constructs to native code, which consists of processor instructions that can be executed by the CPU without the need for further compilation or interpretation. So, SQL Server can natively compile stored procedures that access memory-optimized tables. Native compilation allows faster data access and more efficient query execution, than traditional, interpreted Transact-SQL.
Microsoft claims that “Use of main memory can result in performance gains from a few percentage points of performance improvement, to 20 times performance improvement.”
After downloading the SQL Server 2014, mount the media file downloaded and run “setup.exe”
In SQL Server Installation Centre click on “Installation”
Click on “New SQL Server stand-alone installation or add features to an existing installation”
Note that if you have any of previous versions of SQL Server installed on your machine the setup process will fail because this release of SQL Server cannot be installed in until the existing instances of SQL Servers are uninstalled.
If you click on “Failed” you can read the complete error message that is “Previous SQL product installation failed. A SQL product other than SQL Server 2014 CTP1 is detected. You cannot install this release until the existing instances of SQL products are uninstalled.”
What does it actually mean that the database doesn’t have a database owner?! It seems odd, but, it is possible. In some cases that the database is created by an application it really happens that the database doesn’t have any database owner. For instance, the databases generated by CRM Deployment Manager doesn’t have database owner. So what if we had a bunch of databases in the SQL Server that doesn’t have any database owners? Okay, let me explain. I’ve faced to a situation that we needed to create some database diagrams for many databases and all of those databases where CRM databases created by CRM Deployment Manager. Hence, when we were trying to generate a database diagram an error message were raising saying the database doesn’t have DB owner. So it was very time consuming if we wanted to define a DB owner for each database individually using the GUI. The following code will define a particular database owner for the databases server wide. (In our situation it was a service user.)
SQL server 2014 Community Technology Preview 1 (CTP1) is ready to download here: http://technet.microsoft.com/en-us/evalcenter/dn205290.aspx
Project code-named “Hekaton”
One of the most exciting features in this version is a project code-named “Hekaton” that provides in-memory OLTP capabilities. As Hekaton is built into core SQL Server database it will improve the performance of the database applications significantly. It is more interesting that Hekaton is installed with SQL Server 2014 engine that means no additional action is needed to use the benefits of in-memory processing without rewriting the database application. It is really awesome that it can increase performance of existing database application without having to upgrade the hardware. Microsoft claims that Hekaton is easy to deploy and allows to access the other features in SQL Server, while taking the advantage of in-memory performance.
In my previous post called “How to drop tables database wide using T-SQL” I’ve explained that how we could delete all tables database wide in SQL Server. But, what if there are some other schemas other than “dbo” and some tables that are created in those schemas? If we just delete “[dbo].” from the code we will obviously face to the following error message: “Cannot find the object “TABLE_NAME” because it does not exist or you do not have permissions.” for those tables that are not under “dbo” schema. So, we need to retrieve all schemas and their related tables. This approach is really helpful when we are implementing an ETL process that is designed to delete the tables from particular schemas and recreate the tables and populate new data. This is a common way for staging to delete and recreate tables instead of updating existing tables as update is really costly.
So, let’s write some codes.