I'm working with a system where each customer has their own database.  This amounts to ~4000 databases (approximately 150 tables each database) spread over 8 instances of Microsoft SQL server 2012.  The database scheme for all these databases is supposed to be identical, but recent events has shown that there are minor deviations in data types, indexes, keys and nullable/default value settings in some of the databases.  My goal is to synchronize all database schemas so they all have identical tables, columns, indexes, keys and nullable/default value settings  I have a single database that I'm certain has the 100% correct database schema.  How could I compare this database scheme to others, in an attempt to find variations in keys, indexes, tables, columns and nullable/default value settings?  I've read  which is similar to mine, but the suggestions (like right clicking the database object in SSMS to generate a script or generating and comparing a .dacpac file with the Visual Studio extension SSDT) seems to be oriented towards comparing only two databases, and is not practical for comparing 4000 database schemas.
121676
Is there a way to find the differences in two SQL Server databases (schema only).  One is local and the second is at a customer's site.  We are experiencing problems with crystal reports running some reports and some code not executing and it would appear that the schemas don't match.  Can I run the same command on both databases and compare the results to tell where the differences are?
108476
I have the following SQL Server 2008 table and data:  CREATE TABLE dbo.MyTable (Sno int, refid int);  INSERT INTO dbo.MyTable VALUES   (17, 8),   (16, 8),   (15, 7),   (14, 7),   (13, 6),   (12, 6),   (11, 5),   (10, 5),   (9, 4),   (8, 4),   (7, 3),   (6, 3),   (5, 2),   (4, 2),   (3, 1),   (2, 1),     (1, 0);   Note that Sno 2 and 3 are referencing Sno 1 in the same table (self-reference). So 2 and 3 are children of 1 and 1 is the parent of 2 and 3.  Requirement  I will pass in a single Sno value (e.g. 4) and I need:   All the parents of that Sno in a result set. All the children of that Sno in another result set.   I need to display the top 10 levels for each result set.  This illustrates the relationships and shows what I have tried so far:  SELECT refid FROM dbo.MyTable  WHERE Sno = (SELECT refid FROM dbo.MyTable WHERE Sno = 4); GO SELECT refid FROM dbo.MyTable WHERE Sno = 4; GO Select Sno FROM dbo.MyTable  WHERE refid = (SELECT refid FROM dbo.MyTable WHERE Sno = 4); GO
95591
We are using SQL Authentication (to reduce the number of connection pools) &amp; .NET 4.0 connection strings to connect to SQL Server Enterprise Edition 2012 SP1 on a Windows 2008 R2 Enterprise Server:     Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)   Oct 19 2012 13:38:57   Copyright (c) Microsoft Corporation   Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)   We use about 50 servers split into 8 different groups different parts of a website.  Our website is using this SQL Server to log visit tracking data. Over the last few days it has spat out the following messages about the resetting connection pools:     The client was unable to reuse a session with SPID 1327, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.   Error log reads:     Error: 18056, Severity: 20, State: 46.   The client was unable to reuse a session with SPID 959, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier  operation failing. Check the error logs for failed operations immediately before this error message.   Login failed for user 'xxxx'. Reason: Failed to open the database 'xxxxxxxx' configured in the login object while revalidating the login on the connection. [CLIENT: 10.xx.xx.xxx]   After some digging, I found this document on the CSS blog:  and this one by Aaron Bertrand: . I know the error number is different but the failure ID is the same with a number of the messages are identical).  Failure ID 46 suggests that the login did not have permissions. Our logins default to the master database and the db name is specificied in the connection string.  I wanted to check the number of connection strings pools, etc. and checked all the counters in Perfmon for .Net Data Provider for SqlServer. It only gave me the option of defaultdomain9675 for the instance, so I selected that assuming that is a system generated ID name for our Datacentre network. Unfortunately all of the counters are reading zero. On one of our other main servers the connection pools are hovering around 10 which is what I expected to see on a healthy server with that kind of load.  My question is 3 fold   Can anyone suggest why the Windows 2008 R2 Server is not showing .Net Data Provider for SqlServer? Has anyone experienced this as I obviously believe that the login not having permissions is a red herring ? If different groups of web servers have the same connection string syntax but with slightly different whitespace, would this cause the server to use another connection pool?   Min &amp; max memory settings are 20GB &amp; 58GB respectively. The server is a dedicated database server with 64GB of RAM. I don't think memory is the issue as the box seems to have a decent page lfe expectancy. Auto close is not enabled. The server is always up: this is a 24x7 website with heavy usage.
65478
How can I get the list of available databases on a SQL Server instance? I'm planning to make a list of them in a combo box in VB.NET.
67417
I want to synchronize some datasets and feature classes from Oracle to MS SQL Server using ArcMap replication tools. Both databases are geodatabase enabled. I don't want to replicate/synchronize data in real time but once a day.  I have created one-way replica(parent to child) and it works fine for only the changes that are made from ArcMap, but when I make changes in the table/feature class from Oracle (SQL Developer) i.e updating the attributes of a record, those changes are not synchronized. How can I synchronize all the changes either made from ArcMap or from SQL Developer?
28828
I am pretty well versed with SQL Server, MySQL, Oracle etc but putting these Database products aside, is there a resource that will help me design relational databases well? Is there something like patterns or best practices for database design?  I have seen a few times that database is often not scalable; people have personal preferences with keeping columns like isChecked column which is boolean in nature but stored as Char(1) with values like 'Y' and 'N' instead of 0 and 1 which to me sounds better. Ways not to commit common mistakes while doing database design?  Links to books or articles will be highly appreciated.  Thanks in advance.
116895
We have this large database (>1TB) that we intend to "shrink".  The database revolves around one main entity, let's call it "Visit".  For discussion, let's say it is a database for a medical practice.  There are a total of 30 visit "types", such as procedure, annual, follow-up, immunisation etc, each of which is a subsidary table to "Visit", e.g. "visit_immuno".  The database has accummulated some 12 years of data since 2000.  Someone has proposed that we keep about 3 years of data in the "live" version and have the rest live in an "old_data" database.  The date is ONLY stored in the "Visit" table since it is normalised.  The Visit table also contains a ROWVERSION column and a BIGINT pseudo-identity (clustered) column.  For all intents and purposes, let's say the clustering key is populated by a SEQUENCE (SQL Server 2012 Enterprise) - we shall name it cid.  The visit.date is not always in the same order as the clustering key, for example when a doctor goes on extended visitations and returns with his "briefcase" of data, it gets merged into the main table.  There are also some updates to the "visit" table that will cause the ROWVERSION column to be out of sync with both the cid and date columns - to put it simply, neither ROWVERSION nor cid would make suitable partition keys for this reason.  The business rule for removing data from the "live" is that the visit.date must be greater than 36 months and a child visit_payment record must exist.  Also, the "old_data" database does not contain any of the base tables except visit%.  So we end up with:  Live DB (daily use) - All tables Old-Data DB - older data for the visit% tables  The proposal calls for a Combined DB that is a shell containing Synonyms to ALL the base tables in the Live DB (except visit%) plus Views that UNION ALL across the visit% tables in the two databases.  Assuming the same indexes are created in the Old-Data DB, will the queries perform well on the UNION-ALL Views?  What type of query patterns might trip up the execution plan for the UNION-ALL Views?
76944
I have a draft of requirements that look like this:   Production database should be copied in real-time to "Pre-prod" database The Pre-prod database should be available to read and update It should be possible to make schema and data changes to Pre-Prod Schema and data changes on Pre-prod should not effect Prod   The databases will be on the same SQL Server instance.  I'm wondering what approach to take? Transactional Replication seems the best option, but applying schema changes to a subscriber seems like a recipe for disaster.  Mirroring is out because the mirror will not be available; log shipping is out for similar reasons.  Are there any other technologies I should be considering?
89109
I read that it's possible to restore a database in SQL Server as long as you're restoring from an older version to a newer version, for backwards compatibility reasons.  Does anyone know off hand if you can restore a database from a *.bak file for different editions of SQL Server?  We are moving a very large database via FTP that will take a couple days, so we'd rather only do this once. If nobody responds by the time we transfer the database via FTP, we will obviously try this out and see if it works by testing, and answer our own question.  Below is a query to get version details of SQL Server.  The productversion is in the format {major revision}.{minor revision}.{release revision}.{build number}.  In my case, the {release revision} has a value of 5500 for the source and 5512 for the target.  So that looks okay. However, the edition is different.  Query:  SELECT    SERVERPROPERTY('productversion'),    SERVERPROPERTY('productlevel'),    SERVERPROPERTY('edition')   Source database:  10.0.5500.0 SP3 Developer Edition (64-bit)   Target database:  10.0.5512.0 SP3 Enterprise Edition (64-bit)
115589
I am a little out of my league with this scenario and hope this community can help get me on the right direction.  I have recently been tasked with re-designing and implementing a database application where the company I work for processes a large quantity of text files (sizes ranging from a 100MB to 3GB).  We currently process approximately 600 of these files per year.  This data needs to be accessible after our initial processing.  The current implementation has each file being imported via Application bulk load or SSIS to a "temp" database as individual tables.  I would like to keep this process in place as it gives a good entry point for when manual entry into processing is needed.  I am in the process of trying to create a new MS SQL Server installation and architecture, and am looking for advice/articles which would cover a similar scenario as to best practices (partitioning, filegroups, etc.).  I have worked in databases for a few years, but not at this level.  So if you have any experience in this type of scenario or know of a good article or book you could point me to, I would be extremely grateful.  I just need a nudge in the right direction.  Thank you in advance.
102523
Problem definition  Our database server needs to be transferred to an other datacenter. It runs on Microsoft SQL Server 2012 Enterprise (64-bit) and contains two databases of about 2TB and 1TB.  Having little to no downtime for this would be ideal.  Workload  Those databases are used for a .NET website and are constantly getting updated.   Having it not available over the weekend would be acceptable though. The currently in use DB would remain the only one in use until the switch over to the new one.  That switch would ideally be made just by changing DNS entries to point to the new DB server while making sure the DB is not being updated.  Also, time taken by this operation does not really matter as long as the switch from one server to the other (downtime) is kept low.  Approaches considered   Backup and restore  This has been done in the past but involved a high downtime even though it was done through an internal network, so more efficiently than through Internet Log shipping  As far as I understand, this approach would minimize downtime by configuring a master/slave and transferring  an exact copy of the master DB to its slave being read only. As mentioned above, no access to the slave would be necessary and we just need a way to have a replica of the master DB without data corruption.   It also seems to be quite efficient in terms of resources utilization and wouldn't impact to much the master performance.   I might be wrong about this approach so feel free to correct me. Database mirroring  I'm not too aware of that approach but it seems like a valid option. No need to have real time sync and performance of the master is quite important so asynchronous would be the way to go if this approach were to be chosen. Other options?  That server runs directly on bare metal hardware so lower level solutions are unfortunately not an option. Maybe there is a better way to get this done?   Constraints  As described, those databases are quite big to the point they are hard to maintain but that's an other problem.  The versions of SQL Server will be the same (Microsoft SQL Server 2012 Enterprise 64-bit).  It will have to be transferred over network between two datacenters so most probably over Internet. Having disks sent from one site to the other for an initial sync is unfortunately not an option. Having some sort of security for the transfer would be ideal but we will do the best of this situation.  That should give a quite good overview of our needs for this task and hopefully some of you had to face that situation before.
87139
Suppose I am running multiple column update on one row. Does the database engine physically write new data to the database if some of the columns contain the same values as existed previously in the database? If so, how can it be avoided?  I'm about to implement a job which will run an update on a large table and most of the values will be the same but still, it has to recalculate them all. I am wondering if the update will also rewrite each column even if there is no need to, because the storage media will degrade faster.
116392
If I create a table with an identity column as primary key, and all the other columns have default values, for example  create table rr (id int identity(1,1) primary key, dt datetime default getdate())
97745
I have a database setup with the following schema:   CREATE TABLE siteInfo (     siteID             SERIAL  NOT NULL,     siteName           TEXT    NOT NULL,     siteHost           TEXT    NOT NULL,     notes              TEXT    NOT NULL,     allowableCountries TEXT    NOT NULL,     displayChance      INT     NOT NULL,     imagePath          TEXT    NOT NULL,     PRIMARY KEY (siteID) );  CREATE TABLE siteLinks (     siteID             SERIAL  NOT NULL,     URL                TEXT    NOT NULL,     FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE );  CREATE TABLE siteDemographicLinks (     siteID             SERIAL  NOT NULL,     URL                TEXT    NOT NULL,     country            TEXT    NOT NULL,     chance             INT     NOT NULL,     FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE );  CREATE TABLE linkVisits (     siteID             SERIAL  NOT NULL,     rawVisit           BIGINT  NOT NULL,     redirects          BIGINT  NOT NULL,     FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE );   Data from my form is in the following format:  array(7) { ["siteName"]=&gt;string(9) "Site Name" ["siteHost"]=&gt;string(9) "SIte Host" ["notes"]=&gt;string(5) "Notes" ["links"]=&gt;array(2) {[0]=&gt;string(6) "Link 1" [1]=&gt;string(6) "Link 2"} ["countries"]=&gt;string(4) "None" ["displayChance"]=&gt;string(2) "15" ["extraLink"]=&gt;array(2) {    [0]=&gt;array(3) {       ["url"]=&gt;string(7) "dSite 1"       ["countries"]=&gt;string(4) "NONE"       ["chance"]=&gt;string(2) "11"       }    [1]=&gt;array(3) {       ["url"]=&gt;string(6) "dSite2"       ["countries"]=&gt;string(4) "None"       ["chance"]=&gt;string(2) "13"       }    } }   How would I go abound inserting data into the many type tables so they reference tuples inserted into the one type table?  I have this query constructed:  INSERT INTO siteInfo (siteName, siteHost, notes, allowableCountries, displayChance, imagePath) VALUES ('Sample', 'sample.com', 'note', 'NONE', '50', '../var');   I would then need to insert data into the siteLinks table, which is related to the entry already in the siteInfo table.  INSERT INTO siteLinks (siteID, URL) VALUES ((SELECT siteid FROM siteinfo WHERE sitename='Sample'), 'test');   Is there a better way of doing this?
66411
This seems to be a stupid question but despite some research I was unable to find any information regarding this only (possibly due to using the wrong terminology).  Is it possible to log multiple server logins (sql server authentication) to a single database user (which has permissions assigned by being member of a database role)?  I have dozens of sql logins (sql server authentication) which need to read one setting from a central database and I'd rather map all these logins to a single DB user in the target database than create an own DB user for each login.  If yes, what would be the correct T-SQL syntax?
18713
When looking at the properties of a particular login, it's possible to see a list of users mapped to that login:   I profiled SQL Server Management Studio (SSMS) and I see that SSMS connects to every database one at a time and retrieves information from sys.database_permissions  Is it possible to write a single query that retrieves the user mapping information shown above or am I forced to use a cursor or sp_MSforeachdb or something like that?
109746
We're in the process of a server migration from an on-premise server (Win2008R2) to Azure PaaS.  To move the databases, we used the  tool, which worked great and we can connect to the migrated Azure DB via SQL Server Management Studio.  Considering:    Made quite a few changes to the migrated Azure database (tables, SPs, indexes) to work with the apps in Azure Combined multiple DBs from the on-prem server into one DB in Azure via DMA to save costs  One on-premise database is continually being modified by insert and update operations (multiple tables) during the migration process When fully switching hosting in Azure we need to make sure all data is up-to-date. This will be done very early in the AM during which on-prem IIS websites are stopped assuring no DB operations are made, giving us a short window to migrate missing data   Question: What is the best and fastest way to migrate data (all vs missing/updated) considering the above?  Can this be achieved by following the below steps?   Create a new empty Azure SQL database (to later hold all the tables of 5 on-prem databases) Via , migrate only the schema of that one on-prem database which is continually being modified by insert/update operations Via , set up auto data sync (e.g. every 30 minutes) between the on-prem and Azure database After data sync up and running, via - migrate schema and data of the rest of the on-prem databases (no data changes during migration process) into the single Azure database previously created and used for syncing   EDIT:  After enabling Data Sync in the Azure portal, the sync was unsuccessful due to the below logged errors:   Member Database: MyAzureDb -> Tombstone cleanup failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:18456, Message: Login failed. Member Database: MyAzureDb -> Database provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:18456, Message: Login failed.   To fix 1 and 2 above tried: ALTER ROLE DataSync_executor ADD MEMBER [myUser]; but got Cannot add the principal 'myUser', because it does not exist or you do not have permission.   Member Database:MyOnPremDb: -> Database provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:325, Message: Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. SqlError Number:156, Message: Incorrect syntax near the keyword 'AS'.    The current compatibility level of the on-premise database is 80. Not sure what impact setting it to 100 will have to the production databse/apps.