Tag Info

Hot answers tagged

36

Managing 100 or 500 databases is really not all that different from managing 5 or 10 - you just have to embrace automation and have a scalability plan in place (and don't plan to use high-cost-per-database features like mirroring across all clients). At my previous job we used this architecture and I would never once have ever thought of merging two ...


13

I recommend you read Multi-Tenant Data Architecture, a white-paper that discusses the options you have, and pros and cons. To summarize, it gives three options: separate DBs separate schemas shared schema You are now at separate DBs stage, which offers the best separation (isolation between tenants), but is the most difficult to manage. As you grow into ...


8

By using the ROW_NUMBER() ranking function, you could achieve that. Here's an example below. The top two queries are just for data verification. I'm a firm believer that you should see what you will be deleting (and what will remain) before actually deleting data. (Verification) Rows that will be deleted ;with cte as ( select *, ...


7

I would venture that your best approach is to use separate statements for each of the potential actions, and put them in a serializable transaction. You get to use tried and true statements with no funny semantics or "best practices" violations, and you get to avoid all of the problems I outline in this post, including wrong results bugs and potential index ...


7

DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += [sql] FROM ( SELECT [sql] = N' ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' ALTER COLUMN ' + c.name + ' VARCHAR(50);' FROM sys.columns AS c INNER JOIN sys.tables AS t ON c.[object_id] = t.[object_id] INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE ...


6

Here is a query that does a few things: use YourDatabase; go select object_name = object_name(c.object_id), column_name = c.name, type_name = t.name, c.max_length, alter_table_ddl = 'alter table ' + quotename(object_schema_name(c.object_id)) + '.' + quotename(object_name(c.object_id)) + ' ...


6

Yes, you can do this with a COMPUTED column, It's a workaround really as the column has to be PERSISTED to be used for the foreign key constraint so it consumes storage space: CREATE TABLE dbo.OtherTable ( ID INT PRIMARY KEY IDENTITY(1,1), Blah VARCHAR(100), FieldName AS CAST('This' AS VARCHAR(100)) PERSISTED NOT NULL ) ; ALTER TABLE ...


5

Look into BCP or BULK INSERT, both of which can share format files. Quick math based on pure guesses, since no hard details are given: 13 numeric columns, pretend they're 6 bytes each on average = 13*6=78 bytes. 5 VARCHAR columns, pretend they're an average of 300 bytes each based on the "largest" being VARCHAR(1000) per the original question = 5*300 = ...


4

As JNK said, for an INNER JOIN, these are going to be the same. You can prove it by comparing actual (don't bother with estimated) execution plans. In this case, they are exactly the same (click to enlarge): Personally, I like to keep the join conditions and the filter conditions separate. Join conditions go in the ON clause, and filter conditions go ...


4

There can be many things to address. Below is an outline of things to check before jumping on any conclusions : First, your sequence of POST restore steps will mess up all the work you did. Never shrink your database and that too especially after doing Index maintenance. Read up - Why you should not shrink your data files by Paul Randal. Below are my ...


4

As of SQL Server 2008, IIS is no longer used by SQL Server Reporting Services. It utilizes the HTTP.sys API now. So you will not see SSRS show up as a site or application pool. The tip you linked to deals with SQL Server 2005, which required IIS in order to use SSRS. This tip speaks of the slowness you might be seeing and how to address it in SQL Server ...


3

The sub query in the second query it's what's killing you that requires that the sub query needs to be run once per row that's returned by the record set. If you look at the plan it'll tell you how many times each operator is executed. I'll bet that you'll see a high number of executions on some operators of the second query. (I'm on my phone at the moment ...


3

The closest thing is a database snapshot. You take a snapshot of your database, that will act as the 'base image'. Then customers go nuts on the database and hack it to pieces. When all said and done, you revert the database back to the snapshot and voila, everything is back to its original state. See Revert a Database to a Database Snapshot. You do have ...


3

Congratulations, someone has reinvented EAV (Entity Attribute Value). Please study up on the subject with them. The short form is: one or more EAV tables can be useful in certain specific cases, usually when accompanied by other tables, but you lose most of the benefit of a relational database when you move to them. I would also ask: If someone wants to ...


3

This: SET SHOWPLAN_XML ON; GO SELECT * FROM sys.objects; GO Is equivalent to pressing Display Estimated Execution Plan on the toolbar (or hitting Ctrl + L). You'll notice that no rows are returned from the query, like there is when you use Include Actual Execution Plan (Ctrl + M). The spill warning is only a runtime warning. There is no way that SQL ...


3

You have to specify that you are inserting wide character data: CREATE TABLE #t (id INT,c1 VARCHAR(MAX),c2 NVARCHAR(MAX)); INSERT INTO #t VALUES(1,'žđšćč žđćčžđšćčŽĐŠĆČ','žđšćč žđćčžđšćčŽĐŠĆČ'); INSERT INTO #t VALUES(2,N'žđšćč žđćčžđšćčŽĐŠĆČ',N'žđšćč žđćčžđšćčŽĐŠĆČ'); SELECT * FROM #t; DROP TABLE #t; Result:


2

Alright so this is an intro on how to accomplish this using SSIS. This is just a base model to get you going you're going to need to tune this based on your specific database to improve performance. This tutorial also assumes you know how to set connection managers. Keep that in mind. First off you're going to want to create and then open a dataflow task. ...


2

After a forced failover testing with async mode, do I need to rebuild my old primary database? I'm not sure exactly what you mean by "rebuild" the database, but provided the databases are still in a working condition then you shouldn't need to take any actions like that. What you're seeing, by performing a forced failover, is by design. If you do a ...


2

Does this failover cluster impose any restrictions on this backup restore or will it be a normal backup restore operation. It will be a normal backup and restore operation (provided there are no other issues with the actual backup and/or restore). The fact that you are going from a standalone instance to a failover cluster instance will not negatively ...


2

CDC relies on a SQL Server Agent job to capture information from the transaction log. You can customize the job parameters by updating msdb.dbo.cdc_jobs - there are multiple properties as described in this Books Online topic: maxtrans maxscans continuous pollinginterval That doc has some information about these parameters, as does this white paper. But ...


2

If you've dropped all the tables, you could just drop the database and re-create it. It will be created with the default sizes and growth rates as defined by the model database. These may not be ideal (I don't find the defaults particularly useful in most scenarios), but you can change them. Unless you have a lot of things set up on this database (like ...


2

No, there isn't any. Any sort of 'last updated at' tracking would run into a severe performance problem as all updates, from all transactions, would attempt to update the one record tracking the 'last updated at'. This would effectively mean only one transaction can update the table at any moment, and all other transactions have to wait for the first one to ...


2

Use following query if you want to count the number of occurrence of the string 'first' in the TextColumn each row. SELECT (LEN(TextColumn) - LEN(REPLACE(TextColumn, 'first', '')))/LEN('first') as NumOfOccurrence, TextColumn FROM dbo.SampleTable; Update: To cover the trailing space case (as caught by Twinkles in the following comment), I modified the ...


2

There are many things that can cause gaps in an IDENTITY column (rollbacks, deletes), but in this case due to the jump I suspect it is this bug - caused by the changes to IDENTITY with the introduction of SEQUENCE: Connect # 739013 : Failover or Restart Results in Reseed of Identity So I bet that if you look in SQL Server's error logs, the rows ...


2

The best way to determine this is to simply look at the execution plans. Typically the optimizer will "condense" a lot of the operations in a query down. For example, if you include "WHERE 1=0" in your query it will see that, know it to be false, and do as little work as possible to return an empty set. In fact, in this case, it does 0 I/O operations as far ...


2

I don't think collation is your problem as that is related to sorting and ordering (although you might get a problem later using ORDER BY and so on), but you mention you have some VARCHAR fields. Those will not accept Unicode characters (which Cyrillic certainly are): They will need to be NVARCHAR throughout to do that. Next, how are you inserting your ...


1

This behaviour is a security feature to make hacking more difficult, specifically because there is no variation in the output depending on the input. (Aside from exposing the fact there is a logon trigger active.) If a password is mistyped, the system doesn't respond with an error saying "the password is wrong" or even whether the login name specified ...


1

Run the installer again. Installing/Configuring SSIS and SSRS to existing SQL Server 2008 database services Run SQL Server setup program and on the “Feature Selection” page click the checkboxes associated with the features you would like to have installed. When finish, reapply any service pack the instance have applied before.


1

First off, there is no such thing as an "active/active" cluster. That is a huge misnomer, and can lead to a large amount of confusion. What you have is two failover cluster instances in a Windows failover cluster with at least two nodes. Secondly, this will not be possible if you are dealing two failover cluster instances. I wish to now have the ...


1

Neither of those should work. If it's a named instance, it should be: DATA SOURCE=ClusterName\SQLInstanceName; If it's a default instance, just: DATA SOURCE=ClusterName; You are always connecting to an instance of SQL Server, whether it is clustered or not. The cluster will know which node in the cluster is currently hosting the instance, and this ...



Only top voted, non community-wiki answers of a minimum length are eligible