Count tables’ references and tables’ foreign keys

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:

Continue reading “Count tables’ references and tables’ foreign keys”

How to store a SQL Server database diagram into a file and share it with others?

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”.

Continue reading “How to store a SQL Server database diagram into a file and share it with others?”

How to hash sensitive data for maximising security in SQL Server 2005 and later versions?

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.

Continue reading “How to hash sensitive data for maximising security in SQL Server 2005 and later versions?”

Installing SQL Server 2014 CTP1

  • After downloading the SQL Server 2014, mount the media file downloaded and run “setup.exe”
  • In SQL Server Installation Centre click on “Installation”

image

  • Click on “New SQL Server stand-alone installation or add features to an existing installation”

image

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.

image

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.”

image

Continue reading “Installing SQL Server 2014 CTP1”