anyone played with the paid ODBC connector? Did you hook it up to a local SQL, and if you did which one did you use/recommend?

I use the ODBC and JDBC all the time: ODBC as a linked server in SQL Server, JDBC as a driver in SQuireL SQL. You’d probably be fine using any relational database you are comfortable or like the most, so long as it supports the ability to connect to ODBC data sources. A tool like SQuirreL is nice for running ad-hoc queries, requiring no local database at all.

@scottvonduhn thanks! hmm my client wants to use it as a one off to offline their instance for audit purposes, i see from documentation say Microsoft Access has a 255 column restriction, was just wondering across the board what people use and what they find more useful? from what I hear from you then that's an MS Access limitation rather than a ODBC limitation? I'm not familiar in the RDB space, I suppose, in general any SQL server will be okay?

All databases are implemented differently, and have different max columns, row, and even varchar and blob size restrictions. Since NetSuite uses an Oracle database, that may work the most seamlessly, but I haven’t tried it. I have run into issues with certain large text fields not being converted automatically by SQL Server, so if you want to do full table dumps, you may run into some problems. But, also be aware that not every record type and not every field of every record type is exposed through SuiteAnalytics Connect, so while they can get a snapshot of data, it won’t be everything in their NetSuite account.

thanks @scottvonduhn great help!! will take those into consideration!

for audit purposes Full CSV Export may be useful too, not sure if it can handle well large volumes though.

@NetCase can you explain what you mean? originally I thought exporting as CSV/excel would have been better for support in re-import (supposed they have another NS instance) but I haven't been able to diffrentiate how the CSV Export compares with an ODBC export to a SQL server in terms of pros and cons, and completeness

the full CSV export tool is borderline useless...

I have never tried using the full CSV export in NetSuite. Over in our Salesforce world, one of the SF engineers does a weekly full CSV export using their tool. The amount of data is massive, and it takes hours to complete. It also requires manually rotating and deleting older backups. I don’t think we have ever looked at or used those files a single time in more than 8 years. It’s just some paranoid thing an executive wanted us to do, and we have to keep on doing.

It's slow. Exports a quarter (at best) of the data you'd want.

I've spent a fair bit of time with the connector. Started off using SSIS with SQL Server, but the NS db schema is a moving target so didn't play nice. I ended up writing a backup tool in C# that replicates all the schema and data. Currently backing up approx 670 tables daily and it's pretty solid. It also has filtering so you can choose the data you want. System notes is a biggie so selectively sync what's required from that (100m+ records). Also use async to pull about 10 tables in parallel, I think I tried 15 or 20 and had some issues. Full sync takes approx 2-3 hours and is currently 15gb (so can't use SQL express as that's a 10gb limit).