The connected user is not an Analysis Services server administrator. Only an administrator can make changes to server properties

You are a part of the local administrators group on the server. You want to add a user to the Server administrator group of your Analysis Server. After adding the user you will get next error message: The connected user is not an Analysis Services server administrator.  Only an administrator can make changes to server properties.

Solution: Start SQL Server Management Studio with the option 'Run as Administrator'. Now it is possible to add users to the Server administrator group of your Analysis Server.

How to use Import and Export wizard (DTS) to transfer data to or from a SQL Azure database?

A lot of people are starting with experiments to look to the possibilities of SQL Azure. One of the things to start is a database with content. As described in one of my previous blogs, you can use the SQL Azure migration wizard. This is a nice tool, but most people are already aware of the Import and Export Wizard in SQL Server Management Studio (SSMS), also known in the past as Data Transformation Services DTS.

In this blog I will describe how you can use the Import and Export Wizard to transfer data from an on-premise SQL Server database to an SQL Azure database. What do you need to have to start using the Import and Export wizard.

1. Windows Azure subscription
2. Create a SQL Azure server and database
3. Configure the SQL Azure firewall
4. On premise SQL Server with SQL 2008 R2 with SSMS.
5. Create the tables you need in the SQL Azure database.
6. Create a clustered index on each table to which you want transfer data. Without a clustered index you CAN'T transfer data to a SQL Azure database.

Now you have done all preperations we can start the Import and Export Wizard. Click Start, All Programs, SQL Server 2008 R2, Import and Export Data. The Import and Export wizard is now started.

1. Choose a datasource. This is your on-premise SQL Server.
2. Choose a destination. This will be your SQL Azure database.
1. Destination: .NET Framework Data Provider for SqlServer
2. Encrypt: True
3. Integrated Security: False
4. Password: SQL Azure administrator password
5. TrustedServer: Certificate: True
6. User ID: SQL Azure administrator name. [username]@[sqlAzureservername]
7. Data Source: FQDN of your SQL Azure Server
8. Inintial Catalog: The SQL Azure database you have created to insert data.

3. Press next and execute the same steps as you normally do in the Import and Export wizard.
4. In case you forgot to create a clustered index you will get next message:
1. Messages
   
   Error 0xc020844b: Data Flow Task 1: An exception has occurred during data insertion, the message returned from the provider is: Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again. (SQL Server Import and Export Wizard.

Enjoy using the Import and Export wizard to transfer data to your SQL Azure database.
Tweet

Getting Started with SQL Azure, create your first SQL database in the cloud.

Microsoft® SQL Azure™ Database is a cloud-based relational database service built on SQL Server® technologies. As said by Microsoft SQL Azure should:

1. Provide a highly available, scalable, multi-tenant database service hosted by Microsoft in the cloud. SQL Azure Database helps to ease provisioning and deployment of multiple databases.
2. Developers do not have to install, setup, patch or manage any software.
3. High availability and fault tolerance is built-in and no physical administration is required.
4. SQL Azure Database supports Transact-SQL (T-SQL). Customers can use existing knowledge in T-SQL development and a familiar relational data model for symmetry with existing on-premises databases.
5. SQL Azure Database can help reduce costs by integrating with existing toolsets and providing symmetry with on-premises and cloud databases.

These statements sounds very promising. Therefor I started creating a SQL Azure database in the cloud to see the current status of the cloud-based relational database service.

First of all you need a SQL Azure account. Request an invitation code for yourself.  Within one day you will receive the invation code which enables you to create your own SQL Azure Server. To create a server you need to specify and Administrator Username and an Administrator Password.

Now your server is created with one database: Master.
To access this server you need to configure the firewall settings. Press on the Firewall Settings tab and click on checkbox "Allow Microsoft Services acccess to this server". Select the Rule MicrosoftServices and press the Edit Rule button. Now you can specify the range of IP adresses which are allowed to connect to your Azure Server. In my example I have configured all IP addresses.

Now we will create the first database. Select the Databases tab. Press Create Database, Specify a database name and the size of the database. You can choose between 1 Gb and 10 Gb.

Press the Test Connectivity button to test the connection. You MUST have the MicrosoftServices Firewall rule enabled in order to use this feature. Your database is created and you are ready to connect with the SQL Server Management Studio (SSMS). For the Object Explorer you have enter the Server name and Login credentials.

To open a new query you need to specify the database you want to connect. This canbe done in the connection propertie tab. The USE command is not supported for switching between databases. Establish a connection directly to the target database.

Now you are connected.

Enjoy using SQL Azure.

My favorite SQL Server Management Studio settings

I work a lot with SQL Server Management Studio (SSMS). From a performance perspective I'm not so happy with the default settings of this application. Therefor I made some changes to the settings of SSMS.

To made changes to the setting of SSMS start SSMS and select Tools, Options in the menu.

1. Startup with an empty environment. (Environment, General, At startup: Open empty environment)
2. To avoid to lock other users when you execute long running SELECT queries. Set the default transaction isolation level to: READ UNCOMMITTED (Query Execution, SQL Server, Advanced, SET TRANSACTION ISOLATION LEVEL)
3. Include column headers when copying or saving results. (Query Results, SQL Server, Result to Grid)
4. Quote strings containing list seperators when saving to .csv results. (Query Results, SQL Server, Result to Grid)
5. Word wrap. This is a setting which I changed a lot depending on the kind of queries I'm analyzing. (Text editor, Plain Text, General, Settings)

Enjoy using SSMS