I would like to put a Clustered Index on a date column in a current heap, but one question/concern.This heap every month has thousands of rows deleted and even more added later. How much of an issue will this cause the Clustered Index as far as page splits? I was thinking Fill Factor of 70%.I would normally just test and still will on Dev box, but my Dev box is much smaller than production as far as power.
I have SSIS 2012 Enterprise, using catalog deployment and have more that 50 environment variables for connection to databases across my enterprise.
The problem when i go to configure the packages after deployment and pick the proper env variables, that are not sorted, so i have to browse all entries in order to find the proper entry in environment variables.
I am running into a weird issue with a new SQL Reporting Services 2014 server I built. I installed SQL Reporting 2014 on Windows Server 2012 R2 and configured Kerberos, but the site is extremely slow. After some reconfiguration and log captures I have determined the issue has to do with the Kerberos setup, however I am running a similar configuration with SQL Reporting Services 2008 on Windows Server 2008 R2 and do not run into the same errors.
The error I see while using Wireshark is KRB Error: KRB5KDC_ERR_BADOPTION NT Status: STATUS_NO_MATCH. When I drill down the into the error I can see the kerberos string is testprjmnmtreports14.company.com, which is the URL we are using to access the site. I made sure to add that name as an SPN for the service account that is running SQL Reporting Services, however I still receive the error.
Then I tried configuring the site to run without a hostheader, so I accessed the site with the server name, ECTSTSQLRS5, and the site works perfectly fine, no errors are reported either. So it seems I have isolated the issue down to Kerberos but I am not sure how to resolve it. Here is some more information about my environment:
DNS/URL used: testprjmnmtreports14.company.com Server Name (FQDN): ECTSTSQLRS5.company.int AD Domain Name: company.int Server Version: Windows Server 2012 R2 AD Functional Level: 2008 R2
As you can see I am trying to use a .com address but my AD domain is .int which I think is the issue, but I do not have the same problem on my other server that is running Windows Server 2008 R2. What do I need to do to allow my new site on 2012 R2 to work with this DNS Alias?
I am looking at combining / consolidating some of our servers and was wondering if I can install SQL 2000 and SQL 2005 in a clustered Environment on a san. Cheers Angie
We are running 2 SQL Server and both run in failover clustered Environment. The Problem is now we need to Replicate a Database from one Virtual SQL Sever to the other.
The Second one (clusterd environment)is stroing their database localy while the First One (clustered environment)is storing database in a shared storage. Note that Both Server are used for sperate purpose , but we now need to set replication on the Other Clustered Setup for Reporting Purpose.
Will it work if we configure replication from One SQL Server Clustered Setup to the Other Clstered Setup. If yes, then please let me know how it can be done ?
Hi all,I have posted the same topic under ASP-NET-Developers but had no reply;I then posted it under ASP.Net Community and had also no reply.Hence, i'm posting it here to prevent the impression of spam post ...I have created a SqlContextTrigger using ADO.NET 2.0 to create atrigger onSQL Server 2005.In the class, I create a file and specify its path to be in theApplicationDomain base directory such as:String Path = AppDomain.CurrentDomain.BaseDirectory.ToString()However, the path is always the SQL Server Binn Directory. I'd like totellthe program that I want the path to be related not to SQL Server BinnDirectory but to Visual Studio Solution's Project Bin Directory.How do I do that?Best regards
Yesterday, our server 'failed over' to the passive node in our clustered environment. We are running Windows NT, with MSSQL 7.0. What triggered the fail over, according to the logs, was an error in tempdb. Basically, the error said that tempdb could not be expanded, (could not create any more space for tempdb).
The drive that tempdb database is located on has more than enough room. The tempdb is set at 300M, and can grow to over 700M. The drive that tempdb is on has over 19Gig of space on it.
One other important piece of information is that the drive where our production database is on and the tempdb is quite full. The 19 gig that is left over represents only about 5 percent of the disk.
Can anyone shed some light on why the sql server would have failed over when trying to expand tempdb, when there was sufficient resources to handle that growth? Am I barking up the wrong tree? Could this be another error? When we restarted the server (both nodes and the disk array) the production database came back as suspect. Most of the stuff in BOL points to the space issue. The production database is around 37 gig.
I am running into an issue trying to uninstall SQL Server 2005 64-bit. It hangs at the same point: Removing backup files. I then get a message that, "The setup has encountered an unexpected error while Completing Commit. The error is: The cluster resource cannot be moved to another group because other resources are dependent on it." The instance that I am trying to uninstall is in a cluster hosting two other SQL Server installations. I have tried a few combinations when attempting to uninstall: running the cluster group containing the server I am uninstalling on a different cluster node than all the other cluster groups; running them all on the same node. The only one I haven't tried is moving the Cluster Group containing the IP address of the cluster, cluster name, etc to another node while running the other groups on the other node. Has anyone run into this issue? Each of the nodes has been rebooted several times during these attempts and we are using Windows Server 2003 Clustering. Again, the hang happens at the same point every time. The exact error(s) from the even log are as follows:
Product: Microsoft SQL Server 2005 (64-bit) -- Error 29528. The setup has encountered an unexpected error while Completing Commit. The error is: The cluster resource cannot be moved to another group because other resources are dependent on it.
Product: Microsoft SQL Server 2005 (64-bit) -- Error 29530. Failed to set registry settings for server network libraries. The action is SetShilohRoot. The error is 2 (The system cannot find the file specified.
I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
I am looking for some assistance from the grand knowledgebase out there concerning the implementation of Sql Server Express 2005 on a client's strict domain environment.
I am designing and implementing a pos software that resides on registers and a server within a number of stores. The registers are running WePos and the server is running Server 2003. I run an instance of sql server express on all devices. The registers read the server when it can see it but when it cannot it reads the local instance. I am seeing a number of performance issues and I am trying to tweek the installation and coding of SSE on all devices.
we recently migrated from our in-house domain to the Enterprise domain. Everything went smooth except for the fact that I can no longer accept my dBs using my SA or my domain admin account. There is only 1 account I can get into the management studio with but it has no admin privileges, so I can't make any password changes or add accounts. I don't have a test environment so kind of hesitant to experiment with our production system.
I am embarking on a project to upgrade our SQL server environment. I'm curious for some opinions on the relative benefits of Log Shipping versus a Clustered environment and you have any experience that has helped form your opinion on the issue?
I have a two node failover cluster(Active/Passive). I am planning on installing the Report Server database on the failover instance. Do i need to install RS on both the nodes?
We are using Win2k3 R2 with SQL 2000 in a domain environment.
Is it possible to create a domain group to grant admin level and user level access to SQL2000/2005 without giving users server admin or domain admin access?
It has always been my impression that to have admin access to SQL that you had to at least had admin level access on the server.
I get the following error when trying to execute a call to a procedure located on a linked server. My source server is within a clustered environment. "Could not relay results of procedure % from remote server %." Straight select T-SQL calls work fine.
Can someone tell me how to make a backup in a sql clustered environment (MSSQL 2000) and how to restore that backup in clustered environment (MSSQL2005)? A step by step backup and restore procedure is appreciated.
i have created a fact table which has unique cluster index as below,
CREATE UNIQUE CLUSTERED INDEX [FactSales_SalesID] ON [dbo].[FactSales] (salesid ASC) WITH (DATA_COMPRESSION = PAGE) GO however later when i add CLUSTERED COLUMNSTORE INDEXES : CREATE CLUSTERED COLUMNSTORE INDEX CSI_FactSales ON dbo.FactSales WITH (DATA_COMPRESSION = COLUMNSTORE) GO
it prompts error.
Msg 35372, Level 16, State 3, Line 167 You cannot create more than one clustered index on table 'dbo.FactSales'. Consider creating a new clustered index using 'with (drop_existing = on)' option.
Is there a way using a stored procedure in a local database to add a record to a database executing in a cloud environment when both entities reside in different domains?
I am able to send databasemail on my staging servers which are non clustered but databasemail fails on my clustered production servers. Both production and staging servers sit on the same domain(although the domain controller is sitting on one of the staging servers) I have set up the account and profile and have double checked that it is pointing to the correct SMTP server and to the same one as the staging servers. I have also enabled database mail in surface area configuration. The message gets queued, because i see the 'mail is queued' message. The databaseMail Process also gets started, but during the actual send process I see The following is the error in the databasemail log. Any ideas what could be my problem?
Databasemail Log
Date 18/07/2007 18:49:17 Log Database Mail (Database Mail Log)
Log ID 14 Process ID 964 Last Modified 18/07/2007 18:49:17 Last Modified By TT-IXTOOLSixtools
Message 1) Exception Information =================== Exception Type: System.NullReferenceException Message: Object reference not set to an instance of an object. Data: System.Collections.ListDictionaryInternal TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32) HelpLink: NULL Source: DatabaseMailEngine
StackTrace Information =================== at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID) at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.SessionManager.GetAccount(Int32 accountID) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
btw, I ran this command to send mail
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
We will be implementing our first SQL cluster in December. Our current plan calls for a shared development/test database server with one physical server, but two SQL Server instances. Our production environment will be a SQL cluster. Is it necessary to create a clustered test environment for testing patches, hot-fixes, etc...?
I am trying to use an indexed view to allow for aggregations to be generated more quickly in my test data warehouse. The Fact Table I am creating the indexed view on is a partitioned clustered columnstore index.
I have created a view with the following code:
ALTER view dbo.FactView with schemabinding as select local_date_key, meter_key, unit_key, read_type_key, sum(isnull(read_value,0)) as [s_read_value], sum(isnull(cost,0)) as [s_cost] , sum(isnull(easy_target_value,0)) as [s_easy_target_value], sum(isnull(hard_target_value,0)) as [s_hard_target_value] , sum(isnull(read_value,0)) as [a_read_value], sum(isnull(temperature,0)) as [a_temp], sum(isnull(co2,0)) as [s_co2] , sum(isnull(easy_target_co2,0)) as [s_easy_target_co2] , sum(isnull(hard_target_co2,0)) as [s_hard_target_co2], sum(isnull(temp1,0)) as [a_temp1], sum(isnull(temp2,0)) as [a_temp2] , sum(isnull(volume,0)) as [s_volume], count_big(*) as [freq] from dbo.FactConsumptionPart group by local_date_key, read_type_key, meter_key, unit_key
I then created an index on the view as follows:
create unique clustered index IDX_FV on factview (local_date_key, read_type_key, meter_key, unit_key)
I then followed this up by running some large calculations that required use of the aggregation functionality on the main fact table, grouping by the clustered index columns and only returning averages and sums that are available in the view, but it still uses the underlying table to perform the aggregations, rather than the view I have created. Running an equivalent query on the view, then it takes 75% less time to query the indexed view directly, to using the fact table. I think the expected behaviour was that in SQL Server Enterprise or Developer edition (I am using developer edition), then the fact table should have used the indexed view. what I might be missing, for the query not to be using the indexed view?
If I install an instance with Windows Only authentication, and then change it to Mixed Mode, if I enable the sa login, the password has already been set. What is the default? If it's generated, how secure is it? Is the password generated? What algorithm is used for that?
My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.
I am using a monitoring system where I can monitor a numeric SQL result assuming the result is one field and one row.I would like to do this to say monitor the free available space or percentage on say the Master database. DBCC SQLPERF gives me a few columns and results for all databases on the server.
In our environment applications are using a DNS name which points to the physical server ip address. Now we are planning to move to 2014. We are planning to have servers in different subnets so we will be having two ip adresses for listener. How we can point the DNS to the listener ips? If failover happens can the DNS point to the exact ip address of the listener where it's primary node?
