Home Forums For Those Of You Considering SQL Server Standard

As has been pointed out, one way to alleviate performance problems with Amazing Charts is to move up to SQL Server 2012 Standard Edition. One thing I would like to point out is that with SQL Server Standard you get A LOT of addition stuff not included with Express editions that you would be wise to take advantage of.

SQL Server Integration Services
This is Microsoft's ETL (Extract, Transform, Load) solution. Using SSIS I have done the following:

--Created automated interfaces between Amazing Charts and Practice Management systems. These interfaces can be completely customized rather than having just the "take-it-or-leave-it" interface the vendors will offer. For example, you can have the interface detect certain CPT codes that may require a certain modifier or diag code and have the interface include that modifier or diag code as the encounter is transferred over to the Practice Management software.

--Created interfaces with Lab companies such as LabCorps.

--Create automated interfaces with patient reminder solutions such as House Calls. Send HC all the appointments from, say, 5 days in the future from Amazing Charts' or your Practice Management software's scheduler.

--Have automated write-offs for small balances. Check for all claims where insurance is resolved and have it write off the claim if the balance is too small to worry about.

SQL Server Reporting Services
Create your own custom reports from Amazing Charts or your Practice Management software such as:

--Show reports of data transfers using SQL Server Integration Services so you can see which transfers failed, succeeded, or what encounters were transferred or failed, etc. The report can show Amazing Charts' encounter ID and what the new ID is in the Practice Management system. This way you have complete control over what's going on.

--Procedure Averages Analysis Payments: The average payment you are getting for a procedure by carrier, provider in a date range.

--Procedure Averages Analysis Adjustments: Same as above but in this case you want to see the adjustments being applied by carrier, provider.

Here is a screenshot of one I went nuts with. You can even choose the DAY OF WEEK in your date range.



SQL Server Analysis Services
You can use this to pre-aggregate data from multiple sources for advanced reporting.

These tools come with SQL Server Standard. Yes, it will cost you money to get a developer to come in and set this stuff up for you and develop the ETL packages and reports and so forth, but if the payoff in increased efficiency is there, you should take advantage of it. You've already paid for it all, may as well use it.

JamesNT

Thanks! I just upgraded the server and ordered the SQL server 2012. I believe the performance has improved. Time will tell....It only took me a few years to upgrade!

James,

Thanks for your post. Very informative. However, I must make a few observations. You are making it sound as though, one can just go online and order SQL Server Standard 2012 and install it. Changing the default instance of AC from the default Express to Standard is no easy task for the novice or most users for that matter. Not to mention it would likely instantly void their support contract.

The two biggest attributes, the larger database and the increased RAM, will only help the larger practices.

You mention hiring a developer to set up programs for analysis included with SQL Standard. But, one will need a SQL specialist just to install it properly.

I am also not sure how much of a performance increase one will get. While there are two of us on here who disagree as to how the actual cache works, I don't think I would see much of an improvement with more than a GB of memory anyway. And, the 1GB restriction probably helps those who are using only 4GBs of memory on their main computers.

When you say "find a developer" do you not mention yourself out of fairness. Because you already have the advantage of understanding billing, AC and SQL. If this is going to be a viable alternative, I think the only way it would be feasible would be to have a 3rd party developer working through AC, much like Indy or others do.

It would make sense for you contact AC and see if there would be a way that users could contract with AC and you so their would be a contractual agreement with your being available to provide support or, at the very least, if you install and set up SQL or properties of it, it would be supported.

This could only be a win-win. In these days of out-sourcing, it makes little sense for AC to have one SQL specialist on their staff when they may be sitting around all day. But, to have one or two freelance SQL IT such as you or Raja would be ideal.

Wow !! it has been a long time since I have visited the forum. I still remember the early days in 2006/7 , when Bert would be the go to guy.

Having worked on the sql server 2000 and 2005 code way back when I used to be at microsoft, and having been removed from that product for so long I have forgotten by basics.

what is AC's stand IF we go to sql std? Not that I have ever used AC support(they are so pathetic that i end up solving the issue)

I think most companies are going to have difficulty supporting a product which is completely different than the one it was designed to work with. If I use Windows Small Business server and remove Exchange and add another 3rd party exchange-type server, they aren't going to work on that email product nor other products which possibly share its database. (this is an example -- I don't know how many products can replace Exchange.

But, as you said, support can be limited, and they are not going to know how to work with an Instance of SQL Standard.

We are much smaller office of less than 10 users and not more than 5 online at the same time. I run my AC 6.6 on a VM with 4GB memory and it works.

I should experiment with latest AC on another backup VM? I need to order a new server, so thinking should I get a dual proc or will single proc with loads of memory suffice. I have never seen AC pegging the CPU.

I would experiment with AC on a backup VM. Dual or Quad Core will be much faster, you can dedicate a core or two only to the VM.

V7 and V8 are much more resource intensive than V6. They use a later version of SQL which likes more memory and AC itself is more of a memory hog.
See here for general recommendations http://amazingcharts.com/support/hardware-os-requirements/

If you are considering a new server, I'm not sure you can get one as a single core. Especially with VM's cores matter. Although AC's recommendations are based on Intel architecture, I like AMD processors and they are moderately lower cost, but are not as fast for individual processes. But you can get an 8 core processor for $150 or less!

With all of the variables now in play, upgrading requires much more thought.

For example, we have a client with 20 users on 6.3.3, that doubling the cores (2 to 4) with 12G of RAM found v8.0.2 to be unacceptable when they tried it in a test environment. For them, we are still testing to find the right resource allocation that meets their expectations.

Another client went from 6.3.3 to 7.1.2 (50-70 users), and after going to Full SQL (3 cores) and 32G of RAM, they are stable, but replacing that server with a new server with more cores and more RAM that we are currently configuring. More details after we transition them.

I could go on, but the bottom line is that practices need to think it through, and perhaps test (or have someone like us provide them a test) before they upgrade.

We are working with AC to refine some of this into a methodology, and I'll be putting out a series of videos going through this in detail since this is now the #1 subject we are getting questions about.

It is much deeper subject, but it needs to be pointed out that not all VM technology is the same when it comes to efficiency or performance.

Sorry to bump this thread, but I was just wondering what the current state of upgrading to sql server standard is.

Is there a posted methodology for doing this and will AC support this?

I think its interesting that AC requests a server computer with 8gb of ram when it uses a sql version that is hard limited to 1gb of ram.

Thanks

SQL Express 2012 32bit will consume up to ~3.6G at full load.

AC will do the upgrade to SQL Express 64bit.

For full SQL, you will typically have to work with an AC certified IT provider.

does 64 bit increase the memory cap on the sql server instance?


Also is there a list of AC certified IT professionals?

I am really not sure if SQL will be the answer. The reason they do this is because SQL Express is made so that it can be bundled within software. To require every practice to pay for a full SQL and CALs would be prohibitive. Every time they upgraded to a new SQL version, this would be a new cost.

Maybe allowing for an easy transition which 2012 allows would be helpful. But, it would require a few more techs who are well-versed in SQL. And, couldn't you just see now when a problem arose, and support simply said, it is likely due to your upgrading to full standard. Unless you have a huge practice, I don't think 1GB of RAM is going to hinder you much. Especially when there are those on the board who suggest emptying that cache daily -- something I do not understand.

I can post this as another thread instead of hijacking this one - but essentially we are noticing a huge decrease in performance once the 10th or 11th user starts with amazing charts v7 and we don't get much support from the tech support as to why this is an issue. A few of the users connect via terminal services, so they immediately blame that (though even the non TS users slow down).

No. All Express versions are limited to 1 GB on the RAM for cache. The only difference between 5 and 12 is the 4 GBs of database space compaed with 10 GBs.

The best way to tell is to Google SQL Server specifications and you will find tables and charts with all of the versions with what they come with.

What processor are you using? I really think most of this falls on AC. Also, are you completely 1 GB throughout your entire network including your switch?

If you go back and read the links I provided, and James then confirmed, I am correct. There is the 1G of operable memory, and then a variety of other memory that SQL allocates outside of that 1G. I have seen SQL Express 64b take 8-10G of RAM.