How to configure application database with microsoft sqlserver?


#1

hi,

I set environment variables like :

export MB_DB_TYPE=sqlserver
export MB_DB_DBNAME=metabase
export MB_DB_PORT=1433
export MB_DB_USER=sa
export MB_DB_PASS=password
export MB_DB_HOST=localhost

and when starting my jar I’ve got the following error
java.lang.IllegalArgumentException: No matching clause: :sqlserver

Is sqlServer supported as an application database for metabase ?

thanks


#2

We don’t currently support sql server as an application database.


#3

if it’s specifically important to you, open an issue at www.github.com/metabase/metabase/issues/new and if there’s enough community support for it, we’ll work on it.


#4

Many thanks for your reply and the link to the ticket.


#5

Hi there,

I’m looking for the same :slight_smile: Wondering if this has an existing ticket already on github!

Thanks


#6

I do a lot of work with MS SQL - probably 75% of my work. That said, I can see no benefit to having SQL Server as the application database. MySQL is just as easy to use and well documented. Given the choice, I’d rather the application developers focus on more core functionality that something of little value.
Am I missing something?


#7

Adding my MS SQL db via the Metabase admin panel has been a LIFESAVER! Setting port 1433 via the Sql Server Configuration Manager per the SQL_Server_2008_R2_Express_Edition_with_Advanced_Services_ Installation_Guide_r-1-6.pdf documentation p 34, however, is apparently a requirement. Then filling in db & instance names, leave “Windows domain” N/A, db username “sa” (sign-in as admin) and db pw (my pc’s unlocking pw)…

Mind you it took me HOURS of researching to figure all this out. That’s one of the drawbacks w/Metabase, it’s assumed you’re a techie demi-god and know all this stuff already. So if you’re a hapless “know only enough to get by” type like myself hopefully this will save you beaucoup time and frustration!

That said you should be able to copy-paste all your custom sql queries into Metabase. Skip the “Question” building abstraction altogether, it’s of absolutely NO value when you have complex stuff already done in MS SQL. Best of all, all the custom do-hickies like UDFs, TVPs, CTEs, etc. work seamlessly!

Setting up Metabase’s filter mechanism, however, is a major learning curve… Little gotchas that nobody tells you about straight off can be really painful. This whole business of {} brackets, for example, doesn’t play nice with table aliases so you’re almost obliged to create a well defined and indexed View in MS SQL in those sections where filters are concerned (everywhere else table aliases are probably fine). And to populate the filter drop-downs you have to specify the type of field when mapping via their “x” pane as “Category” NOT “Number” or “Text” or anything else… That’s mentioned somewhere in the documentation but it’s not obvious. It’s one of those “oh, by the way” types of things that can send even the most tech-literate down the garden path (if I’m not mistaken AndrewMBaines can probably sing you a few bars of that ditty)!


#8

I think we’re talking about two different things - one is using MS SQL to store Metabase’s internal data, the other is using MS SQL as a data source. 2nd is supported and works well, first isn’t.
The problem with SQL Express is a limitation of the JDBC, not metabase. There are some WIKIs on here that guide you through.
Yes, I’ve been round this a few times (my hair is greyer by the day) - I’m mostly using Views at the moment, but plan to create some views that provide a star schema if the users ever need to do more than look.


#9

Hi Andrew, couldn’t agree more… I am using the Always Encrypted feature in MS SQL that allows me to encrypt the data in my DB but whenever I connect to metabase I’m able to see decrypted data by just giving an additional JDBC param columnEncryptionSetting=enabled; and providing the certificate location

I would like to have the same flexibility around users of metabase as well to ensure my application compliance around security. Hence the feature request :slightly_smiling_face:


#10

Thanks for the clarification Andrew, sometime I’d like to hear about how figuring out the whole “Category” filter thing may have changed the way you do things (you had some pretty elaborate dropdown population methods I believe). And if you don’t mind sharing the star schema thing, that would be great! So far I’ve managed to use dynamic sql to allow populating the filters based only two or three views in such a way as to at some point give my super-users a flexible way to dig thru the data!


#11

Oh wow, encryption, now that’s a tip I can use! Thanks for sharing! If you open up an issue on Metabase’s GitHub I’d be happy to upvote it!