It’s just a read only user right???

Should we be worried about a read only user? At first look, it looks like, “no we shouldn’t be worried” by a user who has only read access to a database. That is only partially true and on several occasions, I had to explain the people requesting such as access what potential danger, such a read-only user can cause. So I thought it might be a good blog post to publish.

Whenever you come across a read-only user, we assume we are good from the security perspective that it’s ok for this particular user to have a read permissions on the database and there is no harm as that user cannot make any changes to the underlying data. I agree to that statement, but from a point of view other than data security, we will have to recognize that there can be an impact on the performance of SQL Server, on which this database exists.

Consider the case if this read-only user credentials are used in an SSIS package to read a large chunk of data and pushed across to another data destination. When a large number of rows are read from the SQL Server database, that data is first brought into the buffer (memory) of the SQL Server, there by causing any data inside the buffer (that is being actively used) to be flushed off the memory. This might cause a huge impact on the other applications (that read/write to the dbs on this SQL Server). So always keep in mind about this whenever you are creating a read only user. Educate the user who is requesting the read only access about these potential performance impact and let them try to limit their result sets to as minimal as possible..

–Bru Medishetty

Do you like this site? Like our FB page\LearnSQLWithBru so that, you know when there is a new blog post.

Download SQL Server 2012 Service Pack 1

Last week Microsoft released Service Pack 1 for Microsoft SQL Server 2012. If you have been waiting for the Service Pack 1 to be released, to apply on your existing SQL Server 2012 installations, then go ahead and download the Service Pack 1 from Microsoft Download Center link

As with all Service Packs for SQL Server, this Service pack can also be applied to all the editions of SQL Server 2012.. If you would like to know more about what are the new features that this SP1 brings to SQL Server 2012, read them here in the following link Look for the section New or Enhanced features in SQL Server 2012 SP1..

Do you like this site? Like our FB page @\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

How to refresh Test, Dev Systems with Prod Data?

When I attended PASS Summit last October, there were many people whom I met during breakfast and lunch and sometimes in the lobby and we discussed a variety of topics on  SQL Server. During one of those conversation, I was asked to give my suggestion on how to refresh their Development and Test environments with the Production data. So I thought it would be nice to write the same, so that it might help some of those who are looking for the same info. 

Backup & Restore

One of the most common mechanism of refreshing environments is using database backups on the production server and restore on the DEV, QA server to refresh the existing one. The advantage of using backup / restore is it is easy to do it. You don’t have to perform any special steps in this procedure other than restoring, coz your most recent full backup of production database should be good. However, the downside of this is that your DEV / QA database would be the same size of the production and your DEV / QA machine might not be anywhere near the size of your PROD box. The disk space too might be a factor to consider when using this procedure.

SQL Server Integration Services (SSIS)

If you are aware and comfortable using SSIS packages, you can utilize this platform to refresh and  make other customization after your DEV / QA database is refreshed. With SSIS, you can automate the database refresh and setup a SQL Agent job and when you have to refresh you can run the job manually (or on a pre-determined schedule).. Some of the benefits using SSIS is the manual tasks that might be needed after you refresh the data, such as scrubbing off critical data (such as SSNs or Credit Card data etc), adding additional Developer / Test users etc.. You might also not want all of the data from your production databases, only the most recent year’s worth of data is good to test, in such cases backup restore will not solve the purpose, the data that is not necessary has to be dropped after restoring the database backup…

Import Export Wizard

Import Export Wizard would be another handy tool that can be used for data refresh, using this tool does not need any other program (such BIDS or SSDT as in the case of SSIS). You can pick only those table(s) that needed in the DEV / QA environment.. The disadvantage of this tool is that you cannot customize beyond a certain level and scheduling is not an option unless you save the SSIS package at the end of this wizard.. Also, you cannot push data simultaneously to 2 different databases such as DEV and QA at the same time (where as in SSIS you can do so using Multi-Cast Data Transformation).. 

Other options

Finally there are other options such as Scripting the database objects and transferring the data using stored procedures at either the source or destination (which involves a linked server between the source and destination servers) and / or command line utility called Bcp. 

Overall, I would prefer SSIS as my choice to refresh QA / Dev databases..

Do you like this site? Like our FB page @\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty