Configure File & Folder Permissions for SQL Server

Since SQL Server 2012 permissions are assigned to the per-service SID for each of its instance. This blog will show how to configure file & folder permission for the SQL Server Database Engine. SQL Server must have permission of the Windows file system to access the file folder where database files are stored. The default SQL server data and backup folder permission are configured during setup. But if your database files saved in a different folder, you might need to follow below steps to setup SQL Server permission to that folder

Configure folder permission for SQL Server instance

  1. Using Windows Explorer, navigate to the file system location where the database files are stored. Right-click the file system folder, and then click Properties.
  2. On the Security tab, click Edit, and then Add.
  3. In the Select Users, Computer, Service Account, or Groups dialog box, click Locations, at the top of the location list, select your computer name, and then click OK.
  4. In the Enter the object names to select box, type the name of instance. If you database use default name then use NT SERVICE\MSSQLSERVER, or for named instance please use NT SERVICE\MSSQL$InstanceName
  5. Click Check Names to validate the entry
  6. In the Group or user names box, select the name added at step 4, and then in the Permissions for box, select the Allow check box for Full control.
  7. Click Apply, and then click OK twice to exit.

SQL SERVER BCP UTF8 import and export, support UTF8 after SQL SERVER 2014SP2

The bulk copy program utility (bcp) is a very powerful bulk copy tool between database and flat file. Normally used for large number of data import and export. But if you sql server version is older then 2014 SP2, unlucklly you cannot process UTF8 data even if there is a code page parameter -C , but it doesn’t support UTF8. Refer to Microsoft community document

code_pageSpecific code page number; for example, 850.

** Important ** SQL Server does not support code page 65001 (UTF-8 encoding).

If you want to process UTF8, then must need to upgrade SQL Server to version 2014 SP2 or later. Once you installed the upgrade, then you can use BCP to import or export UTF8 as below:

To import UTF-8 data to SQL Server, use the BCP utility and run the following bcp t_users in “D:\test.csv” -c -C 65001

To export UTF-8 data to SQL Server, use the BCP utility and run the following bcp t_users out ” D:\test.csv ” -c -C 65001

SSIS: Reduce SSISDB database size

The SSISDB database is automatically created when we create SSIS catalog. All the SSIS projects, packages and execution history are saved in this database. So if you have a package that run every few minutes you could see the database size is growing exponentially. I deployed one project at AWS EC2 server with limited disk storage size (100GB SSD), the package was automatically executed every 3 minutes by SQL Agent Job. After one weeks later, I noticed the disk size almost full. After investigation, I notice the size of SSISDB is very big already. How can I control SSISDB to a minimum size?

SSIS created a maintenance job already when we create the catalog, it’s SIS Server Operation Records Maintenance.

Looking the job steps, you will notice it calls a stored procedure internal.cleanup_server_retention_window, in this stored procedure you will see it is using catalog.catalog_properties to do database maintain.

Now is solution is easy, I just need to update RETENTION_WINDOW property of catalog_properties .

exec catalog.configure_catalog RETENTION_WINDOW , 1

After this change, then just waiting for SSIS Maintenance Job to do database cleaning task.

If you don’t want to do this via T-SQL. You can right click on your catalog then click Property to change settings from Catalog Properties window:

After you reduced Database size, you maybe will notice the log file size is going up now. For the log file, if it’s not very critical system, you can used DBCC SHRINKFILE to shrink files directly:

USE SSISDB;
GO
–use below script to find ssisdb log file id
–SELECT file_id, name FROM sys.database_files;
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE SSISDB
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1);
GO
— Reset the database recovery model.
ALTER DATABASE SSISDB
SET RECOVERY FULL;
GO