Tag Archives: SQL

K2 Five collation requirement changes

In the past I’ve wrote some blog post promoting documented K2 collation requirement. With release K2 Five this requirement changed and I guess I have to mention it on my blog as essentially it makes my old blog posts about required K2 database collation incorrect.

So recently (with release of K2 Five) all K2 documentation was updated and states that our requited collation now is “SQL_Latin1_General_CP1_CI_AS“. Where to find this information?

In K2 Installation and Configuration Guide you can find “SQL and Reporting Services Operational Requirements” section which says that:

– Case-sensitive databases are NOT supported.

– The following collation setting is required for the K2 database: SQL_Latin1_General_CP1_CI_AS

And as usually you can find the same information in K2 Product Compatibility, Integration and Support matrix:

What is good about this change is that SQL_Latin1_General_CP1_CI_AS will be default collation if you installing SQL Server on top of Windows Server which has been installed with US location/language settings – so at least some people will meet this requirement by accident.

What is bad is that collation requirement was just silently changed in documentation with release of K2 Five without any explanations. According to my current knowledge collation which was mentioned in documentation before was a requirement only for pre-4.6.11 versions of K2. So in case you are doing new installation of K2 4.6.11 or newer make sure that your SQL Server instance provisioned with SQL_Latin1_General_CP1_CI_AS collation.

Somewhat mixed blessing is change which was made to K2 Five installer to enforce this collation: what it does at the moment is just enforces this collation on K2 DB level while ignoring SQL Server instance level collation. So in case you not provisioned SQL Server instance with the right collation you will get an errors post installation and will be forced to change SQL Server instance level collation to fix this. That’s something that I hope will be corrected in K2 installer in the future so that it warns you about wrong instance level collation issues before you start your installation.

Conclusion: read vendor documentation carefully before doing your installation even if you did it many times before 🙂

Update (Feb 2018): Current guidelines are:

– For upgrade installations be sure to make SQL server instance and K2 DB collation are the same (in case you moving K2 DB onto new server)

– For clean installs I would suggest stick to Latin1_General_CP1_CI_AS (especially if you do 4.7 install). I will probably write more detailed post to explain this position later

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Configuring SQL instance firewall rules via PowerShell

Doing distributed environments setups rather frequently these days I realized that I really don’t want wasting my time setting up SQL Server firewall rules via GUI (I described the process here) and luckily enough Ryan Mangan already created such script. All I had to do is try it (confirm that it works), save it on GitHub and share on my blog for the benefit of wider community:

Original blog post by Ryan/source of this script: PowerShell Script for SQL Firewall rules

Facebooktwittergoogle_plusredditpinterestlinkedinmail

How to: Drop multiple databases via SQL Script (no worries backup/restore is covered too :) )

Recently I did rather a lot of test requiring me to work with non-consolidated K2 DBs. Test included multiple DB restore/delete operations and I realized that I need some script to quickly drop all my K2 DBs and start from scratch. Here is this script:

Script selects every database prefixes with “K2” and you just need to copy its output into new query window and execute.

And in case you tend to backup things before you delete them, similar script for backup:

And for restore you can use the script below. Unfortunately it uses hard coded file paths but assuming your back up files have default DB names (and for example were created by the script above) you can get away with minimum find and replace adjustments (path to backup files and your SQL instance data directories may need to be adjusted). Here is the script for restore:

Facebooktwittergoogle_plusredditpinterestlinkedinmail

SQL Server: How to attach all databases from specific folder

I recently had to change SQL Server instance collation using the steps I described in my earlier post. After this operation dozen of databases which were hosted on that instance become detached and I was able to see only set of instance’s system DBs. So I had to look for an option to attach all DB from folder via script as doing this manually for 20 or so databases a bit too much of an effort to me 🙂

There is “Attach all the databases in one folder” script available in TechNet Script Center which was able to solve this problem for me. All you need it pass DatabaseDir parameter to the function specifying folder with your DBs:

Just in case I provide PS code of this function below in case you don’t want to grab the same from TechNet Script Center. All credit goes to Ivan Josipovic.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Changing SQL collation for deployed instance without reinstall (almost)

IMPORTANT UPDATE: If you use K2 4.6.11 or newer you have to have SQL_Latin1_General_CP1_CI_AS collation on a SQL Server instance which hosts K2 DB (more details)

Collation matters. Period. If you not sure just wait till you get an incident or issue which will teach you that it is. Some apps require you to have specific collation for their back end DB. For example for K2 you need to have  Latin1_General_CI_AS collation (see my earlier post on that _ ).

Nonetheless it can often be the case that your RTFM/Prerequisites_Check moment (I wonder if I can copyright/trademark this term? I heard that Michael Buffer trademarked his “let’s get ready to rumble” phrase _ ) happens after SQL server instance has been already provisioned. For newly created (clean install) K2 database you just need to delete database which was created on instance with wrong collation and allow K2 Setup Manager to create it again after you deal with your instance collation. There is an MSDN article detailing that _ but I will outline the procedure below.

Aforementioned MSDN article says that you have to have installation media to perform this operation, but in fact you can get away with Setup Bootstrap folder which should present on your server in location similar to one below (may vary from version to version, see details _ )

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014

So steps are the following:

1. Locate setup bootstrap folder for your SQL server installation, open CMD or PS window (don’t forget “dot net sourcing” thing for the latter):

2. Execute the following command:

Once this operation completes  master, model, msdb, and tempdb system databases are rebuilt (dropped and re-created) with collation you specified in the command. Obviously all your modifications to these databases are lost (if you made any) and essentially it is sort of reinstall/repair type of operation just much quicker and without need to wade through SQL Setup GUI.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

SQL Server: Giving user role membership on specific DB

Just a really quick note on how to manage RBA in SQL Server not relying on SQL Server Management GUI, as sometimes it is far quicker to execute a few lines, for example to create DB and then grant dbowner role membership, for let’s say you application service account so that your app can use newly created DB. So SQL code is the following:

NOTE: In real world/production environments you should not be so generous with granting dbowner role to each and every user, event to app service account. See some expanations for example here: 5 Reasons Against Allowing db_owner Role Permissions.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

SQL Script: List all tables in DB with their row counts

Often I need to test something and use SQL database table of particular size for my test, and sometimes it is easier to pick up some table of appropriate size (at least in terms of rows number) from what you already have. This is the case when this SQL script may come in handy:

Facebooktwittergoogle_plusredditpinterestlinkedinmail

How to: quickly check SQL server collation settings

Just a quick note on how to quickly check Microsoft SQL server collation without wading through SQL Server Management Studio GUI. Go to CMD and execute the following:

\n\n\n[code language=”sql” light=”true”]\nsqlcmd -q "SELECT CONVERT (varchar, SERVERPROPERTY(‘collation’));"\n[/code]\n\n

Alternatively you may use sp_helpsort stored procedure, though when run via sqlcmd it gives a bit strange output if run with sqlcmd

\n\n\n[code language=”sql” light=”true”]\nsqlcmd -q "EXECUTE sp_helpsort;"\n[/code]\n\n

On the boxes with SQL server installed path to sqlcmd executable added to PATH variable so you can fire it off without specifying full path. I guess you may also check corresponding key in registry which should exist there I believe (at least for server collation) 🙂

Also you may view collation settings on databases:

\n\n\n[code language=”sql” light=”true”]\nsqlcmd -q "SELECT name, collation_name FROM sys.databases;"\n[/code]\n\n

Reference: MSDN: View Collation Information

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Free resources for SQL Server MCSA and MCSE Exam preparation

Just sharing number of links to some MVA resources which can be helpful for Microsoft SQL certification preparation and learning Microsoft SQL Server in general.

nosql

Don’t ask me why is the picture above has any relevance to this blog post 🙂

Anyway, the following courses cover SQL Server 2012, along with performance tuning and high availability features of SQL Server 2014:

Developing Microsoft SQL Server Databases (prep for Exam 464)Designing Database Solutions for SQL Server (prep for Exam 465)Implementing Data Models & Reports with Microsoft SQL Server (prep for Exam 466)Designing BI Solutions with Microsoft SQL Server (prep for Exam 467)

And next, these courses complement the existing online exam prep resources for MCSA: SQL Server 2012 exams:

Querying Microsoft SQL Server 2012 (prep for Exam 461)Administering Microsoft SQL Server 2012 Databases (prep for Exam 462)Implementing a Data Warehouse with Microsoft SQL Server 2012 (prep for Exam 463)

Source – Born to Learn Blog

Facebooktwittergoogle_plusredditpinterestlinkedinmail

How to get listing of stored procedures for SQL server database

Just a quick note on how to get listing of stored procedures for SQL server database. Following query will do this for you (as long as you’re not in the master database, system stored procedures won’t be returned):

\n\n

SELECT *\nFROM DatabaseName.information_schema.routinesWHERE routine_type = ‘PROCEDURE’

You may add AND specific_name LIKE ‘%part_of_specific_name%’ to WHERE clause to filter/look up for specific stored procedure by name.

If for some reason you had non-system stored procedures in the master database, you could use the query (this will filter out MOST system stored procedures):

\n\n

SELECT *\nFROM master.information_schema.routinesWHERE routine_type = ‘PROCEDURE’\nAND Left(Routine_Name, 3) NOT IN (‘sp_’, ‘xp_’, ‘ms_’)

\nSource – question on stackowerflow.com: Query that returns list of all Stored Procedures in an MS SQL database.

Facebooktwittergoogle_plusredditpinterestlinkedinmail