On Using Access with SQL Server
DBASchool - In-Person Class - Hope To See You There!
Instructor: Stephen Wynkoop (me!)
I'll be teaching a 3-day class on the things that you really need to know about SQL Server. We'll have hands-on exercises, lots of discussions and real-world lessons learned. Check out the site here and reserve your spot - we will not allow more than 15 students total so we can keep the class to a great size for discussions, demos and such. I can't wait to work with everyone - register ASAP if you'll be attending.
[Class Information] (check out the quotes on the site too from past attendees!)
On Using Access with SQL Server
Thomas wrote in with some additional information and experience working with SQL Server and Access, I wanted to pass it along here.
"In your recent editorial comment about Access and SQL Server, you quote Ian Posner as saying that Access retrieves key data from multiple tables back to the client and performs join operations there.
This is an oversimplification.
In many cases Access queries will pass all join logic back to the server, even with nested queries (one Access query referring to another) and large numbers of tables (I have a 20-table join to an SAP database that is passed back to the server and performs really well). The problem is that it doesn’t always do it, and sometimes a small change to a query results in it being processed locally (with, as Ian says, often disastrous effects on performance).
Figuring out when Access is going to decide to bring a join locally is a bit of a black art, and would benefit from more public discussion.
More complex queries involving outer joins, group by statements and multiple data sources are the things to look out for.
I strongly recommend a Windows registry setting (for Access 2007: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ODBC\TraceSQLMode=1), which traces all the SQL that ODBC actually sends to the server to a file sqlout.txt (normally in My Documents). If a query runs slowly, you can check the trace to see how the join is being performed."
Newsletter Holiday Schedule
We'll be taking a break over the Thanksgiving holiday - we'll be back on Monday - have a great Thanksgiving everyone!
Featured White Paper(s)
SQL Server Fragmentation Explained
This technical whitepaper will help you understand SQL Server fragmentation and the performance benefits you can gain on your... (read more)
|
11/25/2009
|
TempDB Lessons Learned - Don't Miss This Post
Featured Article(s)
Edit SharePoint Display/New and Edit form look, feel, and functionality
Here is a scenario: you have a SharePoint list (calendar, announcements etc) and your users have requested special changes on out of the box display, new, or edit forms. Whether it`s few extra webparts added, or make another form that will serve a purpose of print friendly version, here is how to do it ...
Webcast: End to End Database Development
How to manage the database Application Lifecycle. This session will discuss team-focused database development challenges, and will present tried and true methodologies for dealing with database development issues. This session is a must-attend for any database developer working in a team environment. Presented by: Ted Malone
> Register Now
> Live NOW
Wow. TempDB Lessons Learned - Don't Miss This Post
Cindy Gross wrote a killer blog post about TempDB. There is so much information here, you'll be picking and choosing what you need to do first.
Here's a link to the post.
Interestingly, the number of files that make up your TempDB under optimal configuration is not determined by file size or even disk configuration. It's actually much more simple than that.
In the meantime, be thinking carefully about how many of these things in the post pertain to the other databases in your system. You'll see a few items in the list that deserve attention. Specifically, check out the information about auto-grow options and the impact on performance. Also, the number of files your database should have for best performance.
Sorry for the teases - just go read the blog post. It's excellent information and things you can apply right away.
A Question...
Have you tested virtualization vs. instances? Yes, I do know they are different approaches and environments, but I have had several people ask of late. What's the best way, if both with technically "work" for your own installation, for you to bring up SQL Server functionality where you need separation? Have you tested the impact of instances on performance and maintenance? Have you worked with both to see what is easier to administer, report on and troubleshoot? If so, I'd like to hear from you about your experiences. Drop me a quick email here and let me know - are you an "instances" person or a "virtual machine" person?
Featured White Paper(s)
Performance Optimization: Extending the IT Infrastructure
Due to time, money, and resource limitations it is often no longer feasible to rewrite, re-architect, or even replace under-p... (read more)
Featured Script
dba3_DBCC_Ind_IndexDefrag
DBCC IndexDefrag DBCC Ind... (read more)
|
11/24/2009
|
Using Access with SQL Server?
Featured Article(s)
SQL VS ORACLE: A Comparative Review-Part II
In part I of the series we examined the general differences between Oralce and SQL. In this Part we shall go a little deeper and examine the differences in the way data is handled in these applications.
Index Creation Automation
It seems that most agree that outright automation, with tweaks and controls, would be a great thing. I have stumbled across a posting too from the Microsoft team about just such a routine, based a script that works with DMVs. Really worth a look - read more here.
Be sure to check out the post toward the top of the blog entry too - it has some good information about indexes, why how and where you do it, etc. Good stuff.
Using Access with SQL Server?
I wanted to pass along a recent post to the email discussion list about Access and how it talks to SQL Server. Ian Posner wrote about some key things that are happening when Access works with SQL Server, particularly with Linked Tables. The quest was whether users should be allowed/encouraged to use Access as a front-ed reporting tool against SQL Server. Here's Ian's note:
"ODBC by default creates server-side cursors. These impose locks on the underlying database therefore affecting concurrency. Furthermore with linked tables, Access retrieves the key data from multiple tables back to the client and performs join operations there. This increases the lock duration massively.
If they are going to use Access, I suggest:
1) Only allow Access Database Projects to connect to the database. These do not use the Jet engine at all, processing all joins on the server. However the users will have to be able to create stored procs in the database in order to save their queries. If you give them their own schema, you can sandbox them.
1) Investigate whether a new version of Access when configured as an ADP imposes server-side cursors. This you can easily check by setting up a profiler trace for stored procs with a filter of "sp_cursor%'"."
Excellent feedback!
Featured White Paper(s)
SSIS Accelerator Series: Working with SSIS Expressions
Whether you’ve been involved with SQL Server Integration Services (SSIS) for years or are just learning SSIS, you’ve probably... (read more)
|
11/23/2009
|
About Denormalization of Your SQL Server Databases
Announcing: DBA School - Learning SQL Server for the Rest of Us
A three-day course taught by Stephen Wynkoop - centered around real-world experiences, how-to information and specific demonstrations and labs that will let you use and explore the topics covered. We'll be covering Performance, Management, Security, Disaster Recovery more. Find out more at the class site here.
The catch? There are a maximum of 15 13 seats in this class. If you're interested in attending, please be sure to register as soon as possible. The class is in January and I'd love to work with you!
This will be a great way to get some hands-on training in working with SQL Server. We'll have labs for each segment and lots of discussion on the application of the items covered.
For more information, and to register to save your seat, please click here.
Featured Article(s)
Migrating from Oracle to EnterpriseDB
This article shows you step by step how you may migrate a Oracle 10G XE table to Postgres Plus using the Migration Studio. You not only can migrate a table, its indexes and constraints but whole tables.
About Denormalization of Your SQL Server Databases
It seems that a number of you are facing exactly this challenge in building out your systems. Your online transaction stuff needs to be normalized for performance and data protection. But, when it comes to your data warehouse or simply fulfilling reporting requirements, you're in a position of having to denormalize. Several people pointed out that this is really the point of views and fact tables and such. Very true.
I also heard from people that talked about having to do this, and automated it, using SSIS to create the new tables from the originals. This seems to be a very popular bit of work, taking the information from the original tables to new work tables for reporting using SSIS to modify and re-jig the data for reporting.
Keith provided some additional real-world feedback: "We have multiple applications for financial services and with that comes multiple implementation models of databases. Each client obviously has their own needs but reporting and even analysis of real time data is required. From a functional standpoint normalization has helped with the analysis aspect of real time data because roll ups of the data are faster and easier. But then when reporting is required where addresses and names are required from banks, companies, accounts, etc… the normalized model starts to slow dramatically.
I’ve been advocating normalization for our de-normalized system but not entirely to the 3rd degree. Even though a bank, company, beneficiary are all ‘parties’ we like to keep them separate rather than a party table with views because the views end up in views and the execution plan doesn’t always meet the needs because merge joins seem to be used a lot more when not needed. Most of the time we force the nested loop because most of the time the entities are the same or are at most a handful of required data instead of the sampling value that SQL Server may expect.
Another normalization method I found that I don’t like is an address table. The addresses are never shared within entities so there’s always a unique row that matches a one to one with all the other tables that may reference it, so from my opinion even though multiple tables may have street, city, state, zip… it’s a lot easier to keep it in the data table of that entity rather than joining since it will always be a one to one.
It could be that some entities could share an address but would only be more confusing later and even possible that the address is the same but the suite may be different and managing that would even be more difficult with trying to share that information."
Featured White Paper(s)
Developing Something for Nothing with SQL Server: A Closer Look at SQL Server Express and How it Can Work for You
Software development is an expensive and time-consuming process, not just in terms of time and salaries but also in terms of ... (read more)
|
11/20/2009
|
Data Warehouses and De-Normalization
Featured Article(s)
How to EASILY Migrate from IIS 6 to IIS 7
There are tons of great new features in IIS7 and the list continues to grow. We identify the top reasons people refuse to upgrade to IIS 7 and show you how a new tool from Microsoft, the Web Deployment Tool, makes the process nearly seamless. It is easier than you think.
Backups Can Sure Eat Up Disk Space...
A key planning point that should be on your checklist is disk space for your backups. If you combine retention of your backups, transaction logs and different scopes of backup (differential, full, etc.), you can quickly chew up a lot of disk space to keep things protected. That's where Idera's SQL Safe comes in. You can compress your backups with their new and even more optimized compression algorithms while at the same time optimizing the performance of your backups. It's a win-win. Get more information here and get your own free trial to test it out on your system.
Data Warehouses and De-Normalization
I've had some interesting email and phone conversations about normalization of late. As we have bigger and bigger systems and begin to address the new database extraction requests from our users, it's becoming clear that de-normalization can be a very strong tool.
I get the sense that things are shifting in terms of the types of design work that are happening. I think there are *so* many new ways information is being processed that it is quickly becoming the case where you'll want to be able to denormalize data, or at least present a denormalized view of that data, to support reporting and analysis applications. You can help reporting and query performance greatly if done right, but you'll have to take a step back and consider carefully how your database information is to be used. If you can create better access to information (removing joins, perhaps lowering the number of tables it takes to get a complete record, etc.), you can help your users. Interestingly, you can also give yourself a bit more control over the data and the security you want to have. By building views and other "slices" of information to support applications, you add a new opportunity to define and implement security to protect information.
What have you seen happening in your own applications? Are you denormalizing in support of reporting and analysis of information in your system?
Drop me a note and let me know...
Did You Know?
Did you know you can index views? There are rules of course, but it can be a great help in tuning your queries - build the views you need, then index them so SQL Server can make the most of going after information in the view. Check out "creating indexed views" in books online.
Featured White Paper(s)
The High-Performance DBA
The database administrator’s job continues to grow more challenging as they are called upon to confront new platforms, increa... (read more)
Featured Script
Reduce Transaction log
this routine iteratively will go through and shrink down your transaction log file if there is free space... (read more)
|
11/19/2009
|
|
