Showing posts with label Database. Show all posts
Showing posts with label Database. Show all posts

Friday, December 21, 2018

SQL Server 2008 end of support

2018 is about to end. In the case you didn't know or haven't heard, the extended support for SQL Server 2008 (and R2) reaches to an end in 2019.

This beloved version (lets be honest, is like windows XP for the database world) was launched on 8/6/2008, so that means that is a 10 years old technology, with the same time as the backberry and much other devices that almost no one uses this days, which were great, but trought be told, compared with Today's gadgets they are way behind although it marked a milestone to what we have now you would preffer something better for you, isn't it?

So what does the end of support means?

Basically it means, that Microsoft wouldn't offer any type of support for that version of SQL Server, which can be translated to:
  • No new Service packs will be released
  • No new cumulative updates will be developed

And what does that means to me? Why should I care?

If I depend of any type of regulation or my environment holds any type of certification (PCI-DSS, HIPPA, etc.) it means that I would be flagged as "non-compliant" because it requires that any software that I use is still with support of the company that provide it.

It means, that I won't be getting updates anymore, if any new vulnerability is found for this product, it is most likely that I won't be getting a patch to remediate it. Also, all the repositories will eventually be removed, so even if the package I need was developed and for some reason I didn't applied, and then I look for it, it is most likely that I won't be able to find it, like is now the case for SQL 2005.

So what options do I have?

  1. Extended support: You can pay for extended support, this applies only if you have Software Assurance or Enterprise agreement and is only if you need to raise a ticket with Microsoft asking for support but not to resolve bug fixes and this extended support is Expensive, really really expensive.
  2. Azure: Microsoft can offers the extended support for up to 3 years, if you move your SQL Server as a VM in Azure, with that you can still get support without paying the extended support fee like if you stay on-premises.
  1. Upgrade: The best option is that you upgrade your environment, if you really need to stay on-premises, you can plan a migration in your own data center, however you can also plan to upgrade and take the new benefits of Azure in the different flavors it has to offer (Azure SQL, Azure Managed instance or VM with SQL Server)


Does it really worths the effort?
Absolutelly, the newer versions not only run faster but it also offers more features to secure your data, it also integrates monitoring tools and auto-tunning plus now you can run it in all different platforms not only with Windows.

So if you don't have your migration planned already, what are you waiting?

Wednesday, June 27, 2018

Tools to connect to a SQL database


Today I'm getting back to basics, whether you are new or veteran with the database engine you need a tool to get into, write queries, do some monitoring, tuning, etc.

If you have been using SQL for at least 5 years or any version older than SQL Server 2016 you will mostly only know one tool, as it was included with the installation media, however there are other tools available that you can use with which you can achieve the same goal, and at the same time offer some other benefits. In this post I will be writing about the top 3 and that are developed by Microsoft that you can use, in another posts I'll evaluate some others but for now lets use focus on this 3.

  1. SQL Server Data Tools (SSDT) - SQL Server Data Tools, was introduced as an add-on with Visual Studio 2012 meaning that it has been here for a while now, it offered a lot of features that you can only find with third party tools at an extra cost such as schema and data comparison between two databases, it also offered connectivity with a Team Foundation Services Server (TFS) among other features. In later versions you begin to be able to develop Reporting Services reports with SSDT, so it removed the need of installing the Software Development Kit within your database server or sharing some installation media with other teams for them to develop or update SSRS Reports. With the latest version you are able to connect to SQL Servers from 2005, Azure SQL, Azure SQL Data Warehouse and SQL Server 2017 on Linux. It doesn't requires any license to be installed. If you are more familiar with Visual Studio, it is easier for you to adapt to this one or even better, you can add this package to your installation and work from there without the need on having a separate tool to do the database part. You can see the official documentation and download it from here: https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017

  1.  SQL Operations Studio - SQL Operations Studio is a multi-platform tool you can use to connect to databases, if you are bored and tired of the limitation that SQL Server Management Studio offered or that there was no Data Tools for Linux, let me tell you that this is your tool, you can run Operations Studio on Windows, macOS and Linux. With this tool you can do almost all the administrative and development work you used to do with SSMS, there are still some items missing from SSMS, however most of the ones you used on a daily basis are here. It also includes a lot of reports you are used to see in SSMS with richer format. It doesn't requires any license to be purchased. It is a light weight file compared with the traditional management studio. You can download it for free here: https://docs.microsoft.com/en-us/sql/sql-operations-studio/download?view=sql-server-2017

  1. SQL Server Management Studio (SSMS) - As I said at the beginning of this post, if you have been using SQL Server for at least 5 years, or if you have installed any SQL Server version older than SQL 2016, you might noticed that with SQL Server 2016 there was a big change with the installation media, and is that it didn't include the option of "Management tools" instead it was in a different section in the welcome screen of the setup and when you click on it, it route you to download the media, this because of a lot of different reasons (Performance, Security and starting SQL 2017 multi-platform). This tool continues to be the most utilized tool to manage and interact with databases whether you are running your database on-premises or cloud. The good thing of this change is that if you are not installing a SQL engine in your desktop you don't need the entire media that included the setup files for the SQL engine, instead you just download the latest version and there you have it, also in the past you could only download the express version for free which was very limited or download the media containing the Development version for SQL, that is no longer the case, with the current release format you will have all the features supported. It has improved a lot from the versions we used to know, as now you can develop your own monitoring reports with SSRS and import those into SSMS and have that visibility from there, for example if you have a ad-hoc query to monitor your SQL Servers you can convert it as a report and integrate it with your SSMS and you will no longer need to open a new query window and run it, instead you just run your report and presto!


Hope that this quick review help you get a better idea and start your desire to test different tools.

Thanks for reading!

Wednesday, February 28, 2018

Offline installation of SQL Server 2017 on Linux

SQL Server is now compatible with different Linux distributions, and there is a lot of excitement about it.
How ever one of the main questions when ever you say SQL Server 2017 is now supported in Linux is, Have you test it? how do you install it?

By looking in the web you can find a lot of post and videos related to it, however most of them are based with Azure VM's (one great post I found is from a good friend that you can find here) or there is also the assumption that you will have an internet connection from your server, but what if not? What if you are a financial institution that is PCI-DSS compliant and the direct connection to the web is blocked or simply your network administrator doesn't want to allow you access to the Microsoft repositories from the network your server is running or you created your VM and the connection is not shared with your host? 

Well, let me tell you there is an option, you are not deemed to fight against the network engineers to enable you the access and here I'll show you the steps.

Pre-requesites:
  1. A machine (VM, physical, etc) with Linux 
    1. Red Hat Enterprise Linux (7.3 or 7.4)
    2. SUSE Linux Enterprise Server (v12 SP2)
    3. Ubuntu (16.04)
    4. Docker (1.8+)
  2. The latest version of SQL Server 2017 downloaded you can get it here and copied onto your Linux machine.
  3. A user to run the install (different than root)
Installation Steps
  1. Connect to your Linux machine. You can use Putty or the SSH client of your preference 
  2. Locate the folder where you have the installer, in my case I put it on "/home/SQLInstall/
  3. execute this command: Sudo yum localinstall <name of the installer> in my case the command looked like Sudo yum localinstall mssql-server-14.0.322.28-2.x86_64.rpm .After executing the command it will ask for the password of the user you are connected with to confirm the permissions and the execution command, then it will start the examination of the media and validate all the dependencies, that will look just like this:
 

   4. If you look at the bottom line, it stops at the Question if the size of the package and the size  matches, if the software is the one you would like to install etc, if everything is correct you will then need to Write the letter "y" to confirm.
   5. After confirming and hitting enter, the process will start to deploy the files in the system, however you will need to pay attention to the following message:

It says "complete!" but there is a remark "Please run 'sudo.... to complete the setup...' " so go and execute the command, otherwise SQL Server won't be installed as it only exported the files but didn't actually installed the engine and you will see why in the next step.

   6.  After executing that command, it will ask you to choose the version you would like to install

   So make sure to enter the right version, In my case I choose 2, as it won't support a production environment and is only for demo purposes.

   7.  After selecting the version, it will ask you to agree with the license terms and you will need to write "Yes" if you accept it or "No" if you don't.

   8.  Then it will ask you to configure the "sa" password.


   9.  After configuring the password the installation will succeed. However one of the problems you will face after getting it installed, is that you can't connect from outside of the server itself, so what you need to do is to enable the port through the firewall, and you can do that by running the following commands:
                   sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
                   sudo firewall-cmd --reload

The first command opens the port 1433 and the second command restarts the firewall service (sort of speak)

  10.  After doing all of the above steps and rebooting the server, you should be able to connect to your SQL Server instance, Congratulations!


Hope this steps lets you get up and running and in the case you run into any issue, let me know and I'll be glad to help and learn with you.

Thanks for reading!

P.D.: If by any chance you don't know how to get Red Hat installed, I'm preparing another post with those steps.

Wednesday, November 8, 2017

Contained Databases setup

 Last week I wrote about what is a contained database, the benefits and challenges, today as promised I'll show you how to setup one, how to configure a user and how to connect to it so let's get started.

Step 1
After logging in to your SQL Instance, go to the instance properties.

 Step 2
In the properties page, select advanced and in the first section choose "Enable Contained Databases = True" With this you will be enabling the feature at the instance level so that you can setup your databases this type.

Step 3

Go to the database that you want to convert to a contained database and click properties, then go to options and in the Containment Type choose Partial.

With the above steps you have now your first contained database, however at this point there is nothing different as if you try to connect to the database with the accounts or users that are already granted at the instance level you will see no difference, for you to see the difference you will need to configure one use or account at the database level as follow.

Step 4

Go to the security folder inside the contained database, go to the Users folder and choose "Create new User", it will prompt you the following window where you will create your user, remember to configure the privileges that you will allow it to have, and always remember the best practice is to follow the least privileged principle.

Ok so you have now your user created, let's try to connect

Step 5.1 connecting to SQL Server as usual

Wait, what happened? We created the user, now it is not able to connect?


Step 5.2 To take advantage of the containment, you will need to select directly the database that you want to connect, you can do this by clicking on the "Options Button" at the bottom right corner of the screen, the logon window will change, so then you will need to go to the Connection Properties tab and in the "Connect to Database" section, you will need to write the Database name, it shows the option to lookup, however if you are not logged in it will tell you that you need to login first and you will end up in a circle where you can't authenticate, so better write down the database name so you can enter it here.

Step 6 Welcome you are now connected directly to your database. Can you see the difference? With an account that is granted at the server level you can see all the databases in the SQL Instance, however when you are connected to your contained database, it shows you only the database that you have permissions, creating an isolation of the environment.

Tests:
Now lest do some tests, imaging that you are sharing the account with someone from the production control team and that person has a little bit of knowledge in SQL, and tries to discover it there are more databases in the instance, the query will only return 3, Master, tempdb and the contained database it has permissions, why this 3? Well, master has the metadata that it needs to work with, if you create temp objects you will need to rely on the tempdb, but that's it.

For the second test, I created another contained database and tried to run queries against, however it doesn't lets me do it, why? It was not mentioned in last week's post but other of the limitations is that you can't run queries across different databases, unless you have the guest account enabled, which is not this case and that strongly not recommend.

Summary: Contained databases is a feature that provides isolation between environments if you are running databases that requires this kind of separation, also by using this feature you don't have to worry about migrating users, roles, etc. when you move your database from one server to another, either because you are migrating, upgrading or recovering your server, also this portability eases the configuration and security administration when you have Always On Availability groups configured for you HA/DR environments.

Wednesday, November 1, 2017

Contained Databases

Today's post is in regards a feature that was released with SQL Server 2012, it hasn't gotten the attention or used that it deserves even when it helps solving a lot of problems you face when upgrading or migrating the database servers and is Contained databases.

What is a Contained database?
A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. A contained database basically includes all database settings and the metadata within itself thereby resulting in no configuration dependencies on the instance of the SQL Server Database Engine where the database is actually installed. Users will be able to connect to a contained database without authenticating a login at the Database Engine level. This feature really helps to isolate the database from the Database Engine thereby making it possible to easily move the database from one instance of SQL Server to another, this is incredibly useful when you have HA environments. The Contained databases feature is available at the instance level and is not enabled by default.

What type are available?
Contained database feature provides two containment modes:
None – By default each database has its mode set as NONE. This means there is no contained database feature being used.
Partial – With partially contained databases, we can define boundaries between databases and the server, so the metadata will exist inside the databases. It makes SQL Server databases more portable and less dependent on underlying hosts.

Advantages of contained databases :-
1. User authentication can be done at database level, so you only need to be sure to grant the users permissions in your database.
2. Have less dependency on instance than conventional databases. Objects & features of each database can be managed by them self, reduce workload of system database & SQL instance
3. Easier & Faster to migrate databases from one server to another. Errors related to missing users and orphan users are no longer an issue.
4. Contained database users can be Windows and SQL Server authentication users.
5. Contained database user can access only contained database objects. They cannot access system databases and cannot access server objects.
6. This is the preferred mode to be used with HADR (Always On)
7. Maintaining database settings in the database, instead of in the master database increase security & flexibility. Each database owner have more control over their database, without giving the database owner sysadmin permissions.
8. To close collation issues in contained database. New feature catalog collation introduced with contained database. Now database collation works for user objects & catalog collation works for system objects in database. Catalog collation will be same for all contained databases on all SQL instance, also this collation cannot be changed.

Disadvantages and Limitations of contained databases :-
1. A database owner has more control on contained database, User can create contained database users without the permission of a DBA that can lead to security issues & data theft threat
2. Contained databases cannot use replication, change data capture, change tracking, numbered procedures, schema-bound objects that depend on built-in functions with collation changes
3. Before changing containment settings at database level from NONE to PARTIAL , contained databases feature needs to be enabled at instance level, so make sure to enable it before releasing your server and database to production.
4. To connect to a contained database, you need to specify the database name in the default database option tab.
5. Temporary stored procedures are currently permitted. But can be removed from future versions of contained database.
6. Contained database user can access other databases on the Database Engine, if the other databases have enabled the guest account, which can be a security issue.

Changes cross versions:
This feature hasn't gotten any change since it got released with SQL 2012, mostly as the new versions have been focused in improving the performance, security and integrating Artificial Intelligence and Machine learning features, however, personally if you have environments where you need High Availability and every time that you failover your groups you need to resynch your users and principals, this might work for you, however, you will need to remove you database from your availability group if you want to enable this in your database, but we will be working with it in next week's post.

Thanks for reading!

Resources:

SQL Server 2008 end of support