Hot answers tagged sql-azure
14
The is no such thing as a 'primary index'. There is such a thing as a 'primary key' and also there is such a thing as a 'clustered index'. Distinct concepts, often confused. With this distinction in mind, lets revisit the question:
Q1) Can the clustered index in a SQL Azure table be modified?
A: Yes. Use WITH (DROP_EXISTING=ON):
create table Friend (
...
7
Read Inside SQL Azure:
SQL Azure provides logical databases
for application data storage. In
reality, each subscriber’s data is
actually stored multiple times,
replicated across three SQL Server
databases that are distributed across
three physical servers in a single
data center. Many subscribers may
share the same physical database.
...
6
Use the SQL Azure Migration Wizard:
The SQL Azure Migration Wizard (SQLAzureMW) gives you the options to
analyzes, generates scripts, and migrate data (via BCP) from:
SQL Server to SQL Azure
SQL Azure to SQL Server
SQL Azure to SQL Azure
6
There isn't an explicit way to do this today, but that isn't a permanent scenario (can't reveal more due to NDA). Even when the schema change hit is acceptable, it may not be what you want, because it will invalidate all plans related to the underlying object, not just the bad one.
Not looking for credit for this, but building dynamic SQL to perform the ...
4
Try setting the "Collation Compatible" to True on the Linked Server definition.
If you set it to False, it will pull the entire table down and do the compare on the receiving end. Consider the scenario where the local server is case insensitive, and the remote server is case sensitive, the results will differ based on where the compare is done.
4
Compose a bcp script that exports the contents of all your tables to local files.
Start by writing a query that will output a bcp command to export each table in your target database to a path on your destination machine:
SELECT
'bcp '
+ SCHEMA_NAME(schema_id) + '.' + name
+ ' out '
+ ' D:\local_backup_directory\' + ...
4
Despite what MSDN documentation says, no, it doesn't matter for GROUP BY queries.
You can test it here, at: SQL-Fiddle test (SQL-Server 2012)
CREATE TABLE test
( id INT IDENTITY(1,1)
, a INT NOT NULL
, b INT NOT NULL
, c INT NOT NULL
, d INT NOT NULL
, PRIMARY KEY (id)
) ;
CREATE NONCLUSTERED INDEX a_b_c_include_d_index
ON test (a, b, c)
...
4
My suggestion would be:
(a) talk to Azure support. This is not how it should be working AFAIK.
(b) when building your list of indexes to rebuild/reorganize, add a NOT EXISTS clause to the criteria to eliminate any indexes with GUIDs as the leading key column:
SELECT name, etc.
FROM sys.indexes AS i
INNER JOIN sys.dm_db_index_physical_stats AS s
ON ...
...
3
Developers should produce code, and there are obviously many ways to sync code changes (source control). Never touch the database. Ever. If they need any change in the database, modify the code that deploys/upgrades the database. Rails's Migrations are an excellent example of doing it the right way. Visual studio has Database Projects. You can always roll ...
3
It's a good practice to delimit all identifiers. In this particular case, you said the PK_dbo prefix was actually part of the index name. Therefore, the name has to be delimited appropriately, or it cannot be parsed unambiguously.
Also, what's actually being renamed is an index, not an object.
This should work after filling in the blanks:
sp_rename ...
3
SSIS could certainly take care of this for you but as with many things there are several ways to complete the task.
1) You have several options within SSIS to complete the copy process, Copy Database is one of them. You could backup the database and restore it using a SQL Script. You may need to use the file system task to move the backup file but that ...
3
Okay, first thing, I need to pick at this a little bit:
We went down the route of making these reads readUncommitted since the
operations on the data are absolutely not mission critical.
Using READ UNCOMMITTED/NOLOCK should only be considered when the accuracy of the results is not critical, because that's what the transaction isolation level ...
3
I recommend either of the following:
Michell Ufford's index defragmentation script
Ola Hallengren's index and statistics maintenance script
Either of these can accomplish the task of maintaining indexes without requiring you to rebuild the scripts when indexes are added or removed.
One of the biggest problems I've seen with using the maintenance plan ...
3
Removing Execution Plans from the Procedure Cache
SQL Azure currently doesn’t support DBCC FREEPROCCACHE (Transact-SQL), so you cannot manually remove an execution plan from the cache. However, if you make changes to the to a table or view referenced by the query (ALTER TABLE and ALTER VIEW) the plan will be removed from the cache. Ref: here
2
I noticed there's a new tool in this list, it's Idera's Azure SQL Database Backup. It's free and they usually make good tools, so it's worth a try.
Another way of exporting databases (but this time only schema, no data) to Azure is by using DAC packages (.dacpac files extracted from Management Studio or Visual Studio 2010). This works only from SQL 2008 R2 ...
2
Matthew,
I don't have direct experience with SQL Azure but I think same rules apply here as a normal SQL Server instance.
280 mb is a very very small database and the cost of fragmentation is almost 0. Coming to the size of this small database, I don't think you can control that nor should you be worried.
The above is because when SQL Server creates a new ...
2
In addition to the other answers, you can also use the CSS SQL Azure Diagnostics tool from Microsoft support to run some queries on the server that show you missing indexes and popular queries.
2
It won't help you trace existing calls from your website but you can still obtain interesting performance metrics by running SQL commands from this tool directly: Enzo SQL Baseline. It offers options to load commands in parallel to simulate load and helps you compare performance metrics over multiple runs.
2
Yes, if you have an index covering those columns.
According to the documentation over at MSDN:
In the context of multiple-column indexes, for ORDER-BY or GROUP-BY to consider a particular index, the ORDER-BY or GROUP-BY columns must match the prefix set of index columns with the exact order. For example, the index
CREATE INDEX Emp_Name ON Employees ...
2
You can only have uniqueness validation within a single federation, same as partitioned table allow for aligned uniqueness validation only within a partition (federations don't have an 'unaligned' equivalent). The solution is trivial: create a separate, non-federated, Azure DB used only to validate email uniqueness.
2
20k records is not that many. Are you SELECTing other data from other tables as part of your mailout which is slowing things down? Are you keeping your transaction open longer than you need?
Otherwise, can you use one of the opimistic concurrency isolation levels: Snapshot or Read Committed Snapshot? They should allow you to read without blocking.
2
I think that your problem can be solved with the MS framework called "Trust Services".
Buck Woody explains in this article how you can use it to solve problems of encrypted data with SQL Azure. From his article, a short description about how to use Trust Services:
"With the new Trust Services service, the basic process is that you
use a Portal to ...
2
You probably can't as the procedure cache is shared across all the databases which are hosed on the server that your database is hosted on. All you can do it wait for the old queries to be flushed from cache using normal aging out that the procedure cache uses.
Forcing a recompile of the table objects would probably do the trick as well.
2
The Manage button is correct. It should bring up a page in a new window/tab that prompts for the user name and password to connect to the database. If that isn't working, there's something wrong with your web browser configuration (does it allow popups from the Azure site?).
Alternatively, the database server can be set up to allow external connections by ...
2
I'm not sure about the core of your question ("Can you set up replication between SQL Server 2005 and SQL Azure Database?"), but my assumption is a resounding "NO".
There are tools to migrate from SQL Server to Azure though - it's been discussed on Stack Overflow here and here.
The consensus seems to be that This SQL Azure Migration Wizard (CodePlex hosted ...
1
Have you looked at SQL Server Data Tools? This has schema compare built-in, and can also identify objects and language constructs in your on-premises database that aren't compatible with Azure. It is free to download from Microsoft.
1
The best way to do this effectively I think is to backup all the databases and transfer via Site to Site VPN (setup between your location and East Coast Azure Datacenter).
To backup all databases, setup a SQL agent job with below step. Remember it's a CmdExec type and not a T-SQL statement.
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE ...
1
I've been having a similar issue with 8 devs and we have been working through RedGate Data Compare/RedGate Source Control. We have reviewed using it in Prod with synching up multiple environments, and doing rollouts/deployments/rollbacks with it. Outside of a few bugs and setup nuances it's been a great tool. It's probably one of the most widely used out ...
1
You could also use a SQL job with the SQL agent.
Backup
Restore
Obfuscate restored database
Restore
Note: be careful with the backup database operation, use the "copy only" option otherwise you might break the backup chain when differential backups are used.
1
A SSIS package to move the data would be the route I'd go. This SO thread has many suggestions of solutions users tried apart from SSIS, incl 3rd party tools.
Only top voted, non community-wiki answers of a minimum length are eligible
