Marco Russo has a nice post about the basics of configuring the workspace database server. You can use his post to learn how to set the tabular instance to use for the workspace database server for new and existing projects.

However, there’s more to picking a workspace database server than just picking an instance. You should think about whether you are picking a local or remote instance, and you should also think about the service account running the instance. Your choices here affect the behaviour of the tabular designer.

First, let’s compare the capabilities of local versus remote workspace database servers. The following table shows the differences in behaviour.

Feature Local server behaviour Remote server behaviour
Taking backups in the tabular designer Taking backups is supported Taking backups is not supported
Importing from PowerPivot Data and metadata can be imported from PowerPivot workbooks Only metadata can be imported from PowerPivot workbooks (explanation here)
Working with large row sets Data set is loaded into memory locally on the dev box Data set is on a remote server, which may have much more memory than a standard dev box
Importing from text, Excel, or Access The bitness of Office/ACE provider must match the bitness of the OS The bitness of Office/ACE provider on the dev box need not match the bitness of the OS

Next, let’s compare the capabilities of using a high privileged service account for the workspace database instance (such as yourself) versus using a low privileged account (say the default NT Service\MSSQLServerOLAPService account). The following table shows the differences in behaviour.

Feature High privileged user behaviour Low privileged user behaviour
Taking backups in the tabular designer Assuming you are working on a local workspace database server, backups can be taken and saved anywhere that the service account user can access.

If you are running the service account as yourself, backups can be saved to the default directory for VS projects, \Documents\Visual Studio 2010\Projects.		 For all practical purposes, taking backups is not supported. The service account does not have access to the file locations where users would typically save a tabular project.

Of course, if you like to save all your projects in a folder accessible to the service account, such as the OLAP\Backup directory, you certainly can take backups. Since that’s not realistic for anybody, taking backups isn’t supported.
Importing from PowerPivot Assuming you are working on a local workspace database server, you can import metadata and data from PowerPivot workbooks.

If the service account is running as yourself, the PowerPivot workbook can be saved on your desktop, in your documents folder, or on a network share accessible to you and you will still be able to import metadata and data.		 Unless the PowerPivot workbook is stored in a place where the service account can see it, such as the OLAP\Backup directory, only metadata can be imported from PowerPivot workbooks (explanation here)

Finally, let’s consider your access to the workspace database server’s file system. Whenever you create or open a tabular project, a workspace database is saved in the server’s OLAP\Data directory. Over time, this folder may become bloated with temporary databases. From time to time you need to go in there and get rid of the stuff you don’t need any more. To do that, you need file system access. Therefore, it is best if you pick a workspace database server that you can clean up. This is not required, but your server administrator will thank you.

This is why I configure my workspace database server instance to be a local instance on my dev box that I run as myself. My dev box is a 64 bit box with 64 bit Office installed. This maximizes the amount of functionality available to me in the tabular designer. The only thing I can’t do is work with large data sets. Fortunately, I know a trick to import only a subset of data, so I don’t run into this problem too much.